Combiing Tables

[GEO]

I have two tables that I would like to combine into a
third.

Table1
lastname total1

Table2
lastname total2

Table3
lastname total1 total2

*Not every lastname in Table1 is in Table 2 and vice
versa. What this will leave is that some lastnames will
have no data for total1 or total2.

**Is there an easy way to add total1 and total2 to total3?

 

[Seano]

"Geo"

Break it down to 3 queries:

(*learning mode*=on)

1- Combine the tables' data that matches including all the data from table
1:
Create (Make-table) a table (Table3) from the Table1 LEFT JOIN Table2 On
table1.lastname= table2.lastname.

2- Add the data missed (not joined) from Table2:

INSERT INTO table3 ( Lastname, Table1_Total, Table2_Total )
SELECT Table2.Lastname, Table1.total, Table2.Total
FROM Table1 RIGHT JOIN Table2 ON Table1.Lastname = Table2.Lastname
WHERE (((Table1.Lastname) Is Null));

3- Add the two totals.
Check if the total fields are text or numerical datatype. Convert to
numerical (change in design mode) if text otherwise 1+2 will equal 12!
Add a new field (in design mode) of the table (total3) matching the datatype
of the other totals).
Write an Update query that adds the two fields together and places them in
Total3 field.

UPDATE table3 SET table3.Table3_total = [Table1_Total]+[Table2_Total];

Cheers
Sean


----- Original Message -----
From: "GEO" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Monday, February 16, 2004 3:44 PM
Subject: Combiing Tables