How to Count Date Field 2

[nyamrembo]

Does anyone know how I can count date fields? Example, I have a Customers table and Reservations table. What I want to find out is how many times a customer has made reservations by counting reservation date field for each customer who showed up and also a date field for those who did not show up. When a customer shows up, a second date is field in and when they don't show up, the second date is left blank.

Thanks

 

[MajP]

assuming this is my table

customerName    reservationDate attendedDate
John.Doe        1/1/2010        1/1/2010
John.Doe        1/3/2010	
John.Doe        1/4/2010        1/4/2010
Joe.Brown       1/1/2010        1/1/2010
Joe.Brown       1/2/2010	
Ken.Smith       1/3/2010        1/3/2010
Joe.Brown       1/4/2010

qryMadeReservations

SELECT 
  tblReservations.customerName, 
  Count(tblReservations.reservationDate) AS   
  CountOfreservationDate
FROM 
  tblReservations
WHERE tblReservations.reservationDate Is Not Null
GROUP BY 
  tblReservations.customerName;

customerName   CountOfreservationDate
Joe.Brown      3
John.Doe       3
Ken.Smith      1

qryKeptReservations

SELECT tblReservations.customerName, Count(tblReservations.attendedDate) AS CountOfattendedDate
FROM tblReservations
WHERE tblReservations.attendedDate Is Not Null
GROUP BY tblReservations.customerName

customerName   CountOfattendedDate
Joe.Brown      1
John.Doe       2
Ken.Smith      1

qryNoShows

SELECT tblReservations.customerName, Count(tblReservations.customerName) AS CountOfcustomerName
FROM tblReservations
WHERE (((tblReservations.attendedDate) Is Null))
GROUP BY tblReservations.customerName;

customerName  CountOfcustomerName
Joe.Brown     2
John.Doe      1

 

[kjv1611]

That's an excellent answer, MajP.

nyamrembo,

Let us know if that answered your question. If it somehow didn't, then you definitely need to better explain what it is you're looking for, b/c from all I can tell, MajP just gave you the whole deal in a super easy to understand way.

I'll put a star on that one myself. I wish that every time I came across an answer in a forum it could be that clear.

 

[nyamrembo]

Sorry for my late response, yes it worked. Thank you! But now I am trying to run the same query count in design view and I have a status field that I would like to set to a specific criteria but when I set the criteria, I get an error "Data type mismatch in criteria expression." The status field include the following data “has email”, “need email”. If I just run the count without the criteria, I get results but it’s not correct. This is the same database.
Your help is greatly appreciated. Thanks

 

[MajP]

Switch from design view to sql view and post the Sql string.

 

[TheAceMan1]

How are ya MajP . . .

[blue]Switch from design view to sql view and post the Sql string.[/blue]

MajP .... thats not like you! ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]