Art,
something like this

SELECT [AJG revised 1st invoice date].[Customer Number], [AJG
revised 1st invoice date].[Customer Name], Shipments.invoice_number,
Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number,
Shipments.ar_name
FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON
Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
WHERE (Select Min(Shipments.invoice_date) As MinOfDate From Shipments FROM
Shipments INNER JOIN [AJG revised 1st invoice date] ON Shipments.ar_number =
[AJG revised 1st invoice date].[Customer Number])
ORDER BY Shipments.Invoice_number;

The above assumes that your data only has records for the current year.

Jeanette Cunningham

Well, this will find those records where Shipments.invoice_number is equal to
itself. Since everything is by definition equal to itself, that's all the
records, and your WHERE clause does exactly nothing.

What is [AJG Revised 1st Invoice date]?
Why the "as 1"? What's that intended to do?

Maybe this needs to be:

SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number],
[AJG revised 1st invoice date].[Customer Name],
Shipments.invoice_number, Shipments.invoice_date, Shipments.invoice_amount,
Shipments.ar_number, Shipments.ar_name
FROM Shipments
INNER JOIN [AJG revised 1st invoice date]
ON Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
Where Shipments.Invoice_number in
(Select Top 1 Shipments.Invoice_number From Shipments as Latest
Where Latest.invoice_number = Shipments.Invoice_number
AND Latest.invoice_date >= DateSerial(Year(Date()), 1, 1)
Order by Shipments.Invoice_number DESC);

You want the earliest shipment *this year*, not the most recent shipment,
hence the additiona invoice_date criterion and the DESC.

            John W. Vinson [MVP]

Hello All,
I am redoing the query and here is what I have so far:
This query is pulling all the invoices for the customers setup in 2007.  I
need only the first record.

Thanks for your help.
Art  
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#));