Find most recent date

[Guest]

I'm somewhat of a newbie....so bear with me.

What I've got is a table that has contains various companies, products,
pricing, and what I'd call a time stamp date.....basically as a price for a
product changes....it is input in the table and the current date is input.
It consists of the following:

TrackingID Autonumber
Company Number
Product Number
Date Date/Time
Price Currency

How could I create a query from this that would prompt the user for a date.
Once the date is input, what I'd like to have it retrieve is esentially a
price list....by Company....by Product.....and list prices as of the date
given. So though a product may have 30 different prices over a period of
time.....I want it to pull the price for that product as of the date input by
the user. What is my best option for accomplishing this?

 

[Marshall Barton]

I'm somewhat of a newbie....so bear with me.

What I've got is a table that has contains various companies, products,
pricing, and what I'd call a time stamp date.....basically as a price for a
product changes....it is input in the table and the current date is input.
It consists of the following:

TrackingID Autonumber
Company Number
Product Number
Date Date/Time
Price Currency

How could I create a query from this that would prompt the user for a date.
Once the date is input, what I'd like to have it retrieve is esentially a
price list....by Company....by Product.....and list prices as of the date
given. So though a product may have 30 different prices over a period of
time.....I want it to pull the price for that product as of the date input by
the user.

SELECT T.*
FROM table As T
WHERE T.[Date] = (SELECT Max(X.[Date])
FROM table As X
WHERE T.Company = X.Company
And T.Product = X.Product
And X.[Date] <= [Enter Date])