Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using conventional 'select' statements within fields

Status
Not open for further replies.

mchoss

Programmer
Feb 11, 2002
87
GB
Crystal Developer v8.5
SQL Server 2000
Windows XP

Is there a simple way of adding the number of rows in a given table to a report, where that table is linked to the main table by a single field?

Effectively i want the result of [select count(*) from table2 where table2.linkfield = table1.linkfield] in a field on each row of my report.

Let me know if this does not make sense. Basically i need the count of a number of rows in a linked table returned to my main report for every row in the main report.

Thanks in advance.
 
Group by the table 1 field, and then do a count of the table 2 field in the group footer.

Another option would be to use a SQL Expression to perform a subuery.

Here's an example using the Northwind example dtaabase on SQL Server, where the main report uses the Customers table only and this SQL Expression is counting the orders:

(SELECT count(Orders."CustomerID")
FROM
"Northwind"."dbo"."Orders" Orders
where Customers."CustomerID" = Orders."CustomerID" )

Note that if you check the Database->Show SQL Query you'll see that the query is nested, and very efficient.

-k
 
Cheers, my only problem before was that i had an issue with the crystal syntax.

However, the sql expression i am using below now returns exactly double the correct value for num_appliances? However if i run the same statement (grabbed from Show sql query) directly against my database the query analyser, i ge the correct values. Where is crystal getting this wrong? Or does the 'sum' funtion work slightly differently in Crystal?
-----------------------------------------------------
SELECT
I_DETAILS.incno, I_DETAILS.stn, I_DETAILS.risk, I_DETAILS.location, I_DETAILS.time_app1, I_DETAILS.created_dt,
(select sum(I_EQUIPMENT_USED.equipnumber)
from I_EQUIPMENT_USED
where I_EQUIPMENT_USED.num_1 = I_DETAILS.incno) num_appliances

FROM
I_DETAILS I_DETAILS
-----------------------------------------------------
Thanks again
 
Things get curiouser and curiouser...

I would try in a new report looking at what crystal returns if you just add the table I_EQUIPMENT_USED with I_DETAILS. Does crystal for some reason double the returns for I_EQUIPMENT_USED? What if you bring in only I_EQUIPMENT_USED? Crystal can do some funky things, but I have never seen this particular oddity..

Lisa
 
The Query looks sane, I'd guess that you have 2 rows per detail, which makes sense, a Details table usually means that there are numerous rows of some type.

Try just having both tables in one report, with the equipment used as the Parent table, and the details as the child.

-k
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top