query question - displaying unique records with highest value

[devagupt]

I have a query which contains in it the vial # , product , speed , # of people and the vials/manmin. there are multiple vial # for each product and within a vial # there are duplicate entries.So the same vial # could have run on another shift and the speed could be different therby causing the vials/manmin to be different. I would like to give the user the option to enter a vial # and display the record with the highest vial/manmin for that particular vial #. How would i do this?

Please advise.
Thanks.


*CODE**

SELECT [Packaging table].[vial #], [Packaging table].shift, [Packaging table].line, [Packaging table].Product, [Packaging table].People, [Actual Units]/[hours run]/60/[people] AS [v/manmin], [Packaging table].[Actual Units], [Packaging table].[hours run], [Packaging table].[STD people]
FROM [Packaging table]
ORDER BY [Packaging table].[vial #], [Actual Units]/[hours run]/60/[people];

 

[PaulBricker]

First, you could create a query like this.

SELECT [Packaging table].[vial #],  Max( [Actual Units]/[hours run]/60/[people]) AS [v/manmin]
FROM [Packaging table]
GROUP BY [Packaging table].[vial #]
HAVING ((([Packaging table].[vial #])=[enter vial number]));

Then create your query and add the above query to the query grid. Join the queries on vial # and v/manmin fields.
The one problem I see is getting the v/manmin fields to match. Calculated fields can be tricky. For example, using
Max(CalculatedField) as SomeName
can create a result that is hard to join to a straight calculated field. You may have to do something like the Format Function to get the calculated fields into the same datatype so they can be joined.


Try it and post back any problems.

Paul

 

[devagupt]

Paul,

Thanks , It works. There isnt any problem with the Vials/manmin calculated field so far.

 

[PaulBricker]

When I tested it, I used expressions like this.

Max(Format(CalculatedExpression, "0.0")) As SomeName

and

Format(CalculatedExpression, "0.00") As SomeName

and then joined them without problems. You may not have any problems and that's great, but if you do you can look at using something like this to get the data into the format you need.

Paul