2 sets of count on the same field

Question

2 sets of count on the same field

cindy_sinath

Old Hand

Points: 345
More actions
November 9, 2008 at 10:31 am

#195680

I am trying to perform a Count on the same field to spilt the data up into two sets, Tests and Sessions
SELECT Count(Lesson_CatId a) AS October_Total_Test, Count(Lesson_CatId b) AS October_Total_Sessions
FROM Booking
WHERE a.Lesson_CatId="001" OR a.Lesson_CatId="003" OR a.Lesson_CatId="004" OR b.Lesson_CatId="004" AND (((Lesson_DateSchedule.Lesson_Date) Between #10/1/2008# And #10/31/2008#));
It is not working. How can I make this work.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

cindy_sinath (11/9/2008)
I am trying to perform a Count on the same field to spilt the data up into two sets, Tests and Sessions
SELECT Count(Lesson_CatId a) AS October_Total_Test, Count(Lesson_CatId b) AS October_Total_Sessions
FROM Booking
WHERE a.Lesson_CatId="001" OR a.Lesson_CatId="003" OR a.Lesson_CatId="004" OR b.Lesson_CatId="004" AND (((Lesson_DateSchedule.Lesson_Date) Between #10/1/2008# And #10/31/2008#));
It is not working. How can I make this work.

I really cannot tell from this what you are trying to accomplish. You do not have a table aliased as 'a' or 'b' - so you cannot use either of those aliases. Also, dates in SQL Server are not delimited by # - they need to be delimited by a single quote.

Please review the following and re-post the question with appropriate DDL, sample data and expected results.

Best Practices: http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

Okay, first - you need to post in the Access forum instead of a SQL Server forum. Posting in the SQL Server forum will get you T-SQL answers which may or may not (mostly not) work in Access.

So, to your specific question - you probably need to use Iif to calculate these different totals (this would be CASE WHEN ... THEN ... ELSE ... END statement in T-SQL). Something like:

SELECT SUM(iif(Lession_CatId = "002", 1, 0)) AS Total_Test, SUM(iif(Lesson_CatId <> "002", 1, 0)) AS Total_Session, ...

FROM ...

WHERE ...

Another approach, specific to Access would be to create two queries that return your counts for each type - then join those queries in a third query to combine them.

Again - please post Access questions to the Microsoft Access forum.

Thanks.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

cindy_sinath Old Hand Points: 345 More actions · Answer 3

ok thank you. I've transferred everthing to the Ms Access section.

Thank you.