Multiple Query question

[JMK]

Hi All,

I have 5 queries which I have to "join" together into one full record
source. All the data is already calculated, but I want to be able to view it
in one source which a form will pull from.

The end result should look like:

EmployeeID VehicleID TotalTime PICTime FOTime 3rdTime Last12Time

With the data below pulling from 5 different queries, each from the same
table but requiring information from an Employee Table and a Vehicle Table.

I have tried simple left and right joins and union queries but each time I
get an answer I do not want. This is the last stage to a db which is pretty
time critical too, so any help would be much appreciated.

 

[KARL DEWEY]

Post the SQL of your 5 queries.

 

[John W. Vinson]

Hi All,

I have 5 queries which I have to "join" together into one full record
source. All the data is already calculated, but I want to be able to view it
in one source which a form will pull from.

The end result should look like:

EmployeeID VehicleID TotalTime PICTime FOTime 3rdTime Last12Time

With the data below pulling from 5 different queries, each from the same
table but requiring information from an Employee Table and a Vehicle Table.

I have tried simple left and right joins and union queries but each time I
get an answer I do not want. This is the last stage to a db which is pretty
time critical too, so any help would be much appreciated.

Ummmmm...

What should we do to help?

We have no way to know anything about these five queries, how they are
structured, or how they are logically related, or how you want to combine the
data.

Give us a hand here?

 

[JMK]

Hi Karl,

The SQL is as below

This is what I am using to calculate the times per position, so there are 3
queries which are identical in syntax to this - obviously though rather than
qry3rd its qryCA and qryFO:

SELECT qry3rd.ID, qry3rd.AircraftType, Sum(qry3rd.SumOfFlightTime) AS
SumOfSumOfFlightTime FROM qry3rd GROUP BY qry3rd.ID, qry3rd.AircraftType;


This syntax is from the query which groups the three queries together,
giving me a total number of hours per vehicle type. It gets its data from a
Union Query.

SELECT tblEmployees.ID, tblEmployees.LastName, tblAircraftType.AircraftType,
Sum(qryTotalTypeUnion.SumOfSumOfFlightTime) AS SumOfSumOfSumOfFlightTime
FROM tblAircraftType INNER JOIN (tblEmployees INNER JOIN qryTotalTypeUnion
ON tblEmployees.ID = qryTotalTypeUnion.ID) ON tblAircraftType.ID =
qryTotalTypeUnion.AircraftTypeGROUP BY tblEmployees.ID,
tblEmployees.LastName, tblAircraftType.AircraftType;

This is the query which shows me the total number of hours worked in the
last 12 months:

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.Position,
tblEmployees.CurrentlyEmployed, tblAircraftType.AircraftType,
Sum(qryLast12TypeUnion.SumOfSumOfFlightTime) AS SumOfSumOfSumOfFlightTime
FROM tblAircraftType INNER JOIN (qryLast12TypeUnion INNER JOIN tblEmployees
ON qryLast12TypeUnion.ID = tblEmployees.ID) ON tblAircraftType.ID =
qryLast12TypeUnion.AircraftType GROUP BY tblEmployees.ID,
tblEmployees.LastName, tblEmployees.Position, tblEmployees.CurrentlyEmployed,
tblAircraftType.AircraftType HAVING (((tblEmployees.Position)=9) AND
((tblEmployees.CurrentlyEmployed)=Yes));


So what I need to do is to combine all these into one query which will
display all the data together. The problem I have is one employee may operate
more than one vehicle type and may operate in one or more positions on that
vehicle within the year, but then another employee may only operate one
vehicle type and one position, but I still want to be able to see their data
at the same time!

This example is what I would like the final data to look like:

NAME TYPE TOTAL LAST12 CA FO 3RD

John Doe DHC-8 15,000 500 500 0 0
John Doe DHC-6 2,000 150 0 100 50
Plain Jane BE10 750 500 0 400 100
Someone DHC-8 2,000 700 200 500 0

 

[JMK]

Hi Karl and John,

I think I figured it out with a little blood, sweat and swearing!

I've ended up using a Union Query and having it specify where the record
came from, then using another Select Query on the Union with a couple IIF
statements to re-order it back into some sense of logic (SQL below).

Do you guys see any problems with doing it this way, or can you see a better
way with fewer queries?

For the Union:

SELECT *, "3rd" AS POS FROM qry3rdTypeTotal
UNION
SELECT *, "CA" AS POS FROM qryCATypeTotal
UNION
SELECT *, "FO" AS POS FROM qryFOTypeTotal;

and for the Select:

SELECT tblEmployees.ID, tblEmployees.LastName, tblAircraftType.AircraftType,
Sum(IIf([qryTypeUnion]![POS]="CA",[qryTypeUnion]![SumOfSumOfFlightTime],0))
AS CA,
Sum(IIf([qryTypeUnion]![POS]="FO",[qryTypeUnion]![SumOfSumOfFlightTime],0))
AS FO,
Sum(IIf([qryTypeUnion]![POS]="3rd",[qryTypeUnion]![SumOfSumOfFlightTime],0))
AS 3rd FROM tblAircraftType INNER JOIN (qryTypeUnion INNER JOIN tblEmployees
ON qryTypeUnion.ID = tblEmployees.ID) ON tblAircraftType.ID =
qryTypeUnion.AircraftType
GROUP BY tblEmployees.ID, tblEmployees.Position,
tblEmployees.CurrentlyEmployed, tblEmployees.LastName,
tblAircraftType.AircraftType HAVING (((tblEmployees.Position)=9) AND
((tblEmployees.CurrentlyEmployed)=Yes)) ORDER BY tblEmployees.LastName;


Thanks Again,

Jason