Most Recent Date

[Guest]

In response to my earlier question, Marsh's code worked like a charm. I'd
like to tie in one more question to this if I can. The code below works, but
I don't seem to be able to tie this query now to a report that lists the
actual company name. Can I modify the code below somehow to give me the
actual company name instead of the "autonumber" tied to it? For the purposes
of this question, I've got a separate company table containing:

CompanyID Autonumber
CompanyName text

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

 

[Gary Walter]

Hi Bryan,

Just add company table to query
joining CompanyID to Company,
then add CompanyName to grid
(to SELECT clause).

good luck,

gary

In response to my earlier question, Marsh's code worked like a charm. I'd
like to tie in one more question to this if I can. The code below works,
but
I don't seem to be able to tie this query now to a report that lists the
actual company name. Can I modify the code below somehow to give me the
actual company name instead of the "autonumber" tied to it? For the
purposes
of this question, I've got a separate company table containing:

CompanyID Autonumber
CompanyName text

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