Building query with subquery to produce editable field / data

[keliie]

Hello again,

I've started this query to allow a user to edit data in a form. Based
on searches in this group, I understand that forms based on
query/subquery are not editable. Therefore, I've gone the route of
building this query which should, in theory, allow form editing.

BACKGROUND:
tblMenuMaster.Menu_Item_ID 'This is not the key field
tblMenuMaster.Menu_Description_ID 'This is the key field
tblMenuMaster.Menu_Category
tblMenuMaster.Menu_Type
tblMenuMaster.Menu_Memo

tblMenuPrice.Price_ID 'This is the key field
tblMenuPrice.Company_Location
tblMenuPrice.Menu_Price_Date
tblMenuPrice.Menu_Description_ID
tblMenuPrice.Menu_Item_Price

1:M join on Menu_Description_ID
Each Menu_Description_ID can have several prices with different dates
(e.g., as the menus are updated)

QUESTION:
Any thoughts on building a query which:
-returns 1 record for each tblMenuMaster.Menu_Description_ID
-returns latest (i.e., MAX of Menu_Price_Date) price based on date

CURRENT STATE OF QUERY:
The query that I have only returns one record in the dataset (i.e., not
1 record for each Menu_Description_ID)

QUERY:
SELECT
tblMenuMaster.Menu_Item_ID,
tblMenuMaster.Menu_Description_ID,
tblMenuMaster.Menu_Category,
tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Memo,
tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
FROM
tblMenuMaster
INNER JOIN
tblMenuPrice
ON
tblMenuMaster.Menu_Description_ID = tblMenuPrice.Menu_Description_ID
WHERE ((
(tblMenuPrice.Menu_Price_Date)=
(
SELECT
(
MAX(Menu_Price_Date)
)
FROM
tblMenuPrice
)));

Thanks for any help,

Kelii

 

[keliie]

Should anyone be interested or wish to comment on a more elegant
approach, here is the query I finally ended up using to solve the issue
stated above.

QUERY:
SELECT
tblMenuMaster.Menu_Item_ID,
tblMenuMaster.Menu_Description_ID,
tblMenuMaster.Menu_Category,
tblMenuMaster.Menu_Type,
tblMenuMaster.Menu_Memo,
tblMenuPrice.Price_ID,
tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Item_Price
FROM
tblMenuMaster
INNER JOIN
tblMenuPrice ON tblMenuMaster.Menu_Description_ID =
tblMenuPrice.Menu_Description_ID
WHERE
(
(
(tblMenuPrice.Price_ID)
IN
(
SELECT
tblMenuPrice.Price_ID
FROM
[
SELECT
tblMenuPrice.Menu_Description_ID,
Max(tblMenuPrice.Menu_Price_Date) AS LastDate
FROM
tblMenuPrice
GROUP BY
tblMenuPrice.Menu_Description_ID
].
AS M INNER JOIN
tblMenuPrice
ON
(M.Menu_Description_ID = tblMenuPrice.Menu_Description_ID) AND
(M.LastDate = tblMenuPrice.Menu_Price_Date)
)
)
);