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: 90314 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://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeffrey Williams SSC Guru Points: 90314 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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.