Selecting records based on dates

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

I have a large (over 200K records) table, and I need to
select records based on a range of dates. For each unique
ID in the table, I need to compare the range of dates in
the Service Date field, and select the records with the
first and last dates in that range. Each unique ID number
will have at a minimum two records in the table; many will
have as many as 100. I then want to make these selected
records into a new table.

I hope I explained this clearly. If I can't figure out a
way to program this I'm going to have to manually select
all these records, so any help would be appreciated.
 
Hi Lee,

A couple of ideas come to mind. You could create a
summary query, group by ID, then insert the date field
twice. For the first, choose Min, and for the second
choose Max. Then, create a second query, insert your
table and your summary query and join them on ID. Set
your criteria in your date field to be MinDate OR MaxDate.

You could also do this all at once in one query by using
the DMin() and DMax() functions to lookup the min date
and max date for a given ID, and similarly use these to
set your criteria.

One note, you may get unexpected results if a given ID
could have more than one record on a given date.

Hopefully this will give you some ideas. Post back if
you want more info on either of the options above.

-Ted Allen
 
Ted,
Thanks for your suggestions. I think the first option
would probably work better, as I will certainly have IDs
that have more than one record for the same date.

I have created a query containing just the ID, and the
date field, inserted twice. I grouped on ID, and for the
first date field was set to Min and the second was set to
Max. It pulled the expect results. I am having trouble
getting the second part of your suggestion to work. When
I create the second query, I joined the table and the
summary query on the ID field, but when I try using
MinDate or MaxDate in the Criteria field, it tells me I
have a data type mismatch in the criteria expression.
Maybe I'm just not understanding how to use this last
part.

Thanks, Lee
 
Hi Lee,

Usually when I get that error, it means that the dataset
has some null values that create problems (the first
summary query). Are any of the values blank in the first
query that you ran? If so, you may want to use an iif
statement to assign a dummy date to records where MinDate
or MaxDate is null.

Another idea, when you entered MinDate and MaxDate in the
criteria row for the date field in the second query, did
you surround them by brackets? Do they for sure match the
exact field name from the first query? You may want to
post back the exact criteria expression, or the entire
sql text.

One other note regarding the output that you should get
if this works properly. If a given ID will have multiple
entries on a given date, you will get multiple output
lines for that date in your final query (that warning
applied similarly to both of the methods that I was
passing along earlier). If you want only one output
record for each min/max date, you will need to create a
grouping query to group by date. This will be very easy
if you are only pulling the ID and the dates, but if
there are other fields associated it may be tricky if you
can't define which record you want to return when you
have multiples on the same date.

Hopefully this will help give you some ideas. If it is
still giving you problems try pasting the sql text of
your queries in a return post. Also, let me know if you
have any questions about grouping the records to have
only one of each min and max date.

-Ted Allen
 
Back
Top