Query to find last record for each item

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Folks,

I need help with this one.
I have a table with the following:
Transaction ID (Primary Key & auto number), ProductID, Value.

The table contains many records for each ProductID, e.g:

01, 01, 12
02, 02, 08
03, 01, 15
04, 01, 57
05, 02, 18

I need ot get the last record for each ProductID. So the query needs to
return:

04, 01, 57
05, 02, 18

Anyone got any ideas on how to go about it?

Many Thanks in Advance

Rich
 
Richard said:
Folks,

I need help with this one.
I have a table with the following:
Transaction ID (Primary Key & auto number), ProductID, Value.

The table contains many records for each ProductID, e.g:

01, 01, 12
02, 02, 08
03, 01, 15
04, 01, 57
05, 02, 18

I need ot get the last record for each ProductID. So the query needs to
return:

04, 01, 57
05, 02, 18
Click to expand...
Hi Rich,

I have assumed the leading zeroes
are just for "display" and that all fields
are long (and that table name is "tblRich"..
change SQL's to use your actual table name).

-----------------
2-queries solution:
-----------------
qryMaxTrans:

"separate data into groups of
ProductId's and find the largest
Transaction Id for each group"

SELECT
Max(t1.[Transaction ID]) AS MaxTrans,
t1.ProductId
FROM tblRich AS t1
GROUP BY t1.ProductId;

producing:

MaxTrans ProductId
4 1
5 2

qryLastTransactions:

"for every pair of ProductID/MaxTrans
found in previous query, return the pair
and its [Value] ('Value' is a reserved word
so at least put brackets around it, or better
yet, change name of this field in your table)"

SELECT
t2.[Transaction ID],
t2.ProductId,
t2.[Value]
FROM qryMaxTrans AS q1
INNER JOIN tblRich AS t2
ON
(q1.ProductId = t2.ProductId)
AND
(q1.MaxTrans = t2.[Transaction ID]);

Transaction ID ProductId Value
4 1 57
5 2 18

-----------------
1-query solution:
-----------------

SELECT
t2.[Transaction ID],
t2.ProductId,
t2.[Value]
FROM
(SELECT
Max(t1.[Transaction ID]) AS MaxTrans,
t1.ProductId
FROM tblRich AS t1
GROUP BY t1.ProductId) AS q1
INNER JOIN tblRich AS t2
ON (q1.MaxTrans = t2.[Transaction ID])
AND (q1.ProductId = t2.ProductId);

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Gary said:
Folks,

I need help with this one.
I have a table with the following:
Transaction ID (Primary Key & auto number), ProductID, Value.

The table contains many records for each ProductID, e.g:

01, 01, 12
02, 02, 08
03, 01, 15
04, 01, 57
05, 02, 18

I need ot get the last record for each ProductID. So the query needs to
return:

04, 01, 57
05, 02, 18
Click to expand...

Hi Rich,

I have assumed the leading zeroes
are just for "display" and that all fields
are long (and that table name is "tblRich"..
change SQL's to use your actual table name).

-----------------
2-queries solution:
-----------------
qryMaxTrans:

"separate data into groups of
ProductId's and find the largest
Transaction Id for each group"

SELECT
Max(t1.[Transaction ID]) AS MaxTrans,
t1.ProductId
FROM tblRich AS t1
GROUP BY t1.ProductId;

producing:

MaxTrans ProductId
4 1
5 2

qryLastTransactions:

"for every pair of ProductID/MaxTrans
found in previous query, return the pair
and its [Value] ('Value' is a reserved word
so at least put brackets around it, or better
yet, change name of this field in your table)"

SELECT
t2.[Transaction ID],
t2.ProductId,
t2.[Value]
FROM qryMaxTrans AS q1
INNER JOIN tblRich AS t2
ON
(q1.ProductId = t2.ProductId)
AND
(q1.MaxTrans = t2.[Transaction ID]);

Transaction ID ProductId Value
4 1 57
5 2 18

-----------------
1-query solution:
-----------------

SELECT
t2.[Transaction ID],
t2.ProductId,
t2.[Value]
FROM
(SELECT
Max(t1.[Transaction ID]) AS MaxTrans,
t1.ProductId
FROM tblRich AS t1
GROUP BY t1.ProductId) AS q1
INNER JOIN tblRich AS t2
ON (q1.MaxTrans = t2.[Transaction ID])
AND (q1.ProductId = t2.ProductId);

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
Thanks Gary, worked a treat.

Rich
 
Back
Top