Retrieve all records for 10 most recent dates

[Guest]

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. Here is an example of what I want to do:
Let's say I've got 30 days worth of data (30 unique dates). What I want in
my query results are all the records for the 10 most recent days. How can I
do this? My table is set up similar to this:

Item # Sales Date
23456 $476 10/27/2005 (wk 39)
etc. etc. etc.


Thanks,

Jeff

 

[Rick B]

Under the date field put your criteria of...

Date()-10

In other words: pull all records where the date is greater than the current
date minus ten days.

 

[Guest]

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

 

[Guest]

I think it will better to get the max date and subtract 10 days from it, just
in case there was no data entered for few days.
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[John Spencer]

You might try something like the following. It finds all the last 10 days
of sales for each item

SELECT [Item#], Sales, [Date]
FROM ProductSales
WHERE [Date] in
(SELECT Top 10 Tmp.[Date]
FROM ProductSales as Tmp
WHERE Tmp.[Item#] = ProductSales.[Item#]
ORDER BY Tmp.[Date] DESC)

If you need just the last ten days that a sale of any item took place then
modify the above by removing the Where clause for the subquery.

SELECT [Item#], Sales, [Date]
FROM ProductSales
WHERE [Date] in
(SELECT Top 10 Tmp.[Date]
FROM ProductSales as Tmp
ORDER BY Tmp.[Date] DESC)

 

[Guest]

Thanks for your help, this worked just like I wanted it to.

 

[Guest]

After delving into this further, this didn't quite get me the desired
result...but it's close. My historical dates are missing weekends, and I
want to return the last 30 dates. By using your method it seems to only be
returning those dates that are within 30 days of the most recent date, which
makes sense since it's taking a date and subtracting 30. This means that if
my most recent date is Nov 3, it gets records going back to Oct 4, but if I'm
missing some dates it may only return 23 dates. I want it to return the 30
most recent dates regardless of if there are holes in the dates. So If I'm
missing a week, it still returns 30 dates. Sorry for not making this clearer
in my original post.

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
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

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. Here is an example of what I want to do:
Let's say I've got 30 days worth of data (30 unique dates). What I want in
my query results are all the records for the 10 most recent days. How can I
do this? My table is set up similar to this:

Item # Sales Date
23456 $476 10/27/2005 (wk 39)
etc. etc. etc.


Thanks,

Jeff

 

[John Spencer]

You will need to use a top query to get the matching records. Without
details I can only suggest something like the following.

Select TableName.*
From TableName
Where [Date] In (SELECT Top 30 [date]
FROM TableName
GROUP BY [Date]
ORDER BY [Date] Desc)

After delving into this further, this didn't quite get me the desired
result...but it's close. My historical dates are missing weekends, and I
want to return the last 30 dates. By using your method it seems to only
be
returning those dates that are within 30 days of the most recent date,
which
makes sense since it's taking a date and subtracting 30. This means that
if
my most recent date is Nov 3, it gets records going back to Oct 4, but if
I'm
missing some dates it may only return 23 dates. I want it to return the
30
most recent dates regardless of if there are holes in the dates. So If
I'm
missing a week, it still returns 30 dates. Sorry for not making this
clearer
in my original post.

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
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck

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. Here is an example of what I want to do:
Let's say I've got 30 days worth of data (30 unique dates). What I
want in
my query results are all the records for the 10 most recent days. How
can I
do this? My table is set up similar to this:

Item # Sales Date
23456 $476 10/27/2005 (wk 39)
etc. etc. etc.


Thanks,

Jeff