there must be a simpler way of doing this?!

[shopoholic]

I need help to design a query that is more efficient than the method I am currenlty using. I have a database containing information about bookings on holiday parks and I had devised the following query:

SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Start Date]
FROM Bookinfx
WHERE (((Bookinfx.[Park Name])="COTSWOLD") AND ((Bookinfx.[Start Date])>=#1/1/2004# And (Bookinfx.[Start Date])<=#1/31/2004#));

Now this was fine, but I started getting asked to do several queries with different parks and different dates and changing these parameters manually in the code is beginning to become a pretty labourious task!

What I would really like (if its possible? ) is a query that gives me a count of the number of bookings for each park (there are 7 in total) for each month, e.g.

Park: COTSWOLD
Jan 123 bookings
Feb 338 bookings
etc
I know there is a Count function available to use, but I'm not sure how I could get it to do this. Also, I would like to add criteria, (for example where nights <3).

Any suggestions/help most gratefully received!

 

[Bat17]

See how this does for starters.

SELECT Bookinfx.[Park Name],Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year(Start Date])
HAVING (((Year([Start Date]))=2004))

Are number of nights stored or will the have to be calculated from dates?

Peter

 

[neileg]

If you want the user to enter the criteria you have two choices. The simpler way is to get Access to generate the prompts when the query is run. Like this:

SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Start Date]
FROM Bookinfx
WHERE (((Bookinfx.[Park Name])=[Enter park name]) AND ((Bookinfx.[Start Date])>=[Enter earliest date] And (Bookinfx.[Start Date])<=[Enter latest date]));

More elegantly, you would create a form and use text boxes in the form to capture the user input, but try the above solution first.

 

[shopoholic]

thank you!

Thanks Neil and Peter both of your queries worked have have given me the start I need.

Thanks so much!