How do I compare records criteria in a single table?

[marshdom]

I have a table containing an airline's complete flight listings for a month. Using Group By I have made another table containing all the flight numbers with unique properties other than date departing (eg flight number 2322 appears in the table twice, departing at 09:00 and at 09:10 on another occasion). Most flights leave at the exact same time so only appear in the table once.

I am trying to list any flight with really inconsistent flight times (flight number 165 appears 3 times, departing 11:15, 11:25 and also at 19:40). I have a field named "dur_loc" which shows the number of days "local duration" in days that the flight covers. A 'there and back' flight usually has "dur_loc" of 1 (day). If the flight goes through 'local midnight' it has a "dur_loc" of 2. Layover flights might have a "dur_loc" of 3 or more days.

Right now I have to manually search through the record list to find flight records with the same flight number and then check the "dur_loc" is the same. I need to pick out records where the "dur_loc" field differs for the same flight number.

Any thoughts on a methodology for this? Many thanks.

[NeoPa]

You can produce the Max() & the Min() of any value within a group in your query. If the difference between the two is greater than some specified threshold then you have identified those groups that interest you.

With [Dur_loc] you can either separate them into different groups by including the field in the GROUP BY clause or, if you want to highlight them within a single group then, again, use Max() & Min() and compare them.