Pulling last receipt date in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
Click to expand...
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.
 
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!
 
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
Click to expand...
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?
 
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
Click to expand...
funding].ISSUE_DATE, [all canon funding].INVOICE_NO
FROM [all canon funding]
WHERE ((([all canon funding].ISSUE_DATE) In (SELECT
Click to expand...
max(issue_date) FROM [all canon funding] WHERE serial_no =
serial_no)));
 
Back
Top