none
Query for latest date for each vendor in a table RRS feed

  • Question

  • I have a table of vendor invoices and need to pull the latest date for each vendor in the list into a query.  Each vendor can have one or more invoices in the table.  The table fields are:

    VENDOR

    INVOICENUM

    INVOICE DATE

    Monday, August 3, 2015 3:52 PM

Answers

  • Hi,

    You could try something like:

    SELECT T1.Vendor, T1.InvoiceNum, T1.InvoiceDate
    FROM tblInvoices T1
    INNER JOIN (SELECT T2.Vendor, Max(T2.InvoiceDate) As LastInvoice
    FROM tblInvoices T2
    GROUP BY T2.Vendor) SQ
    ON T1.Vendor=SQ.Vendor
    AND T1.InvoiceDate=SQ.LastInvoice

    (untested)

    Hope that helps...


    Edit: Oops, thanks guys! Sorry I forgot the GROUP BY clause.
    Monday, August 3, 2015 4:03 PM
  • Accrording to your description, you can try like below:
    SELECT T1.Vendor, T1.InvoiceNum, T1.InvoiceDate
    FROM tbVendorInvoices T1
    INNER JOIN (SELECT T2.Vendor, Max(T2.InvoiceDate) As LastInvoice
    FROM tbVendorInvoices T2 GROUP BY T2.Vendor) Temp
    ON T1.Vendor=Temp.Vendor
    AND T1.InvoiceDate=Temp.LastInvoice


    Tuesday, August 4, 2015 9:25 AM
  • David and DB Guy's suggestions are almost correct, however if your field names are exactly as you listed them here, then your SQL should look like the following:

    SELECT T1.Vendor, T1.InvoiceNum, T1.[Invoice Date]
    FROM Invoice As T1
    INNER JOIN (SELECT T2.Vendor, Max(T2.[Invoice Date]) As MaxDate
    FROM Invoice As T2 GROUP BY T2.Vendor) As Temp
    ON T1.Vendor=Temp.Vendor
    AND T1.[Invoice Date]=Temp.MaxDate

    It would be wise if you change your field names and all object names in your database to exclude the use of spaces.  Though Access allows you to use them, it can become troublesome in your development.  Try using Camel-Case notation instead, e.g. MyFieldNameUsingCamelCase.

    Tuesday, August 4, 2015 10:48 AM

All replies

  • Hi,

    You could try something like:

    SELECT T1.Vendor, T1.InvoiceNum, T1.InvoiceDate
    FROM tblInvoices T1
    INNER JOIN (SELECT T2.Vendor, Max(T2.InvoiceDate) As LastInvoice
    FROM tblInvoices T2
    GROUP BY T2.Vendor) SQ
    ON T1.Vendor=SQ.Vendor
    AND T1.InvoiceDate=SQ.LastInvoice

    (untested)

    Hope that helps...


    Edit: Oops, thanks guys! Sorry I forgot the GROUP BY clause.
    Monday, August 3, 2015 4:03 PM
  • Accrording to your description, you can try like below:
    SELECT T1.Vendor, T1.InvoiceNum, T1.InvoiceDate
    FROM tbVendorInvoices T1
    INNER JOIN (SELECT T2.Vendor, Max(T2.InvoiceDate) As LastInvoice
    FROM tbVendorInvoices T2 GROUP BY T2.Vendor) Temp
    ON T1.Vendor=Temp.Vendor
    AND T1.InvoiceDate=Temp.LastInvoice


    Tuesday, August 4, 2015 9:25 AM
  • David and DB Guy's suggestions are almost correct, however if your field names are exactly as you listed them here, then your SQL should look like the following:

    SELECT T1.Vendor, T1.InvoiceNum, T1.[Invoice Date]
    FROM Invoice As T1
    INNER JOIN (SELECT T2.Vendor, Max(T2.[Invoice Date]) As MaxDate
    FROM Invoice As T2 GROUP BY T2.Vendor) As Temp
    ON T1.Vendor=Temp.Vendor
    AND T1.[Invoice Date]=Temp.MaxDate

    It would be wise if you change your field names and all object names in your database to exclude the use of spaces.  Though Access allows you to use them, it can become troublesome in your development.  Try using Camel-Case notation instead, e.g. MyFieldNameUsingCamelCase.

    Tuesday, August 4, 2015 10:48 AM