Max Date < A Certain Date

[foxhound]

I need a query or sql to pull records from a table where Max Date is less than a certain date. However, if the Max Date is greater than "the certain date" I don't want to exclude that record but instead pull the next Max Date for that record. Any suggestions?

Foxhound

 

[Andrew Fergus]

Hi foxhound,

I don't understand the problem 100%. Is "Max Date" a value entered by the user at a prompt either via a query, form or a report? Or is it a value stored in a table? What exactly is "that certain date" - is it a value stored in a table as part of a record? Plus I'm not sure what you mean by "but instead pull the next Max Date for that record".

From my limited understanding of the problem it sounds like you might want two different queries - one to pull the records where "that certain date" is less than the existing "Max Date" and the second to define a new "Max Date". Is this on the right track?

Andrew

 

[foxhound]

Thank you for responding. Sorry for the confusion –I will try to clarify.

I have a list of individuals with 10 fields (including: name, refnum, date, state, loc, note, etc…). First, I want to pull all records from the table where the date is less than another that I choose (when I choose 01/01/2005 it will show records for 2001, 2002, 2003, etc…). Then, if there are duplicate entries for the refnum field, I want to pull the record with the closest date to the one I chose before.

I appreciate any assistance you can offer.

 

[Andrew Fergus]

Hi foxhound,

[Original response edited due to lack of testing on my part]

You will need to do this in a two-step process if you are using queries.

The first query selects all data less than your date range (by entering your criteria into the criteria section of your query under "date") and the 2nd query uses the "max" function in a "total" query. You can't use the two functions in one query - it won't work (just tested it and hence I have edited this response).

For the 2nd query, add query 1, View -> Totals, select the fields you want, under "date" select "Max" - leave the rest as "Group By". The "date" criteria is set in the first query (e.g. <=[enter date] or <=#01/01/2005# etc.).

Please note that where some of the other variables change values (i.e. same refnum, different dates (ok), but say different notes) then the "group by" function will show the refnum twice given the notes changed (for example). So, you might only want to have the minimal number of fields you need on the 2nd query to identify the records you want. You can then have a 3rd query that is based on the 2nd query to pull through the rest of the data you need.

I hope that is not too confusing.

HTH, Andrew.