Trying to create a count query (1 Viewer)

[iainmid]

I have create these two count querys that count the no of booking in January and the other no of bookings in Febuary

SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#12/31/2006# And ([Booking Order].[Start Date])<#2/1/2007#));

SELECT Count([Booking Order].[Booking No]) AS [CountOfBooking No]
FROM [Booking Order]
WHERE ((([Booking Order].[Start Date])>#1/31/2007# And ([Booking Order].[Start Date])<#3/1/2007#));

but i need to create a query which will bring up the two counts in the same query. This is what i have create so far but have got completely stuck and don't know where to go from here.

SELECT Count(BO1.[Booking No]) AS [Count Of Jan], Count(BO2.[Booking No]) AS [Count Of Feb]
FROM [Booking Order] AS BO1, [booking order] AS BO2
HAVING (((BO1.[Start Date])>#12/31/2006# And (BO1.[Start Date])<#2/1/2007#) AND ((BO2.[Start Date])>#1/31/2007# And (BO2.[Start Date])=#3/1/2007#));

 

[neileg]

Create UNION query that takes your two select queries and unions them together. Look up UNION in Access help

 

[KKilfoil]

There's a better way, if you have to do this sort of thing a lot.

Create a SUMMARY query on your table, grouping your records by month, and then adding the appropriate field to COUNT. You'll get a nice output, with one record per month. I believe that the Query Wizard can be used to set up one for you to see how this works.

If you really want to limit your output to a particular month range, then add a WHERE field which could contain something like 'Between #2007/01/01# And #2007/02/28#' ('Between' is inclusive).

 

[iainmid]

when creating the summary query for my table how do i group the records by month because start date is in short date format

 

[neileg]

I agree with KKilfoil. You can use the Format() function to just set the month and year in a new calculated field.

 

[KKilfoil]

when creating the summary query for my table how do i group the records by month because start date is in short date format

Is [start date] defined as a date/time field (as opposed to a text field with data that 'looks' like a date)?

If so, then the Query Wizard should be able to automatically group by month for you. Once it is done, be sure to look at the query design grid to see how it was done. (The wizard simply creates a field with a year/month format)