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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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.
Just Use Excel Automation....load up your Excel spreadsheet...read the values cell by cell...compare them to Access and do the necessary updates.
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.
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.
Thanks for the answers so far experts.
John, I saw you recommend Excel automation often. What is it? &where can I find it?
John, I saw you recommend Excel automation often. What is it? &where can I find it?
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.
That's why I ask for help.
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/
Start from here for start: https://www.devhut.net/2016/07/07/vba-automating-excel/
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?
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?
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?
Training is always the same for a beginner.
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...usu
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.
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...usu
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.
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.
- 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.
@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
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.
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.
@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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial