query =valid price

[Florin]

Hi,

Explain to me, please, how to create an interogation that
shows valid prices on a specific date (let's say 11.03.2007), given the
following table:

Code price date
D210 0.17 01.01.2007
D210 0.21 15.02.2007
D210 0.22 27.02.2007
D210 0.24 15.03.2007
D210 0.32 27.04.2007
I353 1.32 01.01.2007
I353 1.35 16.01.2007
I353 1.37 26.04.2007

The result should appear as follows:
code price date
D210 0.22 27.02.2007
I353 1.35 16.01.2007

Thanks

 

[kingston via AccessMonster.com]

1) Create a parameter query that outputs only data with dates >= [input date].
In that query add a calculated field with the function DateDiff() to
determine the time between the input date and the price date. Order this
data set by the the DateDiff field ascending.

2) Create an aggregate (totals) query based on the first query that groups on
Code and returns the first Date.

3) Create another query, joining the results from part 2 to your original
price list (join on both Code and Date).

You will only need to run the third query, and be careful with your field
names.

 

[Florin]

Thanks

It's ok!!!

 

[James A. Fortune]

Hi,

Explain to me, please, how to create an interogation that
shows valid prices on a specific date (let's say 11.03.2007), given the
following table:

Code price date
D210 0.17 01.01.2007
D210 0.21 15.02.2007
D210 0.22 27.02.2007
D210 0.24 15.03.2007
D210 0.32 27.04.2007
I353 1.32 01.01.2007
I353 1.35 16.01.2007
I353 1.37 26.04.2007

The result should appear as follows:
code price date
D210 0.22 27.02.2007
I353 1.35 16.01.2007

Thanks

This can be done with one query if a subquery or its equivalent is used.

If you have only one Price for a given Code and PDate (I changed the
name because Date is a reserved word) then:

SELECT Code, Price, PDate FROM tblCodePrices WHERE PDate = (SELECT
MAX(A.PDate) FROM tblCodePrices AS A WHERE A.PDate <= DateSerial(2007,
3, 11) AND A.Code = tblCodePrices.Code);

or using DMax,

SELECT Code, Price, PDate FROM tblCodePrices Where
PDate=DMax("PDate","tblCodePrices","Code = " & Chr(34) &

 &
Chr(34) & " AND PDate <= DateSerial(2007, 3, 11)");

or using DLookup,

SELECT Code, Price, PDate FROM tblCodePrices Where
PDate=DLookup("Max(PDate)","tblCodePrices","Code = " & Chr(34) & [Code]
& Chr(34) & " AND PDate <= DateSerial(2007, 3, 11)");

Replace tblCodePrices with the name of your table and change the name of
the Date field to PDate.

James A. Fortune
[email protected]