By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

How do I compare records criteria in a single table?

P: 1
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.

1 Week Ago #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,347
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.
1 Week Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.