Updating records with new data while keeping existing data intact

[DXZ08]

I'm sure there's an easy solution to this that I'm overlooking, but here's my problem:

I have a table in Access that already contains some data (which I would like to keep). I have about 24 identical tables (same column names, same records), except these tables contain data in the cells that the Access table does not have any data in.

I would like to transfer the data from the 24 tables into the single Access table to consolidate the data.

For example: In Access table, record #1 has X in cell A3, A5, A7, while the rest of the cells are blank.
In 1/24 tables, this same record#1 has Y in cell A4, A6, A9, and the rest of the cells are blank.
Etc, etc. for the rest of the tables.

Basically, once all the data is consolidated, each record would have all columns filled with data and no data would be deleted/overwritten by another table because each cell is only populated with one set of data in only one of the tables.

How do I do this? I tried the update query, but I think I need some sort of criteria to keep it from deleting the original data while it's adding data from table 1 out of 24.

Thanks,

 

[Joe4]

Yes, an Update Query sounds like it is the way to go.
I don't understand the part of deleting data though. An Update Query should not delete data, only add it.

Perhaps you could provide a small example of what is happening incorrectly, and how you would like it to run?

Quick question, do you have a Unique Primary key?
If not, you may want to try adding an Autonumber field to your final destination table. You don't need to "map" any fields to it in your Update Query, it will populate automatically. It will also ensure each row is unique.

 

[DXZ08]

Hi, thanks for the reply. I do have unique identifier for each record, that's not a problem.
When I attempted an update query: the table which contained data that I wanted to paste into the Access table, had pasted the data correctly, however, it had also deleted the existing data in the Access table instead of leaving it there. It had basically turned the cells that contained data into blank cells. I didn't expect that to happen. I thought that it would leave those as is and only add the new data into cells that were blank in the Access table.

 

[Joe4]

Actually, I think I misspoke. You do NOT want an Update Query. Update Queries are used to update EXISTING records in a table, not add new records to a table. It sounds like what you actually want is an APPEND query, which will add records to an existing table (and shouldn't edit existing records).

Sorry about that the confusion.

 

[DXZ08]

No, the unique records I want to update are the same (say, 10) records in all tables. All tables contain same records and same columns titles.

Imagine it this way. You print 20 identically formatted tables.
Each has data in random columns and rows for the same records) on transparency paper. You overlay them all over each other so the tables perfectly match up and when you lift the papers together and look through them, it looks like a single table that now has data in all columns for those 10 records.

 

[Joe4]

OK, I see that you are after now. There are a few ways I can think to do this:

1. Import all your records to a new table, with each row separate (Append Query). Then do an Aggregate Query which groups by your ID (or whatever fields you want to Group By), then select the MAX function for all the others fields. This should collapse the records for each group down to one and show all the appropriate x's.

2. Use VBA and recordsets to loop through all your data and write the results to a new table (a bit more advanced).

 

[revans]

It sounds like you included all the fields for the table in your UPDATE query. Only include the specific fields you want to update. Otherwise you'll get what you got: overwritten data.

hth,

Rich

 

[Joe4]

It sounds like you included all the fields for the table in your UPDATE query. Only include the specific fields you want to update. Otherwise you'll get what you got: overwritten data.

That will only work if all the records within each table are consistent and each field is controlled by only one table (i.e. TableA contains all updates for field A4 and no other tables do, etc).

If that is the case, then specifying each Update Query to only update those specific fields should work fine. If not, he will probably need to go another route, such as the ones I recommended.

 

[mdmilner]

I like Joe4's suggestion. It's about as direct as you can get.

Just to embellish a little, the same query that groups to pull the out the maximum value should also likely be a make-table (access term) query to create a brand new/fresh table with the final results. You could just make a third step to do that if you'd like.