Querying patients from a previous month

[medbase]

I have a query with the following fields:

[Clinic] [Month] [Year] [NewPatients]

Some of the calculations I would like to do are based on the number of new patients that a certain clinic received in the previous month. Does anyone have any suggestions on how I can create a field that has the number of new patients from the previous month?

What I have is this:

PrevMonth: DLookUp("[NewPatients]","ClinicQuery","[Month] = " & [Month]-1)

What I can't figure out is how to account for the fact that I have multiple clinics in the [Clinic] field and each clinic has multiple years in which it was receiving new patients. The PrevMonth statement above works fine for the first clinic listed in the query during the first year it received patients (entries are sorted by month and year), but then just repeats these data for each subsequent year and clinic. Any ideas?

Thanks in advance for any advice!

 

[MStef]

I think you have to include all DATE (day,month,year).

 

[raskew]

Hi -
Agree with MStef that a complete date (e.g. DateFirstSeen) will be infinitely easier to deal with than separate Month and Year fields (incidentally, Date, Day, Month and Year are reserved words in Access, e.g. they have special meaning) and shouldn't be used as field names.

Here's an example using the Orders table from the Northwind database. I've used EmployeeID to represent clinics, counting number of orders for a user specified MM/YYYY. This just serves as an example but hopefully will help explain how to count specific occurences during a given month.

SELECT
Orders.EmployeeID AS Clinic
, Count(Orders.OrderID) AS CountOfOrderID
FROM
Orders
GROUP BY
Orders.EmployeeID
, CStr(Format([OrderDate],"mm/yyyy"))
HAVING
(((CStr(Format([OrderDate],"mm/yyyy")))=[enter mm/yyyy]))
ORDER BY
Orders.EmployeeID
, CStr(Format([OrderDate],"mm/yyyy"));

Trying copying/pasting to a DB that contains the Orders table.
When prompted, enter mm/yyyy (e.g. 02/1996) for the month desired.

HTH - Bob

 

[raskew]

Give my query a try. You'll see that it returns the number of patients (OrderID) for each clinic (EmployeeID) for any specified month.

Bob

 

[medbase]

Okay I'll give it a try. I'm pretty new at this, so it may take me a bit to figure everything out. I'll let you know how it turns out...

Thanks for all your help!

 

[medbase]

Raskew - Another more urgent project came up that I had to attend to before getting to your solution. I tried it out today and it works great. Thanks so much for the help!!