Select last 3 months worth of data

[cgemmill1]

I need to create a query that pulls only the last 3 months of data from the previous 24 months. My challenge is that not all the ending dates are the same. For example Org 1 has data ending Jan 31, 2013 while Org 2 has data ending Sept. 2012. Any suggestions?

 

[plog]

that pulls only the last 30 months of data from the previous 24 months

I think I speak for everyone when I say: Huh?

Can you post some sample data from your table(s) including field names along with what you expect this query to return based on that sample data?

 

[cgemmill1]

I have uploaded the results of a cross tabs query. The field names in my query are OrganizationN Name, Medicare Number, Project, HRET_MeasureID, Time, and Measurement Rate.

If you open the attached file and look at the last 3 data points you will see that not all organization have data for all months.

In the end I need to end up with an average rate for the last 3 data points for each facility. Since I have a variety of date ranges I just can't pull the last 90 days. So how can I create a query that pulls the last 3 data points?

I want to compare the baseline rate to the rate from the last 3 data points.

 

[plog]

No file was attached. Before you do, make sure that along with sample input data, you provide me with what you expect based on that input data. I need a target to hit, not just a starting point.

 

[Brianwarnock]

I didn't understand your field names, I expected a Date field but just in case it helps.
Say your table1 has Forg,fdate, fields, then to get the last 3 months records for each org the criteria in the fdate field would be

Between DateAdd("m",-3,(DMax("fdate","table1","Forg='" & [forg] & "'"))) And DMax("fdate","table1","Forg='" & [forg] & "'")

I have assumed that the organisation is alpha.

With 2 Dmax I cant say anything about the performance, there might be a better way.

Brian

 

[cgemmill1]

Brian, thank you for the example. The only trouble I am having with the example is where to put the line of code? Do I create a new variable in the query and add to the critieria line or do I add it to the date field? Is the reference table1 supposed to the table name.

The fields I would use are organization, start_date, numerator, denomoninator, and rate.

 

[cgemmill1]

Brian, I added the code you provided by listing it as the criteria in query under the start_date field. This is the error message I received syntax error "missing operator) in query expression 'Organization Name='Alton"

 

[Brianwarnock]

You have the criteria in the correct place, I suspect that the problem may be due to the "' situation it is not always easy to see the ending quotes are double single double "'" after the = it's single double.
Hope that's it else I don't know.

Brian

 

[Brianwarnock]

And as you have blanks in you names make sure they are in[]

Brian

 

[cgemmill1]

Can I run this without the organization name? Lets pretend we are only doing this for a single organization. What modifications would I make to the code?

 

[Brianwarnock]

No, the Dmax on date requires the organisation name to get the maximum date ie the latest, for that organisation, to run for just one organisation then just include criteria for that organisation in the query.

Having blanks in object names is a recipe for syntax problems avoid them , if necessary use _ instead

Brian

 

[cgemmill1]

What is I want the top 3 dates and the corresponding data for these dates?:banghead:

 

[Brianwarnock]

Pardon! Have you just changed the requirements?

Perhaps you need to explain what you mean by the top 3 dates.

Brian