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

 

[neileg]

Some questions first:
Is this a one off job or are the Excel sheets getting up dated?
Is there a 'master sheet' that has a row for each number?

Some ideas:
If there is a master sheet then you can join the other sheets using the serial number. You are correct that an inner join will only return records that exist in both tables, but a left or right join will return all the records in one table and the matching records in the other. The master sheet would ensure that all serial numbers were present.
If this is a one off import you could create a master table by appending all the serial numbers from every sheet into a new table with an index applied to the serial number. This will ensure every serial number exist and no duplicates.
If you really need to hold all this data in one table (and I have doubts about this) then you can append the data from the other sheets to the master table with an append query.

Does this help?

 

[neileg]

DO NOT CREATE MULTIPLE POSTS ON THE SAME SUBJECT. IT JUST WINDS PEOPLE UP LIKE YOU HAVE WOUND ME UP!

Rant over.

 

[CORiverRat]

My exact problem is that there is NO master sheet that has every device I am looking for and all of them have different information in them. I would like to be able to pull the different info (some have network info, some have asset info, some have location info) into one record. I realize many of the records will have blank fields since they may just appear once in the many different spreadsheets, but a lot of the records do appear in more than one table so consolidating that info just makes sense to me. What I have is several different inventories, taken from several different sources at different times. What I need to do is put ALL these together into one master database that can then be used to verify the existance of the equipment listed, be pruned, updated and added to as necessary. Only one spreadsheet of the existing ones gets updated on a regular basis and that is the only one that will be used to update info into this database. The other updates will occur either through a batch format or an individual record format. All the other tables will then be scrapped after I have a good master created.

If that makes any sense, and anyone has a good idea on how to get this done, let me know. Oh, one other thing I just discovered, there are case differences in some of the spreadsheets in the one common field they share and I need to get that problem resolved also.

Thanks again for any help you can provide.

Roger