Last 3 Months of Dataset

[dgriffis1231]

So I have a "production table". It has the following fields:

ID
DATE
OIL
GAS

Each ID has production in different time periods. I need to find the end of each of those time periods and pull the last 3 months of data. >=DateAdd("m",-3,Date()) ... this formula did not work because it used today's date. A dataset for an ID can has a last production of 12/31/2018 or 07/31/2019.

 

[June7]

So you need max date for each ID and use that in DateAdd calc.

A DMax() domain aggregate function might serve but they can cause slow query performance.

Another approach is an aggregate (GROUP BY) query to pull Max(fieldname) for each ID then JOIN that query to original dataset.

 

[The_Doc_Man]

Just to clarify, are you saying that you have a bunch of records that give details about your "production" and these records can span many months, but for a given ID, you only want the the last three months? BUT different IDs will have different end dates, so you want a "floating" 3-month window that can differ from one ID to the next?

For further clarification, you have a field you call DATE and you should NEVER do that. The word "DATE" is a reserved word and also is the name of an intrinsic function, so you can easily confuse Access with a field of that name. I'm going to call it THEDATE for this discussion.

Build a couple of queries. You didn't name your table, so I'll call it PTbl just for discussion, OK?

Query1:

SELECT ID, MAX( THEDATE ) AS MAXDATE FROM PTBL GROUP BY ID ;

Query 1 gives you the individual end dates for each ID.

Query2:

SELECT ID, THEDATE, OIL, GAS 
FROM PTBL INNER JOIN QUERY1 ON QUERY1.ID = PTBL.ID 
WHERE PTBL.THEDATE BETWEEN DATEADD( "m", -3, QUERY1.MAXDATE) AND QUERY1.MAXDATE 
ORDER BY PTBL.ID, PTBL.THEDATE ;

This will give you the data in ID order and within each ID, the records will be in date order. I don't know if that is really what you wanted, but it might be close. You'll have to do things about the names yourself.