Help: Query one field with two criteria

[andrewhh82]

Hi, and thanks.

i have a table with 3 fields Sku #'s, a date, and a price for that sku. i also have a list of sku's i need to pull and 2 dates that i need to know the price for. Example: what is the price of sku# 10002 on 12/1/04 and 12/1/05. I need this returned all on the same row. I have around 150 sku's so i thought i should build a table with all the sku's and the two dates for each sku. Then somehow query this against my initial table. Does this make sense? If anyone could help, that would be great. I'm working in access 97.

 

[JerryKlmns]

SELECT *
FROM YourTableName
WHERE (((YourDateField = #2005/12/01#) OR
(YourDateField = #2004/12/01#)) AND
([sku#]=10002))

 

[andrewhh82]

This unfortunately returns 1 row for each date. I would like to have the results as a row with 3 columns with fields basicly sku, price1, price2. Is this possible?

 

[PHV]

SELECT A.sku, A.Date As Date1, A.Price As Price1, B.Price As Price2, B.Date As Date2
FROM yourTable AS A INNER JOIN yourTable AS B ON A.sku = B.sku
WHERE A.Date = #2004-12-01# AND B.Date = #2005-12-01#
AND A.sku = 10002

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

 

[andrewhh82]

Perfect. I left out that i had a store field too, but i just did another inner join on that field as well, and it came back perfect. Thanks for everyone's help