Specific Date information

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I run a Query, the totals are displayed for each date, not dependent on the specific date. How can I get my totals to run for the specific date?
 
Ryan,

I would guess you need to use the Where clause of the query, in other
words enter a criteria if you are using the query design view, in order
to restrict the query's data to the required date. If you need more
specific help, maybe you could post back with some more details of your
query and the data, maybe with some examples.
 
I will read more about the Where Clause. If I still can not figure it out, then I will write you back. Thank you for your time and efforts. Ryan
 
Steve,
Thank you again for the assistance. I am fairly new to this, which makes helping me that much more difficult.

I have two fields that I am pulling data from. A date field and a procedure field. I developed a form that a user can enter the date and I have a combo box that has a variety of procedures in it. So, the user can input the date and then select the procedure.

In my query I am trying to establish the total number of procedures for that specific date. The SQL that I am using to query my combo box data is: Echo w/Color: DCount("[Date]","Daily Statistics","[Procedure] = '3 Echo w/color doppler'"), where Echo w/color is the field name, "Date" is the field that I want to count my records, "Daily Statistics" is my table, and '3 Echo w/color doppler' is the specific data that I am looking for according to the date.

What happens when I run my Date query, it gives me the total of tests. Not specific for the date but an overall total and it displays it for each date:

Ex: I have qty of 2 echo tests for 7/14/04 and 2 treadmills for 7/15/04. When I run a query for the dates 7/14/04-7/15/04 it displays 2 Echos for the 14th and 15th, where it only should display 2 for the 14th. My data needs to be specific for the date. Please help! Thank you and sorry for the long message. Ryan
 
Ryan,

I think it would make it easier to see what's going on, if you could
post back with a list of the fields you have in your table, and also
with the fill SQL view of the query (you can get this via the View menu
in the design view of the query). Thanks.
 
Hi Ryan,

Thanks a lot, now we are getting somewhere. I think I am going to
suggest another approach. But I am still confused... It looks like you
have these fields in your Daily Statistics table"
Date
Procedure
.... And the actual data in the Procedure field is like this type of thing:
1 Treadmills
2 Holter
etc
.... And what you want to get out of it, is a Count of each Procedure for
each date within the specified date range.

Is my understanding correct?
 
Ryan,

Ok, try it like this...

TRANSFORM Count([Procedure]) AS HowMany
SELECT [Date]
FROM [Daily Statistics]
GROUP BY [Date]
PIVOT [Procedure]

This will need some refining, but I am just on my way out, and hopefully
it will point you in the right direction.
 
Ryan,

Just replace the SQL of the query you posted with the SQL I gave in my
previous post.
 
Ryan,

A few refinements to my earlier suggestion:

1. If you want to show a 0 instead of Null in the case of no tests of a
certain type, use the Nz function, so the SQL will be like this....

TRANSFORM Val(Nz(Count([Procedure]),0)) AS HowMany
SELECT [Date]
FROM [Daily Statistics]
GROUP BY [Date]
PIVOT [Procedure]

2. The word Date is a reserved word (i.e. has a special meaning) in
Access, and as such should not be used as the name of a field or control
or database object. I would use something like ProcDate or some such.

3. To answer your question, if you are in the design view of the query,
right-click anywhere on the background of the upper panel of the query
design window, and select Properties from the pop-up menu. In the
Column Headings box, enter all the potential headings you want,
separated by commas and enclosed in quotes, such as...
"1 Treadmills","2 Holter","3 Echo w/color doppler", ... etc
This will be included in the SQL of the query, which will then look
something like:

TRANSFORM Val(Nz(Count([Procedure]),0)) AS HowMany
SELECT [ProcDate]
FROM [Daily Statistics]
GROUP BY [ProcDate]
PIVOT [Procedure] In ("1 Treadmills","2 Holter","3 Echo w/color
doppler", etc)
 
Ryan,

What we are using here is called a Crosstab Query, and if you do a
Google Groups search on this term in the Access newsgroups you will turn
up some good information,

The book "SQL Queries for Mere Mortals" by John Viescas is great.
 
Steve,
I picked up the book SQL Queries for Mere Mortals and I am just about finished with it. Thank you for the suggestion.

If I can ask one more quesiton of you:
I am able to run my query for each day and it is working great. (With your help) Now is there a way I can run a query for a certain time frame? For example, if I want to see all the tests that were performed between #7/01/04# And #7/15/04#, can I have it give me one total? Here is my design so far:


PARAMETERS start_date DateTime, end_date DateTime;
TRANSFORM Val(Nz(Count([Procedure]),0)) AS [Count]
SELECT [Daily Statistics].ProcDate
FROM [Daily Statistics]
WHERE ((([Daily Statistics].ProcDate) Between [start_date] And [end_date]))
GROUP BY [Daily Statistics].ProcDate
PIVOT [Daily Statistics].Procedure In ("1 Treadmills",
"2 Holter",
"3 Echo w/color doppler",
"4 Doppler Echo",
"5 Soppler Color Flow",
"6 Echocardiogram",
"7 Partial Echo",
"8 Stress Echo",
"9 Transesophageal EC",
"10 Dobutamine Echo",
"11 Cardiolite Treadmill",
"12 Persantine Stress",
"13 Peds Echo w/Doppler",
"14 Peds Doppler Echo",
"15 Peds Doppler Color Flow",
"16 Peds Echocardiogram",
"17 Peds Partial Echo",
"19 Peds Transesophageal EC",
"20 Definity");



Thank you in advance. Ryan
 
Back
Top