Limiting query records output

[Guest]

I have a set of records in a table of batches of products run in my factory.
these records are sorted by product, batch number and date (date processed),
all associated runtime, downtime, etc... is captured in these records as
well. There are 500 different kinds of products we make. Is there a way in
access to use a query to show the last 10 batches by date (most recent and
back in past from there) for each product?

Thanks
James

 

[Edward G]

Perhaps there is a simple way to do this and one of the MVP's here will be
able to give you one.
I tackled something like this recently and it was pretty time consuming the
way I did it as it involved querying
queries several layers deep. The basic idea is that you start out with a
simply query that includes the date field and
you pull up the Totals row and Group By the Product field etc and select Max
for your date field.
That is layer 1. Second layer is same basic thing set to find max in the
date field except you add the first query to the design grid (field probably
called Max of Date) and set the criteria of the date field in the second
query to be <[Max of Date]. This gives you the second to the last max date
because Access is looking for the Max date less than the last max date you
found. And you'd have to do this 10 times. Unless someone else
has a better way.

Ed G

 

[Edward G]

Oops. Forgot to mention that ultimately, you will need a query to pull all
the MaxDates from the 10 different queries
you built previously. Unless, like I said, someone else has a better way.

Ed G

Perhaps there is a simple way to do this and one of the MVP's here will be
able to give you one.
I tackled something like this recently and it was pretty time consuming the
way I did it as it involved querying
queries several layers deep. The basic idea is that you start out with a
simply query that includes the date field and
you pull up the Totals row and Group By the Product field etc and select Max
for your date field.
That is layer 1. Second layer is same basic thing set to find max in the
date field except you add the first query to the design grid (field probably
called Max of Date) and set the criteria of the date field in the second
query to be <[Max of Date]. This gives you the second to the last max date
because Access is looking for the Max date less than the last max date you
found. And you'd have to do this 10 times. Unless someone else
has a better way.

Ed G

I have a set of records in a table of batches of products run in my factory.
these records are sorted by product, batch number and date (date processed),
all associated runtime, downtime, etc... is captured in these records as
well. There are 500 different kinds of products we make. Is there a

way

in
access to use a query to show the last 10 batches by date (most recent and
back in past from there) for each product?

Thanks
James

 

[John Spencer (MVP)]

You can get the top 10 of each product group by using a coordinated sub-query.
That would look something like the following.

SELECT T.*
FROM [YourTable] as T
WHERE T.YourPrimaryKey IN
(SELECT TOP 10 T1.YourPrimaryKey
FROM [YourTable] as T1
WHERE T1.Product = T.Product
ORDER BY T1.BatchDate Desc, T1.PrimaryKey)

If you can't figure this out, I suggest you post the query that you have now and
perhaps someone can then work out the exact SQL statement for you.