Help to get XLSX sheet back into remainder of Access Db, please?

PurrSent · Aug 5, 2018

I have a database in Access 2010, flat table with no relational tables.

I was required to send the ‘Membership’ data to ‘head office’ for data cleansing, re GDPR requirements so I chose to send only name, address, contact details and not the other fields I have developed.

The ‘Membership’ data is a query run on the main database table. I then have a number of queries set up in relation to Membership requirements.

Having sent this ‘Membership’ data as an Access database table (using Make table in Design view), I have been sent the ‘cleansed’ data in xlsx format.

How should I now get this xlsx data back into my Access database, that has other fields that I need?

This will, undoubtedly, be a regularly required process to undergo, unfortunately.

I would very much appreciate your help to achieve a simple, yet effective, method to get this ‘cleansed’ data into my Access database so I can then continue to use the database and queries for the purpose I have designed them for.

Many thanks

NewTricks · Aug 5, 2018

More info is needed to design a method that works and is also efficient. The most important info is how the Membership data was "cleansed."
Were records deleted but none added?
Were some records deleted and some added?
Were individual fields in some records edited?
If fields were edited, which fields, and do you have a primary key that was never edited?

In addition to these questions, it would be helpful if you would list the fields in the orig database and also the fields in the 'cleansed' spreadsheet. Also the text of the queries used to pare down the orig DB to the 'Membership' data.

NewTricks · Aug 5, 2018

Hmmmm…… Having examined my own questions for a while, I now think there is a straightforward method even if all my questions are answered yes. The only requirement is that there is a primary key in the original data whose values are not changed in the cleansed data.
Of course, work only with COPIES until the method is tested and proven, safeguarding your original data.

Begin by adding blank columns to the cleansed spreadsheet with fieldnames that match the fields in the original data which were left out.
Upload this spreadsheet to the database as a NEW table.

Design a query to update the new table from the original table. It would look something like this:
UPDATE newtable n
SET n.field1 = o.field1,
n.field2 = o.field2,
n.field3 = o.field3,
n.field4 = o.field4,
(etc., the fields above are ONLY the fields that were dropped from the Membership data)

FROM originaltable o
WHERE n.primarykey = o.primarykey

Deleted (cleansed) records were already purged, the records which have edited fields will appear in the new table with the edits, and the query above will update the dropped fields from the old records. Any new records added in the cleansing will not have the dropped fields populated, but you would have to populate these yourself anyway, since they are new.

Finally, delete the old table and rename the new table to the original name.
Hope this works for you. There may be a more elegant solution, but I think this one will work.

PurrSent · Aug 6, 2018

Many thanks, NewTricks. I really appreciate your help

however, I've since discovered, after looking more closely at the spreadsheet that has been returned, that 'they' have put two different databases into one, both of our branch, I have to say. The two databases are relating to two different lists, of a differing purpose. Mine is Membership and the other is a monthly draw thing, and supporters can pay to join both, or one or the other. Another volunteer manages the monthly draw so putting them together does not make any sense!

So, many of the records are now duplicated!!! What a shambles!

Unfortunately, for now, I need to get back to 'head office' to discuss with them how they expect us to work with this shambles, especially if this is to be a monthly occurrance. I'm not prepared, as a volunteer, to deal with all this. It's far beyond what anyone would/should expect of a volunteer, in my opinion.

I'm so very sorry you've gone to the trouble of giving me a way of working with this spreadsheet but this is going to be far more complicated than at first anticipated. As I say, if this is what I have to deal with each month, then I will definitely quit my position and they can sort it out from there. Such a shame but I'll certainly do what I can to get this all sorted out so what gets sent is usable as it is.

Many thanks. Your help has been very much appreciated. Just very sorry it's turned out to be a non-starter.

Help to get XLSX sheet back into remainder of Access Db, please?

PurrSent

Board Regular

Excel Facts

NewTricks

New Member

NewTricks

New Member

PurrSent

Board Regular

Similar threads

Forum statistics

Share this page

Help to get XLSX sheet back into remainder of Access Db, please?

PurrSent

Board Regular

Excel Facts

NewTricks

New Member

NewTricks

New Member

PurrSent

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock