Access query complete data

Gilberto Sanches
Gilberto Sanches used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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 GherguArchitect - Coder - Mentor

Commented:
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 TsioumprisSoftware & Systems Engineer

Commented:
Just Use Excel Automation....load up your Excel spreadsheet...read the values cell by cell...compare them to Access and do the necessary updates.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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 SanchesFreelance ICT Consultant

Author

Commented:
Thanks for the answers so far experts.

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

Author

Commented:
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.
John TsioumprisSoftware & Systems Engineer

Commented:
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 SanchesFreelance ICT Consultant

Author

Commented:
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?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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?
Software & Systems Engineer
Commented:
Training is always the same for a beginner.
Search...Read...Do the Exercises....Experiment....and again Search...Read...Do the Exercises....Experiment
This is a never ending procedure
Right now you have a goal...as you said you opened the site...what did you understand and what did you miss.
Search again for the term "Ms Access VBA Excel Automation" ...you will get more material..more tutorials...examples...usually the first results should give you a good head start...so don't keep searching for ever...what might seem different is probably the same from another perspective. .
Now you have some missing parts...search for the missing parts...
Then you read and understand the code and finally you modify it to cover your needs.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Thanks for the guidance John, I appreciate it
Distinguished Expert 2017

Commented:
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 TsioumprisSoftware & Systems Engineer

Commented:
@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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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 )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Distinguished Expert 2017

Commented:
@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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial