Most recent cost query

[nigelbloomy]

I have a table with a history of the costs and a dates for each item. I want the most recent cost for each item.



When I do a query and select the Max of date in the "Total" row, I get the most recent date for each cost. This means I have a new line each time the cost changed over the whole history.

I think it may be possible to use a subquery in the "Criteria" row, but I haven't been able to figure out how.

Here is the SQL I have now:
SELECT [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO, [Material Cost Table 1].COST, Max([Material Cost Table 1].DATE) AS MaxOfDATE
FROM [Material Cost Table 1]
GROUP BY [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO, [Material Cost Table 1].COST;

Thank you,

[pbaldy]

Like this?

http://www.baldyweb.com/LastValue.htm

[nigelbloomy]

That looks like it is using two queries. Is there a way to make the first query a subquery? I tried to put the SQL from the first query into the "Criteria" row for the Date of the second query, but Access gave me an error saying my subquery can return more than 1 field and I don't have an Exists word in the From clause of the main query.

First step SQL:
(SELECT [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO, Max([Material Cost Table 1].DATE) AS MaxOfDATE
FROM [Material Cost Table 1]
GROUP BY [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO)

[pbaldy]

Mine can be done as a subquery, though I don't like to do it that way:

SELECT VehicleMiles.*
FROM VehicleMiles INNER JOIN [SELECT Max([VehicleMiles].[DorDate]) AS MaxDate, [VehicleMiles].[CarNum]
FROM VehicleMiles
GROUP BY [VehicleMiles].[CarNum]]. AS qryMaxDates ON (VehicleMiles.CarNum = qryMaxDates.CarNum) AND (VehicleMiles.DorDate = qryMaxDates.MaxDate);

[nigelbloomy]

This is what I have and it is giving me a syntax error on the FROM clause.

SELECT [Material Cost Table 1].[SOURCE], [Material Cost Table 1].[ITEM_NO], [Material Cost Table 1].[COST], [Material Cost Table 1].[DATE]
FROM [Material Cost Table 1] INNER JOIN [Select Max([Material Cost Table 1].[DATE]) AS MaxDate, [Material Cost Table 1].[COST]
FROM [Material Cost Table 1]
GROUP BY [Material Cost Table 1].[COST]] AS qryMaxDate ON (Material Cost Table 1.COST = qryMaxDate.COST) AND (Material Cost Table 1.DATE = qryMaxDate.DATE);

[live2ride]

you have no join

select (fields)
from table1
inner join (select max(date), cost from table1) as table2
on table1.id = table2.id
group by (fields)

[nigelbloomy]

I put the whole SQL from the first query into the inner join section. Now my problem is that it is asking me for values for qryMaxDate.COST and .DATE. The inner join is in red. I think I am following the format Live2ride is suggesting.

SELECT [Material Cost Table 1].[SOURCE], [Material Cost Table 1].[ITEM_NO], [Material Cost Table 1].[COST], [Material Cost Table 1].[DATE]
FROM [Material Cost Table 1]
INNER JOIN (SELECT [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO, Max([Material Cost Table 1].TRANS_DATE) AS MaxOfDATE
FROM [Material Cost Table 1]
GROUP BY [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO) AS qryMaxDate
ON ([Material Cost Table 1].COST = qryMaxDate.COST) AND ([Material Cost Table 1].DATE = qryMaxDate.DATE);

[nigelbloomy]

I apologize. I am new to posting and have just learned how annoying it is when questions are cross posted and there is no link to the other post. This question has also been posted on MrExcel at http://www.mrexcel.com/forum/showthr...ent-cost-query.

[live2ride]

you do not have fields cost and date in select statement for qryMaxDate. your qryMaxDate has fields (Source,Item_No,MaxOfDate)
you are joining on fields which do not exist.

[nigelbloomy]

Thank you. I didn't realize that. It works perfectly now.

SELECT [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO, [Material Cost Table 1].COST, [Material Cost Table 1].DATE
FROM [Material Cost Table 1]
INNER JOIN (SELECT [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO, Max([Material Cost Table 1].DATE) AS MaxOfDATE
FROM [Material Cost Table 1]
GROUP BY [Material Cost Table 1].SOURCE, [Material Cost Table 1].ITEM_NO) AS qryMaxDate
ON ([Material Cost Table 1].SOURCE = qryMaxDate.SOURCE)
AND ([Material Cost Table 1].ITEM_NO = qryMaxDate.ITEM_NO)
AND ([Material Cost Table 1].DATE = qryMaxDate.MaxOfDATE);