Qry last invoice date for a customer Item

[gpierce9]

I have trouble with an Access Qry to find the last invoice date for each customer item.

The below retrieves only the 12/29/2017 last dates for 120 customer items.
Should have been more EG some cust items were last sold on earlier dates.
So the below is only using the highest invoice date.

Any ideas how to get all customer items last time purchased? thanks glen



SELECT tblOldSellPrices.cusid, tblOldSellPrices.ItemID, tblOldSellPrices.InvoiceDate, tblOldSellPrices.SalesOrderID, Round(Nz([InvoicedSales])/Nz([invoicedQty]),2) AS [USP Invoiced]
FROM tblOldSellPrices
WHERE (((tblOldSellPrices.InvoiceDate)=(SELECT MAX([invoiceDate])
FROM [tblOldSellPrices]
)))


ORDER BY tblOldSellPrices.cusid, tblOldSellPrices.ItemID;

[ranman256]

you want 2 queries:
Q1, get the Max date:
select [custID],Max([invoiceDate]) from tblOldSellPrices

then Q2 uses Q1 to pull tCustomer table data:
select Q1.[CustID],tCustomer.[CustName],Q1.[invoiceDate] from Q1,tCustomer where Q1.CustID = tCustomer.CustID

[gpierce9]

Ranman - I don't see the ItemID in your note.
For example, I need to know when CusID 12345 last bought
all his items (itemID). EG when Jones last bought a screw driver, a hammer, a saw.
He might be buying 200 items. I need to see the last time EACH itemID was
last purchased by Jones. Any more ideas? thanks.

you want 2 queries:
Q1, get the Max date:
select [custID],Max([invoiceDate]) from tblOldSellPrices

then Q2 uses Q1 to pull tCustomer table data:
select Q1.[CustID],tCustomer.[CustName],Q1.[invoiceDate] from Q1,tCustomer where Q1.CustID = tCustomer.CustID

[gpierce9]

cusid	ItemID	InvoiceDate	SalesOrderID	USP Invoiced
583	10C1	8/15/2017	0003766266	1.42
583	10C1	3/28/2017	0003352616	1.33
583	10C1	7/22/2016	0002768198	1.32

Ranman - above in data from my qry. I only want the first row.
Jones #583 last bought 10C1 on 8/5/17.

I want to see every customers items last purchased date. thanks glen

Ranman - I don't see the ItemID in your note.
For example, I need to know when CusID 12345 last bought
all his items (itemID). EG when Jones last bought a screw driver, a hammer, a saw.
He might be buying 200 items. I need to see the last time EACH itemID was
last purchased by Jones. Any more ideas? thanks.

[orange]

I took your sample data and modified it to show more items and another Customer.
My understanding is you want the latest Date per Item per Customer

Here is the modified test data:

ID	cusid	ItemID	InvoiceDate	SalesOrderID	USPInvoiced
1	583	10C1	15-Aug-2017	3766266	1.42
2	583	10C2	28-Mar-2017	3352616	1.33
3	583	10C1	22-Jul-2016	2768198	1.32
4	526	11L9	08-Feb-2014	2769308	3.45
5	526	11L9	08-Dec-2015	2669308	3.21

Here is the query:

SELECT Gpierce.cusid
, Gpierce.ItemID
, Max(Gpierce.InvoiceDate) AS MaxOfInvoiceDate
FROM Gpierce
GROUP BY Gpierce.cusid, Gpierce.ItemID
ORDER BY ItemID;

Here is the result:

cusid	ItemID	MaxOfInvoiceDate
583	10C1	15-Aug-2017
583	10C2	28-Mar-2017
526	11L9	08-Dec-2015

[gpierce9]

Ranman - You have the gist in your sample example. glen

[gpierce9]

It works. Here is a summary in case some one needs to learn what Ranman taught me.

I even found situations where a customer item was bought twice on two different sales orders
and 2 different sell prices on the same day. So my second query uses a MAX as well in order
to get the higher priced cus item for that last day bought.


I changed Ranman's test table GPierce to tblOldSellPrices.

I made 2 Access queries as Ranman said. 'Qry8287B Last Invoice' and a second one called 'Qry8287C Last Sell Price'

Here is 'Qry8287B Last Invoice' SQL info.

SELECT tblOldSellPrices.cusid, tblOldSellPrices.ItemID, Max(tblOldSellPrices.InvoiceDate) AS LastInvoiceDate
FROM tblOldSellPrices
GROUP BY tblOldSellPrices.cusid, tblOldSellPrices.ItemID;



Here is 'Qry8287C Last Sell Price' SQL info.

SELECT tblOldSellPrices.cusid, tblOldSellPrices.ItemID, [Qry8287B Last Invoice].LastInvoiceDate, Max(tblOldSellPrices.UnitSellPrice) AS MaxOfUnitSellPrice
FROM [Qry8287B Last Invoice] INNER JOIN tblOldSellPrices ON ([Qry8287B Last Invoice].cusid = tblOldSellPrices.cusid) AND ([Qry8287B Last Invoice].ItemID = tblOldSellPrices.ItemID) AND ([Qry8287B Last Invoice].[LastInvoiceDate] = tblOldSellPrices.InvoiceDate)
GROUP BY tblOldSellPrices.cusid, tblOldSellPrices.ItemID, [Qry8287B Last Invoice].LastInvoiceDate;


As a reminder, tblOldSellPrices contains cus-item-invoice date - unit sell price.

The customer items could have been sold many times during the year.
But I wanted, as Ranman shows, the last time the customer bought the item.

It works fine now. Thanks Ranman. GlenP.