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?
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?