Pulling last receipt date in table

[GEORGIA]

I have a table with serial numbers, receipt dates and receipt number. How do I pull a last receipt date for each serial number? Last meaning closet to today. There are mutiple receipt number with dates for each serial, but I just want to pull latest (newest) receipt date for the serial.

Thank you!

 

[Gerald Stanley]

Try something along the lines of
SELECT serialNo, receiptDate, receiptNo
FROM YourTable AS T1
WHERE receiptDate IN (SELECT Max(receiptDate) FROM
YourTable WHERE serialNo = T1.serialNo)

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a table with serial numbers, receipt dates and

receipt number. How do I pull a last receipt date for each
serial number? Last meaning closet to today. There are
mutiple receipt number with dates for each serial, but I
just want to pull latest (newest) receipt date for the
serial.

 

[GEORGIA]

This kind of worked, but it didn't give me everything I needed.
It only pulled the serial number with the newest receipt only. For example, if Serial # 1234 's last receipt is 02/15/2004 and serial # 4567's last receipt is 06/25/2004; it gave me information for serial # 4567 and not 1234. Is there anyway i can pull whatever the last (newest) receipts for each serail number?

Thank you!

 

[Gerald Stanley]

Can you reply with the actual SQL that you have used. The
sample below should work as you want.

Gerald Stanley MCSD

-----Original Message-----
This kind of worked, but it didn't give me everything I needed.
It only pulled the serial number with the newest receipt

only. For example, if Serial # 1234 's last receipt is
02/15/2004 and serial # 4567's last receipt is 06/25/2004;
it gave me information for serial # 4567 and not 1234. Is
there anyway i can pull whatever the last (newest) receipts
for each serail number?

 

[Gerald Stanley]

The subquery needs to refer back to the main query. Try
the following
SELECT T1.SERIAL_NO, T1.ISSUE_DATE, T1.INVOICE_NO
FROM [all canon funding] AS T1
WHERE ((([all canon funding].ISSUE_DATE) In (SELECT
max(issue_date) FROM [all canon funding] WHERE serial_no =
T1.serial_no)));

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
this is the SQL, hopefully it's something i did wrong.

SELECT [all canon funding].SERIAL_NO, [all canon

funding].ISSUE_DATE, [all canon funding].INVOICE_NO

FROM [all canon funding]
WHERE ((([all canon funding].ISSUE_DATE) In (SELECT

max(issue_date) FROM [all canon funding] WHERE serial_no =
serial_no)));