How do I choose the 1st invoice record from each customer?

[Art@ISCO]

I need to run a query to pull the 1st invoice from each new customer for the
year. Here is what I have done so far (probably not correct, but I tried).

Thanks,
Art

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 1
Where Shipments.invoice_number = Shipments.Invoice_number
Order by Shipments.Invoice_number)

 

[Jeanette Cunningham]

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

 

[John W. Vinson]

I need to run a query to pull the 1st invoice from each new customer for the
year. Here is what I have done so far (probably not correct, but I tried).

Thanks,
Art

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 1
Where Shipments.invoice_number = Shipments.Invoice_number
Order by Shipments.Invoice_number)

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]

 

[Art@ISCO]

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#));

 

[Jeanette Cunningham]

Art,
try this

SELECT RM00101USA.CUSTNBR, 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.CUSTNBR =
Shipments.ar_number
WHERE (((Shipments.invoice_date)=(SELECT Min(Shipments.invoice_date) AS
MinOfinvoice_date FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNBR
= Shipments.ar_number) And [RM00101USA].[CREATDDT]>#12/31/2006#));

Jeanette Cunningham

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#));

I need to run a query to pull the 1st invoice from each new customer for
the
year. Here is what I have done so far (probably not correct, but I
tried).

Thanks,
Art

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 1
Where Shipments.invoice_number = Shipments.Invoice_number
Order by Shipments.Invoice_number)

 

[John W. Vinson]

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#));

The first record for each custnmbr in creatddt order? "First record" is
ambiguous...

If so:

SELECT 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#))
AND Shipments.invoice_date = (SELECT Min([invoice_date]) FROM Shipments AS S
WHERE S.ar_number = RM00101USA.CUSTNMBR AND S.[invoice_date] >= #1/1/07# AND
S.invoice_date < #1/1/08#);

John W. Vinson [MVP]