Find field with highest and lowest value in a table

[brother]

Hi guys,

I have a table TblResults with the following fields:

[ResID]
[Date]
[Location]
[Checkpoint1]
[Checkpoint2]
[Checkpoint3]
[Checkpoint4]

Each [Checpoint(x)] contains values/grades ranging from 1-5.

What I want is a query that return the checkpoint with the highest value and the checkpoint with the lowest value. Furthermore, both Date and Location should also show for each of the two checkpoints.


How do I get my query to return these results? Maybe I need one query for Max and one query for Min?


Thanks!

 

[Uncle Gizmo]

have a look at these posts, part 1 through 3 the answer you require is there.

 

[gemma-the-husky]

the problem with this issue is how you distinguish between multiple lowest/highest values. I think thats why its not easy to derive the other attributes for one of these directly from a dmin/dmax query.

one other way is to sort a query based on the target field - then with a recordset you can movefirst and movelast, to get the appropriate rows.

 

[brother]

Sorry, but I dont really know how to apply movefirst and movelast into this.
Is it possible to make one query for dmin and one for dmax that also includes date and location? An example in code would be great!!!

 

[gemma-the-husky]

i think these ideas will work - not sure if there is an easier way still


a series of steps

1. find the minimum value with a dmin
2. use this to find the recordid with a dlookup
3. now you have the recordid, find any other fields with further dlookups

or
1. find the minimum value with a dmin
2. use this query in another query, joined to the table to find the other fields. this will need to be a select top1 query, otherwise you might get multiple results if you have two records with the same minimum value

 

[khawar]

I have uploaded a sample
Check data in table first

Result is achieved by using three queries
Chech them step by step

Qry1
Qry2
Final Query (This query shows your required result)

Regards

Khawar

 

[brother]

Very good Khawar!! Thanks so much! I have talked this over with some guys at work, and we have decided to simplify it a little bit.

Instead of having both the Min and Max value show, we just want Max.
I've been trying to modify your work a little bit, but I cant seem to get it right.

I want the query to show which checkpoint has the highest value for each location. This means that the query should show:
[ResID]
[Date]
[Location]
[CheckpointX] (the checkpoint value)
[CheckpointWithHighestValue] (the name of the checkpoint)

PS. The query should show Date, but not group by it.

thanks so much for all the help so far!

 

[khawar]

Download the attached file

 

[brother]

Khawar,
I might not have done a good job explaining what I want
When I try registering two recordsets with the same location, the query lists them both even though one has a lower value than the other. I only need the highest value based on location.

Thanks for you patience

 

[khawar]

cant understand

 

[brother]

I think I've got it now, check out FinalQueryMax.

Could not have done this without you Khawar, thanks alot!!!

 

[RCBNewBee]

I also needed these two value and the above file help me allot. but I need these value put in the table so i can bring them up for other calculation. If use a qry to tie these value in the table can not be updated it is lock. any ideal to transfer the data with no lock