Count no of bookings in Jan and Feb query help (1 Viewer)

[iainmid]

I am trying to create a query that will count the number of bookings in january and a seperate count for the number of bookings in february from a table called booking order. im using the start date as the date to tell which month the booking was in.
This is what i have come up with but it doesn't work and i am not to sure where to go from here.
select count(jan.[booking no]), count(feb.[booking no]) from [booking order] jan, [booking order] feb
where jan.[booking no].[start date] > 31/12/2006 and < 01/02/2007
and feb.[booking no].[start date] > 31/01/2007 and < 01/03/2007;

 

[boblarson]

If I read you correctly, you appear to have separate columns for each month in your table (bad, bad, bad). This is what makes life miserable for pulling data out of a database.

Because your rows of data include months as columns and not in a true relational format, you will likely have to create a separate query to get the counts for each month and then pull them together as a union query.

 

[iainmid]

hi

my table consists on booking no, property no, start date and end date. the dates are in the short date format. i havent got seperate columns for each month. what i was trying to do in the query results was create a column with the count of januarys bookings under the table alias name jan and another column count of feb bookings under the table alias name feb. this maybe the wrong way to do it though

 

[boblarson]

select count(jan.[booking no])

Would be trying to pull a count of booking no from the jan table. So, if you have a separate table for months that would be as bad. But, if not and it's all in one table and you just goofed on the typing, then I would suggest going through the QBE grid and setting up your query that way and then you can just drag and drop fields and type the between criteria in the criteria spot and then look in SQL view to see the correct syntax.

 

[iainmid]

Hi I have tried creating using the QBE grid but can't get it to work corretly. The counts for jan and feb keep coming coming out the same as each other.
This is the sql i have created

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#));

I have also attached a screen shot of the QBE grid
If you could help with where im going wrong

 

[Rich]

Use a Totals query and group by Month(your date field)

 

[iainmid]

im not to sure how to go about this are u able to explain further