Count "Enquiries" per hour / day / date over a period of time

[ABP]

I'm currently working on an access 2002 database to replace a spreadsheet which has reached critical mass.

I have a table structured as follows:-

tbl_enquiries
ID (Autonumber)
Success / Failure (Text)
Timestamp (Date/Time - General Date)
Responder ID (Text)
Policy Number (Text)
SIB (Text)
Postcode (Text)
Error Code (Text)
Error Type (Text)

What i'm aiming to do is build 3 queries which interrogate the "Timestamp" field and display the number of successful + failed queries for each hour of the day / day of the week / date in the month. Ideally I would like the queries to prompt me which month (or which two dates to operate between, whichever is easier) I would like the data for. I've managed to write a
query which counts the number of successes but i'm not overly sure how to make it interrogate the timestamps.

In the spreadsheet I currently do this by having 4 columns next to the date that extract month-year, day, hour, date from the timestamp then create pivot tables.

I'd like the results to look something like this (they would be formed as 3 separate queries and results) :-

Query : Days of the week
Month : April
Success Failure
Monday: 89 24
Tuesday: 111 56
etc


Query : Hours of the day
Month : April
Success Failure
00:00: 85 24
01:00: 11 65
02:00: 811 0
etc

Query : Dates of the month
Success Failure
1st: 1285 124
2nd: 7181 67
3rd: 8321 4
etc


Does anyone have any suggestions?

 

[ABP]

*bump* (Apologies)

 

[namliam]

If you want popups on running a query... do something like:
Between [Please enter start date:] and [Please enter end date:]

OR you can enter the dates on a form prior to running the query and use
Forms("FormName")!DateField to retrieve the dates.

Good Luck

 

[ABP]

If you want popups on running a query... do something like:
Between [Please enter start date:] and [Please enter end date:]

OR you can enter the dates on a form prior to running the query and use
Forms("FormName")!DateField to retrieve the dates.

Good Luck

Thanks for you response Namlian this has really helped. I now need to work out how to make it so I can make the query extract the "hour" data from the timestamp and count the number of enquiries in that hour over the month displaying it as above.

I have a feeling that I need to use the "EXTRACT" SQL command but I'm not sure how the syntax would work in Access.

 

[namliam]

Extract? I do not know this function....

I would probably use Format(Timestamp, "HH") to find the hour, then group by it and count...