Recent Date

[wdverner]

Hi All,
I created the following query:

SELECT ListContent.*
FROM tblListContent INNER JOIN [SELECT ListID, PartID, Max(EffectiveDate) AS MDate FROM tblListContent GROUP BY List, PartID]. AS q1 ON (tblListContent.ListID = q1.ListID) AND (tblListContent.PartID = q1.PartID) AND (tblListContent.EffectiveDate = q1.MDate);

As you see it gives back the most recent Price of each part.

80000 9.99 01/01/2006
80000 7.99 01/02/2006

The query will return price 7.99 as it is the most recent price.

However when I attach this query to a form, sometimes it works other times it doesnt:

"The Microsoft JET database engine cannot find the input table or query 'SELECT ListID, PartID, Max(EffectiveDate) AS MDate FROM tblListContent GROUP BY ListID, PartID'. Make sure it exists and its name is spent correctly.

The only way I can get the query to work again is to go into design view and move the tables to refresh it. I can then run the query again.

This is obviously a major issue...

Is there another way where I can get a list of Current Prices for all parts without the above error?

Many thanks for your ideas, input and suggestions.

 

[PHV]

Create a query named, say qryRecentDate:
SELECT ListID, PartID, Max(EffectiveDate) AS MDate
FROM tblListContent
GROUP BY List, PartID;

Now your query:
SELECT ListContent.*
FROM tblListContent INNER JOIN qryRecentDate AS q1 ON (tblListContent.ListID = q1.ListID) AND (tblListContent.PartID = q1.PartID) AND (tblListContent.EffectiveDate = q1.MDate);

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