Advanced Excel Query

Richard Hughes

New Member
Joined
Jul 14, 2004
Messages
5
Hi All



I need to check for the following conditions in an advanced query and the problem is a little hard to explain.



I have a field called PAIRING NUMBER and a field called REPORT TIME. I need to check and see if there are two report times on the same pairing that fall within a specific time frame.



The rule states pairings shall not be constructed with more than one (1) duty period beginning in the same calendar day, including the time from 0001 HDT to 0230 HDT of the following day.



The example looks like this:



Report Time 0720 HDT (Home Domicile Time, don't worry about the HDT as I've all ready made the conversion), fly several legs of a trip, then have a rest period of 12 hours and 50 minutes. My next report time is 0150 HDT on the next day.



How do I get the query to check all the report times for a given pairing number for report times that report in the same calendar day for the times that include 0000 to 0230 the next morning.





Trip 2179 dated 07Jan05

Time Zone: L.

1 - FO 1 - CA

Date Flight Depart Arrive Eq Blk Grnd Blk Duty Cred

07Jan 282 PHX 0810 SJC 0910 300 200 30 (1

07Jan 738 SJC 0940 LAS 1100 300 120 1350 (2

Rpt 0710 Rls 1115 L/O LAS 1250 320 505 320

This trip reports in PHX at 0710 HDT.



Note: 2 reports same day here.

08Jan 690 LAS 0050 MSP 0603 300 313 3932 (3

Rpt 0005 Rls 0618 L/O MSP 3832 313 413 313

This leg of the reports in LAS that is 0105 HDT.



09Jan 249 MSP 2135 LAS 2302 300 327 148 (4

10Jan 690 LAS 0050 MSP 0603 300 313 1302 (5

Rpt 2050 Rls 0618 L/O MSP 1202 640 928 640

10Jan DH 813 MSP 1905 PHX 2137 320 0 0 (6

Rpt 1820 Rls 2152 0 432 332

TAFB 8642 Rest 1000 Totals 1313 2318 2615A


25,E,5 Says....



"Pairings shall not be constructed with more than one (1) duty period

beginning within the same Calendar Day, including the time from 0001 HDT to

0230 HDT of the following Calendar Day."



Any ideas on how to get the query to work? Is an advanced Query the way to go here or is there another way?



Thanks for your help with this.



Richard Hughes

A1pilot@flica.net
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,147,674
Messages
5,742,541
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top