Merge 2 Tables - no primary key

[VBA Beginner]

Hi all,

This must be an age old issue but after searching I cannot find a solution..

I have 2 tables that need to be merged into 1.

The "Main Table" holds address information (e.g. client key, type of building, age, value, etc.), the "2nd Table" holds people resident at that address (e.g. client key, 1st name, surname, age, occupation etc.).

The problem I have is that if there are 3 people living at 1 address (within the "2nd Table") they are held on 3 separate rows (all with the same client key).

The "Main Table" has over 2 million rows (72 columns) and the "2nd Table" over 5 million rows (32 columns).

I need to add the "2nd Table" data on to the end of the "Main Table" where the client key matches however due to the duplicates in "2nd Table" I'm stuck.

Any ideas appreciated , apologies for the length of the question (and if this is in the incorrect forum).

many thanks

Glen

 

[JamesMcS]

If there are multiple records with identical client keys you're going to have a problem. One thing you could do is to create a query with all the fields you want, group by everything in the main table and select first or last of all the fields in the second table. If you want to show all the records in the second table though you're stuck with main table dupes.

 

[JamesMcS]

Oh yeah - in the query you need to liknk by the common field in both tables, otherwise you'll get what's called a Cartesian Product.

 

[boblarson]

You should not be merging the table into one. What is the purpose for this? The contacts are a MANY and the Main Table is the ONE. What are you trying to do which necessitates a single table?

 

[VBA Beginner]

Hi Bob,

The data needs to be fed in to an old IT system which has to have each property against 1 row. Nothing I can do about this as it's remotely hosted.

cheers

Glen

 

[boblarson]

So you basically need to have something create a flat structure or something like that, correct? What do you do with the extra people? Are they just discarded or entered as concatenated in a single field or put into individual fields?

 

[VBA Beginner]

I need to add the additional people's data as seperate fields. I can easily add enough additional columns on to "Main Table" to cater for all of the additional data from "2nd Table), it;s the getting the data in to "Main Table" which I'm unsure on.

thanks