Matching sales records to updated prices, by date

[Kelii]

Hi all,

This is a strange one that doesn't seem to want to work for me.

I have a table with sales data, showing sale date, item sold, and
quantity sold. I have another table with price information (i.e., item,
price effective date, and price); each item can have multiple records
in the price table depending on whether the price has been updated
(e.g., if widget a goes from a price of $1.00 to $2.00 on 1/2/2006).

I want to be able to match the correct price to each of the dates where
an item is sold. So for example:

Sales data:
Date #
Sold Item Sold
1/1/2006 WidgetA 1
1/2/2006 WidgetA 2
1/3/2006 WidgetA 3

Price data:
Price
Item Effective Price
WidgetA 1/1/2006 $1.00
WidgetA 1/2/2006 $2.00

Desire query results
Date #
Sold Item Sold Price
1/1/2006 WidgetA 1 $1.00
1/2/2006 WidgetA 2 $2.00
1/3/2006 WidgetA 3 $2.00

I've tried using simple subqueries to match the right dates, using date
criteria and the max function but nothing seems to work properly. See
below for the current SQL which results in multiple records as soon as
a new price become effective (i.e., two records, rather than 1 for all
sales occuring on and subsequent to the price change date, including
one record with the new price and one record with the old price).

Current SQL (note that I've left out much of the minutae from my
description above):
SELECT tblSalesMaster.Sale_Date, tblMenuPrice.Menu_Item_Price,
Max(tblMenuPrice.Menu_Price_Date) AS MaxOfMenu_Price_Date
FROM (tblMenuMaster INNER JOIN tblMenuPrice ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID)
INNER JOIN (tblSalesMaster INNER JOIN tblSalesDetails ON
tblSalesMaster.Sales_ID = tblSalesDetails.Sales_ID) ON
tblMenuMaster.Menu_Description_ID = tblSalesDetails.Menu_Description_ID
WHERE (((tblSalesDetails.Menu_Description_ID)="369 TOURNADOS"))
GROUP BY tblSalesMaster.Sale_Date, tblMenuPrice.Menu_Item_Price
HAVING (((Max(tblMenuPrice.Menu_Price_Date))<=[Sale_Date]));

I'm not asking anyone to disect my query, but thoughts or tips to get
me nudged forward would be extremely helpful.

Best, (and thanks in advance)

Kelii

 

[Allen Browne]

See:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Tom Ellison explains how to create a query that gives the From and To dates
(even though PriceEffective is the only stored date), which makes it easy to
price you need from that query.

 

[Kelii]

Got it to work.

Thanks a ton for the link. I'll be using similar queries in building
FIFO inventory valuations, so this will be invaluable to me going
forward.

Best regards,

Kelii

 

[Kelii]

Allen - as a follow up question to the example provided on your
website,

If you were to assume that there were say 100 rates for each item, also
assume that there are 1000 items, and finally that you would like to
perform a similar calculation for each item and provide summary totals
(i.e., find the relevant prices ranges for all items at every rate and
summarize as output).

My question is, would you take a similar or different approach, or
would the calculation task become to overwhelming / time intensive for
an Access database. If you would consider a different approach, what
would it be.

I hope this follow up question makes sense, if not I can happily
clarify further.

Best,

Kelii

 

[Allen Browne]

What I normally do is store the price in the transaction details table
(orders, sales, whatever it is.)

So, at the time the user enters the transaction, the code looks up the
current price at the date of the transaction, and offers this as the price
when they select the product. The user can then alter the price if needed.

In my mind, that has several advantages:
a) It allows for discounts or penalty rates, i.e. it permits transactions
that are at a price other than the current default.

b) It simplifies (and expedites) the process of calcualting values.

c) It improves the accountancy side of the application, making it easier to
lock down the financial periods, and reducing the risk of someone making one
change to the prices table that has huge implications on all existing
transactions.

In my view, this design is still normalized. Since (a) indicates that any
discrepency between the prices table and the transactions is meaningful, it
is not storing redundant data.

In summary, the price table that stores the history of prices for each
procuct is valid and useful, but you use it at the point of data entry
rather than as the canonical indicator of the price that must be used in
every transaction.

 

[Kelii]

Allen,

Thanks for the thought provoking commentary, I appreciate it. I'll have
to wrap my mind around whether I should redesign the pricing / sales
side of my tables, per your comments.

Thanks again,

Kelii