Sum/Count Aggregate Data?

[cmet]

I have three tables of data to integrate and compare based on a common Operator_ID. I also need to sort the Counts based on Model_ID.

The final output query should include:
Operator_ID1 Model_ID1 Table1Count Table2Count Table3Count

Operator_ID1 ModelID2 Table1Count Table2Count Table3Count

Operator_ID2... etc

I've created a master query with Operator_ID linked to an aggregate query for each operator, but when they integrate, the records don't add properly.

Help ?

[patjones]

Can you post the code that you're using to accomplish this? Thanks.

Pat

[cmet]

 SELECT tbl_operator_master.Operator_Name, tbl_model_master.AIRCRAFT_ID, tbl_aerostrategy_data.[FL 2007]
FROM tbl_model_master RIGHT JOIN (tbl_operator_master LEFT JOIN ((tbl_aerostrategy_data LEFT JOIN tbl_model_x_ref ON tbl_aerostrategy_data.Type = tbl_model_x_ref.AIRCRAFT_FULL_NAME) RIGHT JOIN tbl_operator_x_ref ON tbl_aerostrategy_data.Operator = tbl_operator_x_ref.Operator_Name_1) ON tbl_operator_master.Operator_Name = tbl_operator_x_ref.Operator_Name_2) ON tbl_model_master.AIRCRAFT_ID = tbl_model_x_ref.AIRCRAFT_X_REF_ID
ORDER BY tbl_operator_master.Operator_Name

To get the qry for one database (which is successful).

Then,

 SELECT tbl_operator_master.Operator_Name, tbl_model_master.AIRCRAFT_ID, qry_model_awin_totals.AIRCRAFT_IN_SERVICE, qry_model_aerostrategy_total.[FL 2007], qry_model_acas_totals.[CountOfACAS Serial Number]
FROM tbl_model_master INNER JOIN (((tbl_operator_master INNER JOIN qry_model_acas_totals ON tbl_operator_master.Operator_Name = qry_model_acas_totals.Operator_Name) INNER JOIN qry_model_aerostrategy_total ON tbl_operator_master.Operator_Name = qry_model_aerostrategy_total.Operator_Name) INNER JOIN qry_model_awin_totals ON tbl_operator_master.Operator_Name = qry_model_awin_totals.Operator_Name) ON (tbl_model_master.AIRCRAFT_ID = qry_model_acas_totals.AIRCRAFT_ID) AND (tbl_model_master.AIRCRAFT_ID = qry_model_aerostrategy_total.AIRCRAFT_ID) AND (tbl_model_master.AIRCRAFT_ID = qry_model_awin_totals.AIRCRAFT_ID)
GROUP BY tbl_operator_master.Operator_Name, tbl_model_master.AIRCRAFT_ID, qry_model_awin_totals.AIRCRAFT_IN_SERVICE, qry_model_aerostrategy_total.[FL 2007], qry_model_acas_totals.[CountOfACAS Serial Number]

For the total total query, but the result isn't correct.

[patjones]

OK, well I have taken a little time to absorb your SQL. I take it that the second query is producing something, so can you post what it is giving you, versus what it should be giving you? Not the entire result set, but enough to give us an idea of what you want. Thanks.

Pat

[cmet]

Thanks!

It's producing duplicate records, but they don't correctly match the models and counts and sums.

For example, it lists:

Operator 1 Model1 17 8 1
Operator 1 Model1 1 8 1
Operator 1 Model1 17 6 1
Operator 1 Model1 1 6 1

when the output should be:

Operator 1 Model1 17 8 1
Operator 1 Model2 1 0 0
Operator 1 Model3 0 6 0

[patjones]

Hi cmet,

Is there a one-to-one correspondence between each operator and all three of the queries awin, acas, and aerostrategy? In other words, is there just one record for each operator in each of the three queries?

Pat

[cmet]

Hi cmet,

Is there a one-to-one correspondence between each operator and all three of the queries awin, acas, and aerostrategy? In other words, is there just one record for each operator in each of the three queries?

Pat

No - there is more than one record, because of the model variable. For example, Query1 gives an output of:

Operator1 Model1 15
Operator1 Model2 6
Operator1 Model3 7

Query2 gives an output of

Operator1 Model1 10
Operator1 Model3 6
Operator1 Model4 2

Etc...

[patjones]

Have you tried removing the GROUP BY clause? In this situation, I'm not sure it's necessary.

Pat

[patjones]

Hi cmet,

I have attached a mock file for you to look at. I created some tables with a little test data which I think minimally duplicates your situation.

When you open the the query in SQL view, you can see that it takes the same form as your query but without the GROUP BY; running it seems to give the correct result set. When I added the GROUP BY, I found that it still gave the same result set. This makes sense because the condition

(tbl_model.model_ID = tbl_aero.model_ID) AND (tbl_model.model_ID = tbl_awin.model_ID) AND (tbl_model.model_ID = tbl_acas.model_ID)

on the outermost JOIN ensures that the only rows that are returned are ones where the three model_ID columns are the same. So the grouping shouldn't matter.

This being said, that doesn't explain why the query works for me and not for you. So maybe you can look at what I have in the tables and tell me if I'm missing anything pertinent that could change how I'm viewing this.

Pat