Access Query: Help Required! 1

[Chunkus]

Hi

I urgently need help on writing a query.

I have two tables. One contains purchases by product and date, the other table contains standard prices by product and effective date.

I need to return the relevant standard price for each purchase. See Example Below

Table A: Purchases
ProductCode Date
10A 1st Jan 05
10A 8th Feb 05
10A 12th Feb 05
etc

Table B: Agreed Product Price
ProductCode EffectiveDate Price
10A 1st Dec 04 £10.00
10A 5th Jan 05 £10.50
10A 11th Feb 05 £11.25
etc

Query
ProductCode Date Standard Price
10A 1st Jan £10.00 (Price @ 1/12/4)
10A 8th Feb £10.50 (Price @ 5/1/5)
10A 12th Feb £11.25 (Price @ 11/2/5)

How do you do it?
This is doing my head in.
Somebody PLEASE help me!!!!!!

Thanks

JASON

 

[fredericofonseca]

search these forums for select and inner join and you will find plenty of samples.

Then if you have more problems come back with your code and we will try to help further.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DotNetGnat]

Try something like this:

SELECT A.ProductCode, A.[Date], B.StandardPrice FROM
Purchases A INNER JOIN AgreedProductPrice B ON
A.ProductCode=B.ProductCode

-DNG

 

[lespaul]

try this:

SELECT A.ProductCode, B.[Date], A.Price As StandardPrice FROM AgreedProductPrice A
INNER JOIN Purchases B ON
A.ProductCode=B.ProductCode AND A.Date < B.Date

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

SELECT A.ProductCode, A.Date, B.Price AS [Standard Price]
FROM Purchases AS A INNER JOIN [Agreed Product Price] AS B ON A.ProductCode = B.ProductCode
WHERE B.EffectiveDate = (SELECT Max(EffectiveDate) FROM [Agreed Product Price]
WHERE ProductCode=A.ProductCode AND EffectiveDate<=A.Date)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Chunkus]

THANKS PHV - you are a STAR, which is why I have 'Starred' your post.

Your code was VERY useful - it worked a treat.

I have never used a SELECT statement within a SELECT statement before. This will be VERY useful for me. I am used to using the graphical interface within Access, as opposed to typing SQL statements direct.

In the past I would have solved this problem using multiple queries like this:

Qry1: (All Dates <= Purchase Date)
SELECT A.ProductCode, A.Date, B.Price AS [Standard Price], B.EffectiveDate
FROM B INNER JOIN A ON B.ProductCode = A.ProductCode
WHERE (((![EffectiveDate])<=[A]![Date]));

Qry2: (Max of dates <= Purchase Date)
SELECT Max(Query1.EffectiveDate) AS MaxOfEffectiveDate, Query1.ProductCode, Query1.Date
FROM Query1
GROUP BY Query1.ProductCode, Query1.Date;

Qry3: (Link to Std Prices)
SELECT Query2.ProductCode, Query2.Date, B.Price AS [Standard Price]
FROM B INNER JOIN Query2 ON (B.EffectiveDate = Query2.MaxOfEffectiveDate) AND (B.ProductCode = Query2.ProductCode);

Do you know anywhere on the Web where I can learn more about SELECT statements within SELECT statements.

Thanks again, I can rest easy now.

Jason