Data By Month--with a variable as parameter??

[lunarmono]

Hi, my skills are extremely limited so any help resolving the following issue will be invaluable. I am making a database of patient medication orders which has a table with fields "last name", "first name", "chart #" and "Order Date" among others. What I have now is a series of queries that eventually sum up the number of different patients ordering medications in a particular month. THey go like this:

1) Select Query of all fields with [order date] field parameter prompting for the "month as a number followed by a /" (((Parameter: Like [Enter Month as number followed by a /] & "*&quot))

2) Query of this query's results showing only [first name], [last name], and [chart #] and requiring unique values.

3) Query of this query's results counting the [last name] column.

This works, but it has problems. First of all, entering in a month as a number with a / is not pretty. Secondly, only the stats for a single month can be viewed at a time. Ideally, I could bring up a form which would look as follows:

JAN FEB MAR

# of Unique Pts Ordering: 342 254 896


I can visualize the solution involving setting a variable as the search parameter and then leaving it up to the form or a macro or whatever to set that variable for each text box in the form.
Anyhoo, thank you very kindly for reading about my problem and in advance for any advice you may have.
Sincerely,
Andy

 

[MBall2003]

have you thought of a cross-tab query?

 

[MBall2003]

i had the same sort of problem, i needed to sum up hours worked by motnh i thought about doing it similarly to the way you described, but i was told that what it was that i wanted was a crosstab query

 

[lunarmono]

Yes but I guess that I'm not sure how it exactly works and more importantly if I could perform a complicated task like finding unique orders with it.

 

[MBall2003]

yes you can do everything that you can do in a normal query pretty much

after going through the cross-tab query, in the query design you can put your Where conditions there


mball

 

[lunarmono]

I'll give it a whirl. Thanks for the tip mball, I'll let you know how it goes...

 

[MBall2003]

when you're in the query design jsut put the variable that you want to check for criteria, in the Total row , pull down and select where that put your criteria in the criteria field

 

[lunarmono]

Yes! crosstab is very cool. But how do I get it to total only records with a unique combination of first name, last name, and chart #?? What would follow there WHERE? And who's on first?
Thanks
AD