counting records in a query

[andrewvranjes]

hi there!

can anyone help me please?

i have 5 queries that total up the numebr of records in each. now i want to display this data. i have been using a cross tab query to do this but i am getting a error message!

andrew

 

[Twin]

Hi Andrija,
if i understand correctly, each of those five queries show total number of records as a single line result...? in which case simple select query with no joins will do the trick...just select the required field from each query and drop it into the result grid and you will get a range of five columns with a single row showing the total count of records of each query (if that is what you want to see, if not send some more details).
HTH

Chris

PS. How are things 'down under'?

 

[cogent1]

Meet me halfway...

OK. What's the error message?

 

[andrewvranjes]

totalling reocrd in a query

hi guys,

basincallly i need to total up a number of record in my query. i have grouped them togther, now i have to display the number of records in each group.

i have attached the outputted file in excel format. basically i need to total up the different companies in this file.

Please Help! & many thanks for down under!

Andrew

 

[Jon K]

Try this group by query (you can copy to SQL View of a new query and run it):

SELECT CompanyName, Count(*) as NumOfRecords
FROM [BP Hose query]
GROUP BY CompanyName


You can add other fields simultaneously in both the Select clause and the Group By clause e.g.

SELECT CompanyName, JobCode, Count(*) as NumOfRecords
FROM [BP Hose query]
GROUP BY CompanyName, JobCode

 

[andrewvranjes]

cheers jon k!

hey john,

cheers, that worked a charm!

i have one more question for you, how do i sort the number of records in desending order in sql?

thanks heaps mate!

Andrew

 

[andrewvranjes]

modifing sql code

guys!

i am having trouble displaying SiteID field.

i have only modified your sql code by adding SiteID into it and taking out JobCode.........

SELECT CompanyName, JobCode, Count(*) as NumOfRecords
FROM [BP Hose query]
GROUP BY CompanyName, JobCode

SELECT CompanyName, SiteID, Count(*) as NumOfRecords
FROM [BP Hose query]
GROUP BY CompanyName, SiteID

and a dialog box appears asking for the SiteID !

i dont get it!

please help again!




Andrew

 

[Jon K]

There is a space in the field name Site ID, so you must put it in a pair of square brackets [Site ID]

To sort the result, add an Order By clause at the end of the query:

ORDER BY Count(*) DESC

ORDER BY CompanyName, Count(*) DESC


The second Order By clause will sort CompanyName in ascending order, and the number of records within each company in descending order.