Work around aggragated query to make Updatable

[Basshopper]

The user currently manual checks the Use Part? box but would I would like to automate with an update query. I have this query that needs to be updatable but has a group by and min to get the correct results. I plan to run this query by another update query to update the table, but its currently not updatable. Is there a workaround or does anyone have another scheme to go at it. Here is my current queries.

SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], Min([t_BOM Part Cross].[Alt Unit 1]) AS [MinOfAlt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?]
HAVING ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND ((Min([t_BOM Part Cross].[Alt Unit 1]))>0))
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #];

I have another query that I used a SUB SELECT ON THE [Alt Unit 1] but does not group the other fields to get the results but it is updatable.

SELECT [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
WHERE ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND (([t_BOM Part Cross].[Alt Unit 1])>(SELECT MIN([Alt Unit 1])>0 
FROM [t_BOM Part Cross])))
ORDER BY [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #];

thanks for any help available

 

[namliam]

Simply make the subselect a group by query and use in

SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], Min([t_BOM Part Cross].[Alt Unit 1]) AS [MinOfAlt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
Where ([t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #]) 
in (SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], 
    FROM [t_BOM Part Cross]
    GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?]
    HAVING ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND ((Min([t_BOM Part Cross].[Alt Unit 1]))>0))
)
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #];

It is not pretty and probably not very fast, but ... Updatable...
You could also try something with a function, but that is for another day I think... Try this first...

Greets