CODEWITHSUNDEEP
sqlms-access
jonplaca
jonplaca

Reputation: 827

MS ACCESS: Unable to retrieve LAST Price Paid based on Maximum Date

OBJECTIVE

Develop a sales catalog for a COMPANY ID based on ITEM ID and latest PRICE paid (based on LAST SHIP DATE).

APPROACH

  1. Pull in CUSTOMER, SALES, ITEM Tables
  2. Run Query Link tables based off of CUSTOMER ID and ITEM to understand purchase history
  3. Export a table showing a COMPANY ID, ITEM ID, LAST SALES PRICE, LAST SHIP DATE

CODE

SELECT 
[Sales Order Details (F42119)].SDAN8 AS [COMPANY ID], 
[Sales Order Details (F42119)].SDITM AS [ITEM ID], 
[Sales Order Details (F42119)].SDAITM AS STYLE, 
(CCur([SDLPRC])/10000) AS PRICE, 
Max([Sales Order Details (F42119)].SDDRQJ) AS [LAST SHIP DATE] 

INTO [Table - Sales Details]
FROM [Sales Order Details (F42119)]

GROUP BY 
[Sales Order Details (F42119)].SDAN8, 
[Sales Order Details (F42119)].SDITM, 
[Sales Order Details (F42119)].SDAITM, 
(CCur([SDLPRC])/10000);

ISSUE/QUESTION

  1. CUSTOMER A bought ITEM ABC @ 3 different prices on 3 different dates. I've taken the Max of Ship Date in hope to show the LAST PRICE PAID (resulting in one single value for price). However, for some reason, I am still receiving the three different prices on three different dates. How can I have MS Access only display the latest price based off of the latest ship date?

NOTE: SDLPRC = "Sold Price". I have to convert SLDPRC into a Currency and then divide by 1000; this is due to our current database setup. Also, SDAITM is an "Abbreviated Item Number" that is more customer-friendly.

Upvotes: 1

Views: 207

Answers (1)

Rominus
Rominus

Reputation: 1221

The problem is that you're grouping by your Price variable (CCur([SDLPRC])/10000). When you use GROUP BY, Access/SQL will split the rows by all the variables in the GROUP BY statement. So you need to not group by price.

Change your query to use a subquery that finds the last date of a sale grouped by [Company ID], [Item ID] and Style. The use an outer query to grab the price for that particular record. Something like:

SELECT b.[COMPANY ID], b.[ITEM ID], b.STYLE, b.[LAST SHIP DATE], CCur(a.[SDLPRC])/10000 as PRICE
INTO [Table - Sales Details]
FROM [Sales Order Details (F42119)] as a
INNER JOIN
    (SELECT 
        [Sales Order Details (F42119)].SDAN8 AS [COMPANY ID], 
        [Sales Order Details (F42119)].SDITM AS [ITEM ID], 
        [Sales Order Details (F42119)].SDAITM AS STYLE, 
        Max([Sales Order Details (F42119)].SDDRQJ) AS [LAST SHIP DATE] 
    FROM [Sales Order Details (F42119)]
    GROUP BY 
        [Sales Order Details (F42119)].SDAN8,
        [Sales Order Details (F42119)].SDITM, 
        [Sales Order Details (F42119)].SDAITM 
    ) as b
ON a.SDAN8 = b.[COMPANY ID]
   and a.SDITM = b.[ITEM ID]
   and a.SDAITM = b.STYLE
   and a.SDDRQJ = b.[LAST SHIP DATE]

Upvotes: 2

Related Questions