query to pull prices for different dates from a table

  • Thread starter Thread starter karen
  • Start date Start date
K

karen

I have an item table with specific dates associated with
specific prices. I need to create a report and in each
line, would like to print the item along with all the
dates and their corresponding prices.

Also, the difference between the prices of the earliest
and latest dates.

Its been a long day and I am drawing a blank. I would
appreciate responses........
 
Karen

I bet its been a long day. Do you think it might be easier for someone to
help if they knew what your data was? I bet if you post the data structure
and some sample data you will get a reply or two.

Ron W
 
I have the following rows in my table

Item Date Price
20T 2/4/04 12
20T 4/4/04 14
20T 5/4/04 10
30A 1/1/04 9
30A 2/2/04 6

I want the following result in my report....

Item LasttDate LastPrice EarliestDate EarliestPrice Diff
20T 5/4/04 10 2/4/04 12 2
30A 2/2/04 6 1/1/04 9 3
 
OK Here is what I have.

I first created a totals Query to be used as my base.

SELECT tblItems.item, Max(tblItems.date) AS MaxOfdate, Min(tblItems.date) AS
MinOfdate
FROM tblItems
GROUP BY tblItems.item;

Then for all of the money

SELECT items2.item, items2.MaxOfdate, items2.price, items3.MinOfdate,
items3.price, [items3].[price]-[items2].[price] AS Diff

FROM (SELECT items1.item, items1.MinOfdate, tblItems.price
FROM tblItems INNER JOIN items1
ON tblItems.date = items1.MinOfdate AND tblItems.item =
items1.item) as Items3

INNER JOIN (SELECT items1.item, items1.MaxOfdate, tblItems.price
FROM tblItems INNER JOIN items1
ON tblItems.item = items1.item AND tblItems.date =
items1.MaxOfdate) as Items2

ON items2.item = items3.item;


Returned
item MaxOfdate Items2.price MinOfdate Items3.price Diff
20t 5/4/2004 $10.00 2/4/2004 $12.00 $2.00
30a 2/2/2004 $6.00 1/1/2004 $9.00 $3.00


Ron W
 
Or all as one just barely comprehensible Query

SELECT items2.item, items2.MaxOfdate, items2.price, items3.MinOfdate,
items3.price, [items3].[price]-[items2].[price] AS Diff

FROM (SELECT items1.item, items1.MinOfdate, tblItems.price
FROM tblItems INNER JOIN
(SELECT tblItems.item, Max(tblItems.date) AS
MaxOfdate, Min(tblItems.date) AS MinOfdate
FROM tblItems
GROUP BY tblItems.item) as items1
ON tblItems.date = items1.MinOfdate AND tblItems.item =
items1.item) as Items3

INNER JOIN (SELECT items1.item, items1.MaxOfdate, tblItems.price
FROM tblItems INNER JOIN
(SELECT tblItems.item, Max(tblItems.date)
AS MaxOfdate, Min(tblItems.date) AS MinOfdate
FROM tblItems
GROUP BY tblItems.item) as items1
ON tblItems.item = items1.item AND tblItems.date =
items1.MaxOfdate) as Items2

ON items2.item = items3.item;

Ron W
 
Back
Top