Union Query help! 1

[aageorge]

I need to combine a couple of tables having one or more similar fields. For eg:
T1 T2 T3
Date A B C D Date B C Date A C
8/1 10 10 10 10 8/2 20 20 8/1 30 30
8/2 10 10 10 10 8/2 30 30
8/3 10 10 10 10

I need to combine everything into one table:

T1
Date A B C D
8/1 40 10 40 10
8/2 40 30 60 10
8/3 10 10 10 10

I have tried designing a simple query with expressions adding the same fields across the tables. The problem with that is it dosen't show results if there are missing dates (i.e, in the above example only 8/2 will appear). I tried creating a union query but I am facing problems with different no. of fields needed to be merged from each table. I would appreciate any suggestions.

 

[lespaul]

are you adding the data together? ie, 8/1 A = 40 in the result grid because T1FieldA = 10 plus T3FieldA = 30, sum together to get 40? If so, a UNION query won't do what you're asking anyway. What is the SQL you mention that doesn't show results for missing dates?

Leslie

 

[aageorge]

Leslie,

You are right, the union query won't sum it up. I was planning to sum it up after it does the union. The biggest problem is getting all the fields into one table.

 

[Norris68]

SELECT TheDate, Sum(A) AS SumA, Sum(B) AS SumB, Sum(C) AS SumC
FROM (SELECT TheDate,A,B,C FROM T1
UNION
SELECT TheDate,0 AS A,B,C FROM T2
UNION
SELECT TheDate,A,0 AS B,C FROM T3)
GROUP BY TheDate

... just don't try to design it except in SQL view

 

[aageorge]

Norris68,

Thanks a lot, worked like a charm....

 

[aageorge]

Norris 68,

I am setup the above query for my database. But when I tested it with some numbers it returned the field names instead of the counts. In my database the fields A, B etc are part nos. like 1324, 4245, 6353 etc. So instead of getting the output as

Date Total1324 Total4245 Total6353
8/10 10 10 10
8/11 20 20 20

the output I get is

Date Total1324 Total4245 Total6353
8/10 1324 4245 6353
8/11 1324 4245 6353

I am sure it has to do with the way I assigned part nos. as the field names. So how do I correct this problem?

Thanks.

 

[Norris68]

You have numeric field names?!! Try enclosing them in [square brackets]. Otherwise it will take the literal numeric value (as per the zeros in my example to substitute for missing fields)