Hey folks!

I need to write a query that searches for records by time and date parameters, and gives a summary count of those selected records. The results need to be displayed in a report, that lists the summary counts by Time parameter, and the report must show "multiple" time parameters. I also need to create a line graph showing the "peaks and valleys" of information.

My table contains the following information:

ID=autonum
DailyID=number
DateIn=Date()
TimeIn=Time()

I need to (1): Specify a Date Range (e.g., 11/15/03 to 11/21/03);
(2): Specify various Time Ranges that items come in (e.g., between 8am - 11am, 11am-2pm, 2pm-5pm, etc.);
(3)Summarize a Count of items (count of DailyID) for the specified date range and each time range;
(4) Put all the Count summaries on 1 page of a report; and
(5) Generate a Line graph showing the peaks and valleys of the count, by the various time ranges for the date period specified.

I can get a simple query running that specifies the Date range and Time range and gives me summary count of DailyID, but am having difficulty getting it all together.... I'm sure I'm missing something so totally easy to someone more experienced than I.

Any help is greatly appreciated!

Scott

Forgot to mention: I can only get the simple query going for 1 Time range at a time....

ST

Have you ever created a CrossTab query? It sound like that would be what you are looking for.
A crosstab can be used for a graph also.

Try to create one with the query wizard.

Click on Queries
Click on New
Select "Crostab Query Wizard"
then, select the table or query that you would like to use. Play with it a copuple of times and then I can help you some more. Access actually can do it all for you. It may take a bit of getting used to, but it is not too hard.






Sal

Sal: The crosstab query wizard comes close, but I still need the user to be able to specify the date range as a parameter, and break the time up in "blocks" like so: 8am-10:59am, 11am-1:59pm, 2pm-4:59pm, 5pm-7:59pm, 8pm-11:59am, 12am-1:59am, 2am-4:59am, and 5am-7:59am. Yeah... it's weird, but that's what the head cheese wants to see!

Scott

PS: Thanks for all the generous help you've provided in the whole process!!

It can be done, but I will have to play with it for a while. Right now I just had some wine and I am too sleepy. Will get back with you in the AM.
:)



Sal

I've found what may turn out to be at least a step in the right direction with my query-writing, but had to put it away for a while... I'll check back in later today......

Scott

Question : Does this have to create multi[le rows on the result set? or only one row?




Sal

I need it to somehow generate results like thus:

TIME CountDailyID
8am-11am 29
11am-2pm 43
2p-5pm 41
5p-8pm 37
8p-12am 25
12am-2am 18
2am-5am 3
5am-8am 1

     TOTAL COUNT 197


I then need to be able to generate a single-line graph showing the "ups and downs" of the CountofDailyID.

I could write a query pulling information for each of the individual time-frames, but putting it all together and creating the graph is the issue....

Thx,
Scott

You can create a few queries.

SELECT Count(Documents.DailyID) AS 8_11
FROM Documents
WHERE (((Documents.TimeIn) Between #12/30/1899 8:0:0# And #12/30/1899 10:59:59#));

paste that on the SQL view and call it
qur8_to_1059

paste this

SELECT Count(Documents.DailyID) AS 11_2
FROM Documents
WHERE (((Documents.TimeIn) Between #12/30/1899 11:0:0# And #12/30/1899 13:59:0#));

and call it
qur11_to_2

then paste this into a new one

SELECT qur8_to_1059.[8_11], qur11_to_2.[11_2]
FROM qur11_to_2, qur8_to_1059;

and run it
You get the idea for the rest. I know that this can be done with a crosstab quety, but it would take me a while to figure out. Now for the date ranges. you can do a paramenter on each query qur8_to_1059, qur11_to_2 and any others you create for the other time frames.

Have you done parameter queries before?




Sal

Actually,since I have a hang over or something, I forgot. You can do it all in one query using embeded subqueries.



Sal