Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 21st, 2003, 07:04 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query query...

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
 
Old November 21st, 2003, 07:05 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

ST
 
Old November 21st, 2003, 07:09 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 21st, 2003, 08:02 PM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!!
 
Old November 22nd, 2003, 12:21 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 22nd, 2003, 10:28 AM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 22nd, 2003, 11:15 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Sal
 
Old November 22nd, 2003, 11:25 AM
Authorized User
 
Join Date: Oct 2003
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 22nd, 2003, 11:31 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 22nd, 2003, 11:33 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.