Retrieve all records for 30 max dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted a question last week that I thought was satisfactorily answered,
until I did some further testing. After reviewing the results, I can see I
was not 100% clear in my question. Here's what I'm after:

I have a basic table with product data listed by day. This table will
continue to grow every day since I will append each new day's data to the
table. (BUT, there is the possibilty of missing dates. For instance, I
have historical data with no data for weekends.) Here is an example of what
I want to do:

Let's say I've got 60 days worth of data (60 unique dates), but with
periodic missing dates. What I want in my query results are all the records
for the 30 most recent days (or 30 max dates). How can I do this? My table
is set up similar to this:

Item # Instock Date
23456 99.5% 10/27/2005
etc. etc. etc.

The response listed below got me close, but with the missing dates it
returned less than 30 dates.

-----------------------------------------------------------------------------------------------------------
Try this, filtering on the last date - 10 days

Select TableName.* From TableName Where [Date] > = (select Max([date]) From
TableName) - 10

One more thing, if the field name is date it can cause some problem because
date is a resurve name in Access
 
You could set up a Totals query which just contains the Date field as Group
By then set Top Values property to 30

Link this query to your table with a relationship on the date field and it
should pull out all the records for the dates in Query1

Hope this helps

Sheila
 
Back
Top