Multiple Table problem

[coyotem78]

Hiya

I have problem hopefully someone can solve.

Lets say I have 2 tables. Each table contains user data. Let say, the 2 tables represent 2 months. In each month we may have the same users and some new users. An example:

Table 1

UserA1 16/10/1989 25
UserG 25/08/1957 3
UserA2 30/05/1985 8

Table 2

UserA1 16/10/1989 16
UserB 16/10/1989 8
UserG 25/08/1957 2

where the 2nd col is the Date of Birth, 3rd col is some data. In addition UserA1 and UserA2 have the same name, but different people.

Now what I want to do is to display all the users and sum the data like this

UserA1 16/10/1989 41
UserB 16/10/1989 8
UserG 25/08/1957 5
UserA2 30/05/1985 8

Obviously I have to use a query of some kind but don't know how to do it, prolly be very simple.

Thanks in advance

coyotem78

 

[dk]

Hi,

You can do this by using a union query and then by aggregating your data. You can either do this using 2 queries (1 for the union, 1 for the aggregration based on the first) or you can do it all in one hit. Here's how to do the latter:-

This example assumes your field names are Field1, Field2 and Field3 and your tables are named Table1 and Table2. You'll need to change to match your tables' field names.<pre>SELECT SubQry.Field1,SubQry.Field2, Sum(SubQry.Field3) AS TotalField3 FROM
(SELECT * FROM Table1 UNION ALL SELECT * FROM Table2) AS SubQry
GROUP BY SubQry.Field1, SubQry.Field2;</pre>

What this is doing is using a nested query. The nested union query is executed first, then the outer query is executed. Let me know if you can't get it to work.

_________________
<font face="Arial Black" color="#0000FF">Hope this helps,
DK</font>
This message was edited by dk on 2003-02-20 06:36

 

[coyotem78]

Hi dk

I can't seem to get the query to work. Can u help?

thanks

coyotem78

 

[dk]

What are the table names and the field names in each of the tables?

 

[coyotem78]

There are actually too many fields to mention but to make things simple cut it to 4: Letters of Name; Date of Birth; Agency ID; Transport - total (one way trips)

Table names: Extract 2001-3; Extract 2001-4