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

Windows 10 Access 2016
Join Date
Oct 2018
Posts
72

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
434
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
5,514
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.

Windows 10 Access 2016
Join Date
Oct 2018
Posts
72
Micron,

Part of why I am using is excel is a need to full data from a field in one table (to be used in my calculations) where a second field most closely matches a value from another table.

It's difficult for me to put into plain English. Say I have two tables, tables A and B. They store somewhat different but related data. Lets says say table A has values that I need to use to do calculations in table B.. but since table A hosts several data points I need to use a reference point in table A to know which record to use.

Table A data:
Distance;direction;speed.
0;180;5
25;170;10
52;160;20

Table B Data
Distance;Direction (found from from table A);Speed (found from table A) ;OtherCalculation
10;180;5
12;180;5
30;170;10
40;160;20
80;160;20

I hope that gives a representation. Table B is copying the distance and speed from table A WHERE the Distance in table b most closely matches the distance in table A. I use an array in excel to do this but don't know how to do it in access.

From there I use a series of cells (in excel) to do a five step calculation which involves converting to radians and back.

Thanks

5. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
Posts
5,514
Table B is copying the distance and speed from table A
You mean Direction and Speed?
WHERE the Distance in table b most closely matches the distance in table A
This would likely require a subquery. However, if by closest you mean either below or above a value, then I've never done that. Might require 2 subquery fields - 1 for a value less than, 1 for more than. Can't really elaborate much on the subquery idea because the data and outcome is too small as is the definition of "closest" not clear. Note that the main query would probably have to be sorted by tableA.Distance; either DESC or not, depending if getting the Max or Min of distance.

In summary, your original supposition is that there might be an easier way over what you're already doing. Doesn't sound like it. I would advise you to consider what the drawbacks could be should values that feed the calculation would change. If never, then not much to go wrong, I guess. If possible, then a mechanism for discovering the change and updating the calculation is needed.

Windows 10 Access 2016
Join Date
Oct 2018
Posts
72
Yes, I think I will need several queries. The value could be higher or lower which of course complicates things. I wonder if any part of my excel expressions can help.

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