How to choose 1st invoice from customer masterfile

[Art@ISCO]

SELECT DISTINCT RM00101USA.CUSTNMBR, RM00101USA.CUSTNAME,
RM00101USA.CUSTCLAS, RM00101USA.CREATDDT, Shipments.invoice_number,
Shipments.invoice_date, RM00101USA.ADDRESS1, RM00101USA.ADDRESS2,
RM00101USA.CITY, RM00101USA.STATE, Shipments.invoice_amount
FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNMBR =
Shipments.ar_number
WHERE (((RM00101USA.CREATDDT)>#12/31/2006#));

 

[John Spencer]

You can try the following.

SELECT DISTINCT RM00101USA.CUSTNMBR
, RM00101USA.CUSTNAME
, RM00101USA.CUSTCLAS
, RM00101USA.CREATDDT
, Shipments.invoice_number
, Shipments.invoice_date
, RM00101USA.ADDRESS1
, RM00101USA.ADDRESS2
, RM00101USA.CITY
, RM00101USA.STATE
, Shipments.invoice_amount
FROM (RM00101USA INNER JOIN Shipments
ON RM00101USA.CUSTNMBR = Shipments.ar_number)
INNER JOIN (
SELECT ar_number, Min(Invoice_Date) as FirstInvoice
FROM Shipments
GROUP BY Shipments.ar_number
) as A
ON Shipments.Ar_number = A.ar_number AND
Shipments.Invoice_Date = A.FirstInvoice
WHERE (((RM00101USA.CREATDDT)>#12/31/2006#));

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..