Subquery perhaps?

[Guest]

Hi there, I have a table with the following fields:

TripID
LegNumber
LegTown
ArrivalTime
Departuretime

I need to create a query that displays results with the columns:
TripID, Leg1Town, Leg1Arrival, Leg1Departure, Leg2Town, Leg2Arrival,
Leg2Departure,....... etc

I intend to base a form's recordsource on the SQL statement that is
generated. Currently, the forms recordsource is based on a query that gets it
information from other queries. One for each Leg Number. (Each trip has up
to 8 legs). I would prefer to get rid of the queries altogether and have the
forms recordsource as an SQL statement that doesn't make reference to any
queries. Is this possible? If so I would appreciate any help that can be
offered. Thanks!

 

[John Vinson]

Hi there, I have a table with the following fields:

TripID
LegNumber
LegTown
ArrivalTime
Departuretime

I need to create a query that displays results with the columns:
TripID, Leg1Town, Leg1Arrival, Leg1Departure, Leg2Town, Leg2Arrival,
Leg2Departure,....... etc

I intend to base a form's recordsource on the SQL statement that is
generated. Currently, the forms recordsource is based on a query that gets it
information from other queries. One for each Leg Number. (Each trip has up
to 8 legs). I would prefer to get rid of the queries altogether and have the
forms recordsource as an SQL statement that doesn't make reference to any
queries. Is this possible? If so I would appreciate any help that can be
offered. Thanks!

A rather monstrous Self Join query should work. Add your table to the
query grid *eight times*, joining each instance to the previous
instance by TripID. Make all the joins Left Outer Joins (option 2).

Put criteria on LegNumber of 1 on the first leg; on each successive
instance use

2 OR IS NULL
3 OR IS NULL
....
8 OR IS NULL

Alias the town, arrival, and departure fields appropriately.

John W. Vinson[MVP]

 

[Guest]

A rather monstrous Self Join query should work. Add your table to the
query grid *eight times*, joining each instance to the previous
instance by TripID. Make all the joins Left Outer Joins (option 2).

Put criteria on LegNumber of 1 on the first leg; on each successive
instance use

2 OR IS NULL
3 OR IS NULL
....
8 OR IS NULL

Alias the town, arrival, and departure fields appropriately.

John W. Vinson[MVP]

Hi John, Thanks for your help! Unfortunately the number of records returned
when the query runs decreases when I add more legs. ie If I design the query
to show the details of four legs of each trip, trips with only one, only two,
or only three legs don't show in the results. This is despite using left
outer joins and setting the criteria to the leg number or Is Null for
LegNumber. Consequently, if I were to add the table eight times only trips
with eight legs would show up in the results. Any other ideas?

 

[John Vinson]

Hi John, Thanks for your help! Unfortunately the number of records returned
when the query runs decreases when I add more legs. ie If I design the query
to show the details of four legs of each trip, trips with only one, only two,
or only three legs don't show in the results. This is despite using left
outer joins and setting the criteria to the leg number or Is Null for
LegNumber. Consequently, if I were to add the table eight times only trips
with eight legs would show up in the results. Any other ideas?

Sounds like you may have some other criteria sneaking in. Please post
the SQL view of the query.

John W. Vinson[MVP]

 

[Guest]

Sounds like you may have some other criteria sneaking in. Please post
the SQL view of the query.

John W. Vinson[MVP]

OK, But no doubt you'll note that the field names in the SQL aren't the ones
in the original question but the table structure is the same:

SELECT AAFP_tbl_FlightPlanLeg.intLegNumber, AAFP_tbl_FlightPlanLeg.dteETD AS
Leg1ETD, AAFP_tbl_FlightPlanLeg_1.intLegNumber,
AAFP_tbl_FlightPlanLeg_1.dteETD AS Leg2ETD,
AAFP_tbl_FlightPlanLeg_2.intLegNumber, AAFP_tbl_FlightPlanLeg_2.dteETD AS
Leg3ETD, AAFP_tbl_FlightPlanLeg_3.intLegNumber,
AAFP_tbl_FlightPlanLeg_3.dteETD AS Leg4ETD
FROM ((AAFP_tbl_FlightPlanLeg LEFT JOIN AAFP_tbl_FlightPlanLeg AS
AAFP_tbl_FlightPlanLeg_1 ON AAFP_tbl_FlightPlanLeg.lngFlightPlanID =
AAFP_tbl_FlightPlanLeg_1.lngFlightPlanID) LEFT JOIN AAFP_tbl_FlightPlanLeg AS
AAFP_tbl_FlightPlanLeg_2 ON AAFP_tbl_FlightPlanLeg_1.lngFlightPlanID =
AAFP_tbl_FlightPlanLeg_2.lngFlightPlanID) LEFT JOIN AAFP_tbl_FlightPlanLeg AS
AAFP_tbl_FlightPlanLeg_3 ON AAFP_tbl_FlightPlanLeg_2.lngFlightPlanID =
AAFP_tbl_FlightPlanLeg_3.lngFlightPlanID
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=1) AND
((AAFP_tbl_FlightPlanLeg_1.intLegNumber)=2 Or
(AAFP_tbl_FlightPlanLeg_1.intLegNumber) Is Null) AND
((AAFP_tbl_FlightPlanLeg_2.intLegNumber)=3 Or
(AAFP_tbl_FlightPlanLeg_2.intLegNumber) Is Null) AND
((AAFP_tbl_FlightPlanLeg_3.intLegNumber)=4 Or
(AAFP_tbl_FlightPlanLeg_3.intLegNumber) Is Null));

Here is the SQL for the query which uses other queries to get the LegETD.
(This returns the correct number of records):

SELECT Leg1.intLegNumber, Leg1.dteETD AS Leg1ETD, Leg2.intLegNumber,
Leg2.dteETD AS Leg2ETD, Leg3.intLegNumber, Leg3.dteETD AS Leg3ETD,
Leg4.intLegNumber, Leg4.dteETD AS Leg4ETD
FROM ((Leg1 LEFT JOIN Leg2 ON Leg1.lngFlightPlanID = Leg2.lngFlightPlanID)
LEFT JOIN Leg3 ON Leg2.lngFlightPlanID = Leg3.lngFlightPlanID) LEFT JOIN Leg4
ON Leg3.lngFlightPlanID = Leg4.lngFlightPlanID;
(Leg1, Leg2, Leg3 & Leg4 are all queries. For your interest all are of the
same structure with only intLegNumber criteria being different.) The SQL view
of the query Leg2 is:

SELECT AAFP_tbl_FlightPlanLeg.lngFlightPlanID,
AAFP_tbl_FlightPlanLeg.intLegNumber, AAFP_tbl_FlightPlanLeg.dteETD
FROM AAFP_tbl_FlightPlanLeg
WHERE (((AAFP_tbl_FlightPlanLeg.intLegNumber)=2));

Thanks again!
Greg

 

[John Vinson]

Here is the SQL for the query which uses other queries to get the LegETD.
(This returns the correct number of records):

so... this works? Is performance adequate? (If not try indexing
LegNumber if that's not done already). If it works I'd go with it!

John W. Vinson[MVP]

 

[Guest]

so... this works? Is performance adequate? (If not try indexing
LegNumber if that's not done already). If it works I'd go with it!

John W. Vinson[MVP]
But it involves having eight underlying queries which I was trying to get away from. Is it possible to write a SELECT within another SELECT in SQL?