Need Help Running Query

[Guest]

My SQL is kind of rusty so I need help running a Query on 4 tables that
contain the same information... In the following columns..

SKU Jan Feb Mar April blah blah blah....

What I need is a Query that will take the SKU from table A and the Sales
data for each month and summarize it across the 4 tables for each SKU.. With
the final output only containing the SKU and the total for each month.
However the tables do not contain all the SKUs that the other tables have.
Table A has 137 rows but table B has only 89 rows and so forth, but the SKUs
are the same when they are present.

Can anyone help me?

 

[Tim Ferguson]

My SQL is kind of rusty so I need help running a Query on 4 tables that
contain the same information... In the following columns..

SKU Jan Feb Mar April blah blah blah....

This sounds like a Major Design Problem. The fact that you are having
difficulty writing the query is just one symptom.

Best of luck


Tim F

 

[Guest]

Although Tim is correct, if you have multiple identical tables, Why?
Even if they are tables that you import from other databases, you should be
combining them and have an additional field that designates the source of the
data ("Store 1", "Store 2", "Catalogue Sale", etc)

But, with that said, you can do this with a Union query. You can't create a
Union query with the query builder, you have to put it together in SQL.

It does not matter that there are different numbers of rows or that some
SKUs are not in all tables, that is what union queries do.

It doesn't even matter that the structures of the tables are dissimilar, as
long as you have the same number of fields for each table in your query and
that the data types match. You can also add a calculated column to identify
the source of the rows in the query.

Here is an example from one of my apps that has only two tables. You just
keep adding UNION ALL for the additional tables. (Excuse the poor naming
standards, I inherited the beast)

SELECT "A" As Identifier, Actual_res_export.Activity,
Actual_res_export.Pool2 As Pool, Actual_res_export.PerformAcctUnit,
Actual_res_export.itm, Actual_res_export.ProjectID, Actual_res_export.RRNR,
Actual_res_export.Jan, Actual_res_export.Feb, Actual_res_export.Mar,
Actual_res_export.Apr As Apr, Actual_res_export.May As May,
Actual_res_export.Jun As Jun, Actual_res_export.Jul As Jul,
Actual_res_export.SumOfAug As Aug, Actual_res_export.Sep,
Actual_res_export.Oct, Actual_res_export.Nov, Actual_res_export.Dec FROM
Actual_res_export
WHERE Actual_res_export.ProjectID <> "billable" And
Actual_res_export.BillCat = "unbillable";
UNION ALL SELECT "P" As Identifier, Plan_res_export.[Activity #],
Plan_res_export.Pool, Plan_res_export.PerformAcctUnit, Plan_res_export.itm,
Plan_res_export.ProjectID, Plan_res_export.subtask,
Plan_res_export.SumOfJan, Plan_res_export.SumOfFeb, Plan_res_export.SumOfMar,
Plan_res_export.SumOfApr, Plan_res_export.SumOfMay, Plan_res_export.SumOfJun,
Plan_res_export.SumOfJul, Plan_res_export.SumOfAug, Plan_res_export.SumOfSep,
Plan_res_export.SumOfOct, Plan_res_export.SumOfNov, Plan_res_export.SumOfDec
FROM Plan_res_export
WHERE Plan_res_export.ProjectID <> "billable" And Plan_res_export.BillCat =
"unbillable";