jonplaca jonplaca - 1 month ago 9
SQL Question

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.

Answer

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] 
Comments