Beginner: Join Tables

[ciel220]

Hi,

I have 2 tables:

On table 1, I have
Id Type Date Details
-- ---- ---- -------
1 A Aug02 ABC
2 B Aug02 CDE

On table 2, I have the info. for updated entry
Id Type Date Details
-- ---- ---- -------
1 A Oct02 CDE


What I want is, a merged table with;
Id Type Date Details
-- ---- ---- -------
1 A Oct02 ABC
2 B Aug02 CDE

How could I do that?
I tried to creat a relation in Access and then use Inner join in the SQL query, however, I could never got the entries I want.

Please shed some light on this!

Thanks!!!!
ciel220

 

[zerosandones]

Since your question is unclear, here are two scenarios to solve your dilemna:

Scenario #1:

In this scenario, I assume that you want to insert records into a new table, let's call it Table3. The primary keys in Table1 and Table2 are both "Id" and these are the fields which relate these two tables to each other. If an Id exists in Table1, but not in Table2, add it to Table3. If an Id exists in table2 but not in Table1, also add it to Table3. If there is a match on Id, insert data from Table2 for that Id.

SQL provides the UNION clause to facilitate this requirement. First, lets create a query named qryDistinctIDs.

SELECT Id
FROM Table1
UNION
SELECT Id
FROM Table2;

Next, lets insert those unique Id values into Table3.

INSERT INTO Table3 (Id)
SELECT Id
FROM qryDistinctIDs;

Finally, lets update each of the new Table3 records with data from Table2 and, if not present, the data from Table1:

UPDATE (Table3 LEFT JOIN Table1 ON Table3.Id = Table1.Id) LEFT JOIN Table2 ON Table3.Id = Table2.Id SET Table3.Type = nz([Table2]![Type],[Table1]![Type]), Table3.Date = nz([Table2]![Date],[Table1]![Date]), Table3.Details = nz([Table2]![Details],[Table1]![Details]);

Scenario #2:

In this scenario, I assume that Table1 is the driving table and that Table2 only contains records with an Id value that already exists in Table1. The primary keys in Table1 and Table2 are both "Id" and these are the fields which relate these two tables to each other. If there is a match on Id, insert it into Table3 with the data from Table2. As was the case in scenario #1, I'll place the newly updated records in a third table named Table3:

This scenario is much simpler and can be accomplished with one append query as follows:

INSERT INTO Table3 ( Id, Type, Date, Details )
SELECT Table1.Id, nz([Table2]![Type],[Table1]![Type]) AS MostCurrentType, nz([Table2]![Date],[Table1]![Date]) AS MostCurrentDate, nz([Table2]![Details],[Table1]![Details]) AS MostCurrentDetails
FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id;

Hope this helps,

00001111s

 

[ciel220]

Hello =)

Thanks for your reply. However, the query you gave me doesn't seems to work. First of all, I got a 'syntax' error on the 'insert into' part. I fixed that as follow:

SELECT Table1.Id, nz([Table 2]![Type],[Table1]![Type]) AS MostCurrentType, nz([Table 2]![Date],[Table1]![Date]) AS MostCurrentDate, nz([Table 2]![Details],[Table1]![Details]) AS MostCurrentDetails INTO Table3
FROM Table1, [Table 2];

What happen now is, this query return me with a lot of records... 4 times of what I suppose to get.
I think there's something wrong with it... what is nz function use for? how could I fix that?

I think I'm in senario 2 of what you are saying.

Thanks a bunch!!!!
ciel220

 

[zerosandones]

In my list of assumptions, the primary key of each of your tables is the ID field ... is this true?

If not, you have a flaw in your table design with respect to what you are trying to accomplish.

Since you have verified that scenario #2 is the one which fits your situation, what you have confirmed is that either Table1 or Table2 or both tables do not have primary key "Id". If they both do, as they should from your explanation of how Table2 is being used, your quadruplicate results are a physical impossibility.

Please confirm the primary keys of each table and which field or fields from each table define how Table1 is associated to Table2.

00001111s