# Data entry in Access, calculate in Excel, update data in Access.

1. Novice
Windows 10 Access 2016
Join Date
Oct 2018
Posts
22

## Data entry in Access, calculate in Excel, update data in Access.

Hi Folks,

I have a series of somewhat complex calculations and functions I've used in Excel that I've been trying to get to work in Access but I'm having little success so I've developed a workaround. My process is working however I am looking for guidance on streamlining it.

I suppose the nature of the calculations is not pertinent for this thread, but the take away is that i don't know how to do it in Access currently.

My current process:

In my excel workbook I have two sheets which use queries to pull the necessary data from two separate Access tables. (tblSurvey, tblStruct)
A third sheet in Excel calculates the fields that I need.
Those calculations are copied to a fourth worksheet in excel which is used as my linked table to Access.
In Access I have the linked table which I believe refreshes automatically from the properly formatted excel sheet.
I then use an update query to update the calculated values where needed into my original Access table. (tblStruct)

I hope this makes sense. I am thinking there must be an easier way but I have almost zero familiarity with querying between Excel and Access.

I also have to figure out how to do this with a criteria.

2. Well, I tried at least.
Windows 10 Access 2016
Join Date
Jul 2017
Posts
423
It might be helpful for you to post example data and calculations.

3. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
4,849
Most of the time, storing calculations is ill advised, for if one value changes you will likely not remember to force a recalc, and there's nothing in Access that will pick up the need.
One option is to replicate the math in Access if the calc isn't too onerous. A Combinations or Permutations would be doable, for example, but you could research whatever the calculation is and see if you can replicate it. The most difficult one I've ever done is the volume of liquid in a cylindrical horizontal tank, based on depth readings.

I do recall one instance of where I was able to use an Excel feature from Access as long as I had the necessary library referenced, plus made a connection to Excel. I can't recall what it was about because I was only playing around, but the point is, I was able to make use of an Excel function from Access.

Again, storing calculations is seldom a good idea. I suppose you could make it so that all calcs/recalcs are done in the worksheet and only link to that from Access. I wish I could say for certain that all you need to do is refer to the calculated cell without having to copy the result to another sheet, but I'm retired now and things are getting fuzzier.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Tech Forums: Microsoft Office Forums