Avatar of Gilberto Sanches
Gilberto Sanches
Flag for Suriname asked on

Access query complete data

I need your help with completing data in a Access database. We have a database with multiple rows with records that are incomplete. The incomplete data we have in a Excel document.

For example, we have in Access column A, B, C, D, E, F, G, H.
Column A in Access matches the Field in Excel.
The situation is that in certain rows, records of column E, F, G, are missing.
In others B, C, D. In some cases all except A, G missing.

What are the options to complete the data?

I thought of something, a query or script or something better, that can do the following:

1st find matching unique record (from a certain Column) between a Access table & a Excel sheet.
2nd, Based on that then search horizontally in the Access table for blank records.  
3rd, Once found blank records in Access take it's field-/column name then find it in the Excel sheet,
4th, Once found check if that cell is blank.
5th, if blank go to step 1 for other record.
6th, if NOT blank copy cell value to record in Access table.
7th repeat process
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
slightwv (䄆 Netminder)

Not sure I completely understand what you are wanting to do but it sounds like you want to merge data you have in Excel into an Access database.

Is this a one-time thing or will you need a way to do this often?

If a one-time deal, I would probably just import the Excel data into a new Access table and write a few update/insert queries to pull the data into your primary Access table.
Eduard Ghergu

Hi,

You can create some VBA code to open the Excel file, read the values according to your requirements and update the Access table with these values.
John Tsioumpris

Just Use Excel Automation....load up your Excel spreadsheet...read the values cell by cell...compare them to Access and do the necessary updates.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dale Fye

First off, lets get your terminology right.

Record - this refers to a row of data in Excel, not a column.

If your Access table, and Excel spreadsheet contain a unique value in every row (called an primary key), this could be as simple as a column containing the values 1 - N, then you can link your Excel spreadsheet to Access using the External Data option in the Access  Ribbon.

Then you simply create an update query that joins the Access table to the linked Excel spreadsheet, and updates the values in the Access record with those in the Excel spreadsheet.
Gilberto Sanches

ASKER
Thanks for the answers so far experts.

John, I saw you recommend Excel automation often. What is it? &where can I find it?
Gilberto Sanches

ASKER
Yes, SlightWV, a data merge. We do the append & update queries but I found the situation too complicated. Cause these queries can easily mess up the data.

That's why I ask for help.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

Excel Automation is the process of controlling Excel via Access
Start from here for start: https://www.devhut.net/2016/07/07/vba-automating-excel/
Gilberto Sanches

ASKER
Hey John, okay. I opened the website you shared. We are getting closer to applying it. What training would you recommend for a beginner regarding Excel automation?
slightwv (䄆 Netminder)

Again, is this a one time thing or will you need to do this on a regular basis?

A lot of VBA code seems like overkill if this is a one time thing.

>>Cause these queries can easily mess up the data.

I don't see how a query can mess up data.  Can you explain?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gilberto Sanches

ASKER
Thanks for the guidance John, I appreciate it
PatHartman

Excel automation is more complicated and prone to error than an update query.  To join your Access table to the Excel data and perform an update query, you will need to import the Excel data since a query with the join will not be updateable because the linked Excel sheet will not be updateable.

- Use the QBE to start the query.  
- Add the two tables and draw the join line between the two "key" columns.
- Select all the columns in the table you want to update.
- Change the query type to update and access will add a new line to the grid.
- For every column except the key column, add the corresponding column from the excel sheet.  Access will provide intellisense that will help with the typing.  Just start with exceltablename. and the field list will pop up.
- Save the query.

this will update every column in the Access table with the value from the spreadsheet.  If you only want to update the empty columns, we need to do more work and to do it, switch to SQL view.

You will see the query string and it will include a number of tbl1.fld1 = tbl2.fld1 pairs.  You will change each of them to:

IIf(tbl1.fld1 & "" = "", tbl2.fld1, tbl1.fld1)

This if statement concatenates the Access field with a ZLS so that only a single test needs to be done to identify both null and ZLS values.  If the Access field is "empty", the field will be updated with the spreadsheet field, otherwise it will be updated with its own value.

As with ANY update process.  Back up the database (I also zip it to prevent accidents) before testing your update query.

Access is a Rapid Application Development Tool and it does a lot of stuff for you.  Sometimes if you do know how to code, your first inclination will be to write code to solve a problem.  In the long run, the best solution will be to first try to use an action query.  Queries are much faster than code loops and simpler to implement.  Your next option is to manipulate the settings of a form or report, and your last option is to write code.  This mindset will allow you to make the best of Access and not constantly fight with it.
John Tsioumpris

@Pat I have to disagree... reading your comment I think you have more chances of doing something wrong than taking some time to learn something useful that will always resolve similar "issues" as "you" get full control
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

You still have full control using straight SQL.  I believe VBA is over-kill for this.  If I was to write VBA, it would be to help automate the loading of the Excel data into Access then use straight SQL to merge.
Scott McDaniel (EE MVE )

I'd also suggest using Pat's suggestion of linking the Excel table and then using straight SQL to update your data. Automation is great if you need to actually work with the Excel document (i.e. change cell formats, add rows/columns), but for getting data into Access (or comparing data between excel and Access), linked tables would be my first choice.
PatHartman

@John,
This seems to be a case where the spreadsheet is the "master" so the simple query built with QBE is all that is needed.  Only if the requirement is to update ONLY the "empty" columns do you need to even switch to SQL view.

Working in VBA you would need the same field by field code so the query eliminates the code logic of automation and looping.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck