ACCESS FEED: RE: [MS_AccessPros] Last Receipt with Vendor

Kamis, 27 Februari 2014

RE: [MS_AccessPros] Last Receipt with Vendor

Hi Doyce

Firstly, you should not be using Last in this context. First and Last operate on a "bucket" of records and *might* return the values from the first and last records entered, but they cannot be relied on to do even this. If you want the *latest* date in a date field (or largest value in any other field) then use Max([fieldname]).

Secondly, I'm a bit worried by your "_55" suffixes in your field names. I am suspicious that this indicates a lack of normalisation, but that's another matter.

Back to your query, this will give you the VENID_55 value from the [PO Receipts] record with a given PRTNUM_55 value and the latest date in TNXDTE_55:

SELECT TOP 1 v.VENID_55
FROM [PO Receipts] AS v
WHERE v.PRTNUM_55=<some value>
ORDER BY v.TNXDTE_55 DESC, v.[PO Receipts PK];

(Note that the primary key needs to be included in the ORDER BY in case there were two orders for the same product on the same day. This prevents duplicate records being returned by the TOP 1 predicate)

You can then use this query as a subquery to get the most recent vendor for every part:

  SELECT
    p.PRTNUM, p.PRTNAME
  (SELECT TOP 1 v.VENID_55
   FROM [PO Receipts] AS v
   WHERE v.PRTNUM_55=p.PRTNUM
   ORDER BY v.TNXDTE_55 DESC, v.[PO Receipts PK])
  AS LastVendor
FROM Parts AS p
ORDER BY p.PRTNUM;

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of winberry.doyce@roadsysinc.com
Sent: Friday, 28 February 2014 11:30
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Last Receipt with Vendor

Friends, I would like to find the vendor for the last time a part number was received and put it on a report. We can buy the same part from multiple vendors I just want the very last one. This query gets me that last receipt from each vendor.
SELECT [PO Receipts].PRTNUM_55, Last([PO Receipts].TNXDTE_55) AS LastOfTNXDTE_55, [PO Receipts].VENID_55
FROM [PO Receipts]
GROUP BY [PO Receipts].PRTNUM_55, [PO Receipts].VENID_55
ORDER BY [PO Receipts].PRTNUM_55;
I just want the vendor from the last receipt. How do I do that?
Doyce