Aggregate SQL Count Problem

[sharkchaser]

I'm quite ingnorant regarding aggregate SQL functions, especially Count functions.

This query returns values that I can then count but what I would like is the query to actually COUNT the number of ListingDate and PendingDates that are returned for each.

SELECT June2010Newsletter.City, June2010Newsletter.ListingDate, June2010Newsletter.PendingDate
FROM June2010Newsletter
WHERE (((June2010Newsletter.City)="laguna woods") AND ((June2010Newsletter.ListingDate)>=#3/1/2010# And (June2010Newsletter.ListingDate)<=#3/31/2010#)) OR (((June2010Newsletter.City)="laguna woods") AND ((June2010Newsletter.PendingDate)>=#3/1/2010# And (June2010Newsletter.PendingDate)<=#3/31/2010#));

Thanks . . . Rick

 

[Emtucifor]

SELECT
   Count(*) AS TotalCount,
   Sum(Iif(ListingDate Between #3/1/2010# AND #3/31/2010#, 1, 0)) As CountByListingDate,
   Sum(Iif(PendingDate Between #3/1/2010# AND #3/31/2010#, 1, 0)) As CountByPendingDate
FROM June2010Newsletter AS L
WHERE
   (L.City = "laguna woods")
   AND (
      (ListingDate Between #3/1/2010# AND #3/31/2010#)
      OR (PendingDate Between #3/1/2010# AND #3/31/2010)
   )

 

[sharkchaser]

Thanks Emtucifor . . .

error upon execution:

syntax error in date and query expression

(L.City = "laguna woods")
AND (
(ListingDate Between #3/1/2010# AND #3/31/2010#)
OR (PendingDate Between #3/1/2010# AND #3/31/2010)
)

I can't see it!

Rick

 

[sharkchaser]

I found it!

The last date string was missing the # at the end.

Now I'm going to test it and see.

Thanks MUCH!

Rick

 

[sharkchaser]

Emtucifor . . .

This is a count that I'm getting.

TotalCount Count New Listings Count New Pendings
360 14 0

Shouldn't Count New Listings and Count New Pendings add up to Total Count?

Thanks.

Rick

 

[dhookom]

Sharkchaser/Rick,
Why are you asking Emtucifor when it is your data?

Duane
Hook'D on Access
MS Access MVP

 

[Emtucifor]

Sorry about the missing octothorpe.

The total should be equal or lower, as low as half the sum of the two, because when both conditions are true at once they get counted only once.

Please show your exact query.