Question Import specific data cells from excel into Access 2016 (1 Viewer)

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
Hello,

I have an access database that one of the table column's data needs to be updated. There is existing data in this column, but there are a lot of empty cells previously. I need to update those empty cells now with the new data that I have in an excel spreadsheet. There is about 30k total records and I have to update about 9k.

Is there a way to upload the new data into this table column (only empty cells) while keeping the existing data for other records? The data in the excel spreadsheet contains 2 columns. The first column is the name of company and the second column is a set of numbers like order receipt number (9 digits) that corresponds to the matching company name. I need help importing the second column into the Access database, but only to the empty cells matching the company name without replacing/deleting any data for other records.

Data from this table comes from a front-end form that staff would use. The second column (order receipt number) would be entered at a different time, but it was forgotten when the staff left the company. I'm sort of new to Access DB, so any help is greatly appreciated. Any questions or need more detail, feel free to let me know!!

Thanks,
Carmen
 

June7

AWF VIP
Local time
Today, 02:45
Joined
Mar 9, 2014
Messages
5,425
Can you set a link to the Excel table?

Build an UPDATE query with WHERE clause that restricts which records to update. The query would include a JOIN between the Excel link and data table. Will this be a 1-to-1 relationship?

Provide sample data if you need more help. If you want to provide db, follow instructions at bottom of my post.
 

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
Hello,

Thanks for replying to help me!

Unfortunately, this DB was done way before my time and they do not want any linking to an Excel table. How would you create an UPDATE query that can restrict which records to update? I have attached an image with some sample data, so it'll be easier to explain. From the screenshot, I need to update the column "AR transaction" (empty cells), but has to match to the corresponding "Company Number" column. That column is a unique ID to the "Company Name" column. These are 1-1 relationships.

Thank You,
Carmen
 

Attachments

  • Capture.JPG
    Capture.JPG
    54.8 KB · Views: 135

June7

AWF VIP
Local time
Today, 02:45
Joined
Mar 9, 2014
Messages
5,425
The link to Excel could be temporary, just to get the data fixed then drop the link. Or import the spreadsheet into a table, do the update, delete the imported table. The query builder can help you construct the UPDATE action SQL.

Otherwise, use automation code to manipulate Excel objects. http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst
 

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
If I use the automation code from the link you sent as reference, will it input the new data from Excel based on any empty cell, or will it go into the correct cell that it needs to be in matching the Company Number column? Since I have 30k records total and only about 9k needs to be updated. Just wondering if the table needs to be filtered or configured in a way that it recognizes it.

Thanks,
Carmen
 

June7

AWF VIP
Local time
Today, 02:45
Joined
Mar 9, 2014
Messages
5,425
Code is an example. Customize to suit your situation. Link or import is still probably easiest approach.

Or you can even try copy/paste selected Excel cells to Access table. Make sure each dataset is sorted and filtered to same set of records.
 
Last edited:

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
I agree, long-term would be better to use the VBA code, but for now I think I will go with your idea of importing the table, update the data, and delete the imported table. Which way is better linking or importing the database?

I attempted the import method, but need some assistance on creating the update query. Like I mentioned earlier, I'm pretty new with Access... still not sure how I ended up with this project. I'm stuck on how you write the criteria to restrict which records to update. I attached screenshots of where I'm at on the query builder and on the SQL view.

Thanks again for the help, I really appreciate you helping me out here!
 

Attachments

  • Capture.JPG
    Capture.JPG
    29.3 KB · Views: 136
  • Capture2.JPG
    Capture2.JPG
    13.6 KB · Views: 115

June7

AWF VIP
Local time
Today, 02:45
Joined
Mar 9, 2014
Messages
5,425
Include tbl_AN.CompanyNumber in the SELECT. Apply filter under tbl_AN.[AR Transaction]: Is Null

See if that gets the correct set of records for update. When the dataset looks correct, click UPDATE on the ribbon and define the fields to use in the update action. Then run the query.

This will be an ongoing process?
 

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
I applied the filter, but how do I know if it's pulling the correct set of records to be updated? When I tested to UPDATE button, it didn't do anything and when run the query, I got this error that I have attached.

No, it would be a one-time update, but future references if this does happen again, would the VBA code be better?

