Help with SQL Query for Microsoft Access

[kbhat]

Suppose I have an inventory database in Microsoft Access in which I
try to keep information about products, their description, price and a
history of price changes.

Suppose TBL_1 has the following fields:
Product_Code [key], Product_Description

and

TBL_2 has the following fields:
Product_Code [key], Price, Date_of_price_assignment

Date_of_price_assignment is the date on which I assign a price to the
product.

I would like to design a query in which I specify a date and the
database retrieves the price of the product for the corresponding
date. For example, let us assume that Product_Code 65 had a Price of
$36 assigned to it on January 12th and a Price of $27 assigned to it
on July 3rd. If we query the product information for February 23rd, a
value of $36 is returned for the price. If we query the product
information for August 1st, a value of $27 is returned for the price.

Any help will be appreciated.

Thanks,
Bhta

 

[Allen Browne]

Your table is correctly designed, and you can retrieve the price for a date
using a subquery.

Tom Ellison explains the details here:
http://allenbrowne.com/ser-58.html

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[kbhat]

Your table is correctly designed, and you can retrieve the price for a date
using a subquery.

Tom Ellison explains the details here:
http://allenbrowne.com/ser-58.html

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--

Thanks. Example 58 really helped.