Need to Merge Multiple Tables

[CORiverRat]

I have several tables I have brought in from excel spreadsheets. Each one has one common column (lets call it serial number), but then all the rest have a variety of information (one has location info, one has network info, one has asset tracking info, one has sw load info, etc). What I want to do is a) collect all the records from each table into one, b) merge the records with common key field so I don't have multiple records for this one key field and can have just one record with the information from all the different tables in it, c) not lose any records that don't have a sister record in some other database.

Anyone have any idea the best way of attacking this problem is. Common joins cause problems because of records that are not in both tables get left behind. Union and append queries creae many multiple records with the same ser # but only the bits of info they brought in from their original spreadsheet, not the other info from the other speadsheets. I am an Access Newbie and trying to figure out if this is even possible without extensive coding.

Thanks,

CORiverRat

 

[CORiverRat]

Question... Will this pick up records with serial numbers that are not in the first table I create. That is where I am running into the biggest difficulty. Because of the nature of the joins I can't seem to be able to snag all the records that are in some all of the tables without creating multiple duplicate records (through union or append joins) that aren't in others. What I would like is something that would do an update if the serial number already exists, but would do an append if it didn't.

Thanks

 

[nateobot]

Question... Will this pick up records with serial numbers that are not in the first table I create. That is where I am running into the biggest difficulty. Because of the nature of the joins I can't seem to be able to snag all the records that are in some all of the tables without creating multiple duplicate records (through union or append joins) that aren't in others. What I would like is something that would do an update if the serial number already exists, but would do an append if it didn't.

Thanks

You could create two queries. One that appends records if the serial doesn't exist in the master. Another that updates if the serial exists.

I know this is almost the same thing that you said above.

Another option would be to do as Pat said below and create a master table with ALL the possible serial numbers. Add your columns and update the information from the other spreadsheets into this master. This would cover all possible serials and only update information from each table(spreadsheet) where the serial matched. Then you can go through and normalize everything.

 

[CORiverRat]

So you can do a conditional append query? If that is true then I think I figured out how to do this. How exactly would one go about setting up a conditional append query, to only grab those records that are not already there? Once that is done I can do a series of regular left sided join queries from the master into each of the other tables to do the updates from there.

Thanks again for the help.

Roger

 

[nateobot]

Use the "Find Unmatched Query Wizard" and create a simple select query. That will show you how to only select the records that are not already in your Master table. You can then use that knowledge to enter your criteria for appending the new data.

 

[nateobot]

Actually I might have fibbed a on that one.

In your append query. Put the following (with your modifications) in the criteria of the SerialNumber field:

Not In(Select SerialNumber from tblAppendingTo)

 

[CORiverRat]

When I try that not in statement in an append query to look at the field in the MASTER db it still adds a new record for each entry. the format I have is

NOT IN(SELECT SerialNumber from MASTER).

One other note, this is one of the fields I am pulling from the 2nd db to be added to the first. Could that be an issue at all?

Thanks