Update Current And Future Value/No Change To Previous Values

[pjd218]

I have fairly simple database.

2 tables:
PriceTBL
SalesTBL

PriceTBL is used to store pricing and volume data for 5 products. PriceID used as key. SalesID default value set from command button call from SalesFRM.

SalesTBL is used to enter total daily sales for each product type. Other fields include date and location. SalesID used as key.

SalesTBL one to many with PriceTBL.

SalesFRM enters daily sales totals. A command button on the form opens SetPriceFRM and sets the SalesID field in the PriceTBL

What I am trying to accomplish is the ability to change pricing on a product on a given date and have that price used as the default price for the product until another price change without affecting the records with a sales date prior to the price change.

The pricing is used in a number of calculations both on the SalesFRm and in queries and reports.

Tried just about everything but can't quite get there.

 

[Darrylles]

Hi pjd,

How about a tblLatestPrice table, which holds one PriceID per product (the latest priced product)?

Also, good to see you're using some kind of naming convention for objects, but the following convention is more common:

tblTablename
frmFormname
rptReportname
qryQueryname
txtTextboxName
lblLabelname etc.

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[pjd218]

Darrylle,

Thanks for the suggestion.

I actually got things to work out using a DLookup and Dmax combined function.

Off to the races!