Ten most recent records by date

[Guest]

Hello All and thanks in advance for any help you can provide,

I am trying to create a query that grabs the ten most recent deliveries by
part number and then sum the number of items delivered in an inventory db
that I am creating. There are a few different tables linked by part number
and delivery with a date associated with each delivery. There are a few
hundred part numbers and I wanted to avoid creating a table for every part
number just to grab the ten most recent deliveries if that makes sense.

Here is the SQL for the table that sorts and counts the deliveries by part
number and it sums the number delivered by part number. It counts all of the
records and I need it to count just the ten most recent.

SELECT DISTINCTROW [Past Deliveries].[Part Number], Sum([Past
Deliveries].[Number Delivered]) AS [Sum Of Number Delivered], Count(*) AS
[Count Of Past Deliveries]
FROM [Past Deliveries]
GROUP BY [Past Deliveries].[Part Number];

 

[John Spencer]

You need a coordinated sub-query in a where clause in your query. Probably
something like the following.
Assumption: There is a field [Date] that represents the delivery date.


SELECT DISTINCTROW [Past Deliveries].[Part Number],
Sum([Past Deliveries].[Number Delivered]) AS [Sum Of Number Delivered],
Count(*) AS [Count Of Past Deliveries]
FROM [Past Deliveries]
WHERE [Past Deliveries].[Date] IN
(SELECT TOP 10 T.[Date]
FROM [Past Deliveries] as T
WHERE T.[Part Number] = [Past Deliveries].[Part Number]
ORDER by T.[Date] Desc)
GROUP BY [Past Deliveries].[Part Number];

This will give you more than ten deliveries in cases where there is a tie
for the last date position. It can also be slow - depending on your
indexing scheme and the size of your data set.

 

[Michel Walsh]

Hi,


Your table as a primary key, pk, and a date/time stamp, dtstamp, then


SELECT [part number],
SUM([number delivered]), As sumOfDelivered,
COUNT(*) AS countOfDelivered

FROM [past deliveries] As pd

WHERE pk IN(SELECT TOP 10 a.pk
FROM [past deliveries] as a
WHERE a.[part Number] = pd.[ partNumber]
ORDER BY a.dtstamp DESC)

GROUP BY [part number]




which can be quite long if the table is quite large.


Another solution is to rank each record, within each part number, and keep
only the records having the rank <= 10, the, make a second query that will
group-sum-count on that restricted set of records.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Create a Query:

SELECT [M1].[Part Number], [M1].[Number Delivered]
FROM [Past Deliveries] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 1 M2.[DateFieldName]
FROM [Past Deliveries] as M2
WHERE M2.[Part Number] =M1.[Part Number]
ORDER BY M2.[DateFieldName] Desc)

I assume that you have a date field that indicate when the record was
entered. If you don't then use any field that indicate the order the records
were entered
This query will return the 10 most recent enteries for Item.
To get the some, create another query that is based on the above query

 

[Guest]

Sorry, change the Top 1 to Top 10
--
\\// Live Long and Prosper \\//

Create a Query:

SELECT [M1].[Part Number], [M1].[Number Delivered]
FROM [Past Deliveries] AS M1
WHERE M1.[DateFieldName] In (SELECT Top 1 M2.[DateFieldName]
FROM [Past Deliveries] as M2
WHERE M2.[Part Number] =M1.[Part Number]
ORDER BY M2.[DateFieldName] Desc)

I assume that you have a date field that indicate when the record was
entered. If you don't then use any field that indicate the order the records
were entered
This query will return the 10 most recent enteries for Item.
To get the some, create another query that is based on the above query

--
\\// Live Long and Prosper \\//

Hello All and thanks in advance for any help you can provide,

I am trying to create a query that grabs the ten most recent deliveries by
part number and then sum the number of items delivered in an inventory db
that I am creating. There are a few different tables linked by part number
and delivery with a date associated with each delivery. There are a few
hundred part numbers and I wanted to avoid creating a table for every part
number just to grab the ten most recent deliveries if that makes sense.

Here is the SQL for the table that sorts and counts the deliveries by part
number and it sums the number delivered by part number. It counts all of the
records and I need it to count just the ten most recent.

SELECT DISTINCTROW [Past Deliveries].[Part Number], Sum([Past
Deliveries].[Number Delivered]) AS [Sum Of Number Delivered], Count(*) AS
[Count Of Past Deliveries]
FROM [Past Deliveries]
GROUP BY [Past Deliveries].[Part Number];

 

[Guest]

This was the easiest and best solution, Thank you so much John, you just made
my life a hundred times easier!
--
All truly great thoughts are conceived by walking. - Friedrich Nietzsche

You need a coordinated sub-query in a where clause in your query. Probably
something like the following.
Assumption: There is a field [Date] that represents the delivery date.


SELECT DISTINCTROW [Past Deliveries].[Part Number],
Sum([Past Deliveries].[Number Delivered]) AS [Sum Of Number Delivered],
Count(*) AS [Count Of Past Deliveries]
FROM [Past Deliveries]
WHERE [Past Deliveries].[Date] IN
(SELECT TOP 10 T.[Date]
FROM [Past Deliveries] as T
WHERE T.[Part Number] = [Past Deliveries].[Part Number]
ORDER by T.[Date] Desc)
GROUP BY [Past Deliveries].[Part Number];

This will give you more than ten deliveries in cases where there is a tie
for the last date position. It can also be slow - depending on your
indexing scheme and the size of your data set.

Hello All and thanks in advance for any help you can provide,

I am trying to create a query that grabs the ten most recent deliveries by
part number and then sum the number of items delivered in an inventory db
that I am creating. There are a few different tables linked by part number
and delivery with a date associated with each delivery. There are a few
hundred part numbers and I wanted to avoid creating a table for every part
number just to grab the ten most recent deliveries if that makes sense.

Here is the SQL for the table that sorts and counts the deliveries by part
number and it sums the number delivered by part number. It counts all of
the
records and I need it to count just the ten most recent.

SELECT DISTINCTROW [Past Deliveries].[Part Number], Sum([Past
Deliveries].[Number Delivered]) AS [Sum Of Number Delivered], Count(*) AS
[Count Of Past Deliveries]
FROM [Past Deliveries]
GROUP BY [Past Deliveries].[Part Number];