Showing only 1 result for a given variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all!


I have a database that show:

Zone, Person, Days (and a few other things which are not important atm)

Basically there are multiple people in the same zone, however they have
different amount of time. I only want to see one person per zone and with
the lowest time.

I have tried using the min and first functions on Zone and Time however they
dont seem to tie together very well.

Any help is much appreciated

Ernest
 
oh also, i guess

within the zone, there could be multiple people with the same time (that is
also the lowest time) if this is the case, it doesnt matter which line it
shows as long as its the lowest time
 
Assumption:
Days is the field that you use to determine "time"

Multiple query solution:
First Query saved as qLeastTime
SELECT Zone, Min(Days) as LeastTime
FROM YourTable
GROUP BY Zone

The next query uses that and your table

SELECT YourTable.Zone, YourTable.Person, YourTable.Days
FROM YourTable INNER JOIN qLeastTime
ON YourTable.Zone = qLeastTime.Zone
AND YourTable.Days = qLeastTime.LeastTime

Now if you want to restrict that to one record per zone

SELECT YourTable.Zone
, First(YourTable.Person) as Person
, YourTable.Days
FROM YourTable INNER JOIN qLeastTime
ON YourTable.Zone = qLeastTime.Zone
AND YourTable.Days = qLeastTime.LeastTime
GROUP BY YourTable.Zone, YourTable.Days

If you can't construct the query in the SQL view and can only use the design
view (the query grid), then post back and perhaps someone can give you
detailed step-by-step instructions.
 
days is just a number and i want the lowest value.

(i have previously done a date calculation which gave me the difference in
days)
 
days is just a number and i want the lowest value.

(i have previously done a date calculation which gave me the difference in
days)

i am "ok" with sql so ill give it a go.
 
Back
Top