query =valid price

F

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
 
J

James A. Fortune

Florin said:
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
Click to expand...

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) &
Code: 
 &
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]
 
Top