Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to design a query that looks up a pricing table and returns the record
that comes imediatly BEFORE the date specified, however I need to know more
about forming the query and formating dates. What steps should I take.

Thanks,
K
 
Presumably you have a Price table with fields like this:
PriceID AutoNumber primary key
ProductID Number which product this price is for
StartDate Date/Time the date this price applies from
PriceEach Currency the price for this product from this date.

Then you want to show the current price in a query that contains the Product
table. If you don't mind a read-only result, a subquery would be the most
efficient result:

SELECT Product.*,
(SELECT TOP 1 PriceEach FROM Price
WHERE Price.ProductID = Product.ProductID
AND Price.StartDate <= Date()
ORDER BY Price.StartDate DESC, Price.PriceID) AS PriceEach
FROM Product;

If you need an editable result, grab the ELookup() function from this link:
http://allenbrowne.com/ser-42.html
Then type this into the Field row of your query:
PriceEach: ELookup("PriceEach", "Price", "ProductID = " & [ProductID],
"[StartDate] DESC")

For other suggestions on how to handle this kind of thing, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Thanks, Thats just the kind of thing I'm looking for - going to try it now!
k

Allen Browne said:
Presumably you have a Price table with fields like this:
PriceID AutoNumber primary key
ProductID Number which product this price is for
StartDate Date/Time the date this price applies from
PriceEach Currency the price for this product from this date.

Then you want to show the current price in a query that contains the Product
table. If you don't mind a read-only result, a subquery would be the most
efficient result:

SELECT Product.*,
(SELECT TOP 1 PriceEach FROM Price
WHERE Price.ProductID = Product.ProductID
AND Price.StartDate <= Date()
ORDER BY Price.StartDate DESC, Price.PriceID) AS PriceEach
FROM Product;

If you need an editable result, grab the ELookup() function from this link:
http://allenbrowne.com/ser-42.html
Then type this into the Field row of your query:
PriceEach: ELookup("PriceEach", "Price", "ProductID = " & [ProductID],
"[StartDate] DESC")

For other suggestions on how to handle this kind of thing, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

tpkt said:
I want to design a query that looks up a pricing table and returns the
record
that comes imediatly BEFORE the date specified, however I need to know
more
about forming the query and formating dates. What steps should I take.

Thanks,
K
Click to expand...
Click to expand...
 
Back
Top