Sorry for the million questions :(. Thanks again for the help!
 

Attachments

  • Capture3.JPG
    Capture3.JPG
    47.7 KB · Views: 121
Last edited:

mike60smart

Registered User.
Local time
Today, 10:45
Joined
Aug 6, 2017
Messages
1,899
Hi

Your Update Query should like the example shown in the attached

Update.JPG
 
Last edited:

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
Hello,

I tried modifying my UPDATE query like how you had it, with the criteria I need, but when I run the query, it did not update any of the empty cell records. I attached a screenshot of my query
 

Attachments

  • Capture4.JPG
    Capture4.JPG
    41 KB · Views: 122

isladogs

MVP / VIP
Local time
Today, 10:45
Joined
Jan 14, 2017
Messages
18,186
MikeSmart
I haven't read the entire thresd but …
In your screenshot you have a cartesian join with matching fields linked using filter criteria.
Why not just join the two tables by the common MemNo field as that will be far more efficient
 

isladogs

MVP / VIP
Local time
Today, 10:45
Joined
Jan 14, 2017
Messages
18,186
Wolves1
Remove the join on the AR Transaction field.
The other join on PK fields guarantees uniqueness.
Also you can't update a field if it is being used in a join
 

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
Hi Colin,

It updated the data to the field, but it's incorrect. I need the corresponding data in the AR Transaction field to match according to the Company Number field. Also, some of the data updated are incorrect as there are duplicates, but if the entire column is empty, then it enters the data correctly into the respective fields. Issue is I have existing data already, but I don't want the updates to creating duplicates. Hopefully that makes sense :(


I'm running on test data, so I understand how it works, before I test it on the actual DB. I appreciate the help.

Thanks,
Carmen
 
Last edited:

mike60smart

Registered User.
Local time
Today, 10:45
Joined
Aug 6, 2017
Messages
1,899
Hi Colin

Well I use this method all the time and it works every time by updating all related records.
 

wolves1

Registered User.
Local time
Today, 03:45
Joined
Feb 3, 2019
Messages
22
Hi Colin,

Just an update to my previous post, I tried running the query again by removing the previous uploaded data to see if it does the same thing and it's now working fine. Data is entered correctly matching the Company Number and no duplicate data was made. I will try again with a larger set of test data to see if it shows an errors before moving forward the actual DB.

Thanks for the help, it's much appreciated and same with everyone who pitched in this thread post!.

Thanks,
Carmen
 

isladogs

MVP / VIP
Local time
Today, 10:45
Joined
Jan 14, 2017
Messages
18,186
Hi Colin

Well I use this method all the time and it works every time by updating all related records.

Yes it will do but doing it with a join should be more efficient and therefore faster.
Suggest you try both methods on a table with a large number of records to update so that it takes a few seconds. You can compare the time taken in each case using the Timer function to a precision of 0.01s
 

mike60smart

Registered User.
Local time
Today, 10:45
Joined
Aug 6, 2017
Messages
1,899
Hi Colin

Many thanks will take your advice and test the next time I have to use this method.

I always stick with a proven method but will always take good advice.
 

isladogs

MVP / VIP
Local time
Today, 10:45
Joined
Jan 14, 2017
Messages
18,186
Hi Mike

I just did a fairly simple test on a table of almost 180K records.
In order to minimise variations due to other CPU activity, I looped through each test 10 times ..
Just like I do on all my speed comparison tests

The differences were fairly small but the INNER JOIN version was slightly faster each time

First updating all the records
Test 1 - INNER JOIN ; Records = 175684 ; Loops = 10 ; Time taken = 44.69 s
Test 2 - CARTESIAN JOIN ; Records = 175684 ; Loops = 10 ; Time taken = 44.98 s

Then with an added filter
Test 1 - INNER JOIN ; Records = 142111 ; Loops = 10 ; Time taken = 40.59 s
Test 2 - CARTESIAN JOIN ; Records = 142111 ; Loops = 10 ; Time taken = 40.78 s

Then with two added filters
Test 1 - INNER JOIN ; Records updated = 92550 ; Loops = 10 ; Time taken = 35.8 s
Test 2 - CARTESIAN JOIN ; Records updated = 92550 ; Loops = 10 ; Time taken = 36.04 s

Tomorrow, I'll repeat the test with a more complex update query.
If you like I can upload the code used for you to try out on a database of your own
 

Users who are viewing this thread

Top Bottom