Query Needs to show Min Value without Dups with checkbox 2

[Basshopper]

Hi I have a query I want to run that will autocheck the box [Use Part?] by picking the lowest [Alt Unit 1] value for each group of [Line #] in other words there could be 2 [Alt Unit 1] prices for the same [Line #] When I group and use the Min function I can no longer update the [Use Part?]
Thanks for your help

SELECT [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?], [t_BOM Part Cross].[Alt Unit 1]
FROM [t_BOM Part Cross]
WHERE ((([t_BOM Part Cross].[quote=#])=[FORMS]![Parameter]![quote=#]));

 

[PHV]

WHERE [t_BOM Part Cross].[Alt Unit 1] >
(SELECT MIN([t_BOM Part Cross].[Alt Unit 1])
FROM [t_BOM Part Cross])

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

 

[Basshopper]

That did the job on making it updatable, But it is not giving my the correct results yet, How can I group by the Quote # and Line # and still have it updatable??? So close,

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 #];

 

[Basshopper]

Got it working, thanks for all that helped