Results 1 to 6 of 6

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

  1. #1
    JRodko is offline Advanced Beginner
    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. #2
    kd2017 is offline 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. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    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.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  4. #4
    JRodko is offline Advanced Beginner
    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. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    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.

  6. #6
    JRodko is offline Advanced Beginner
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 10-30-2018, 11:40 AM
  2. Excel refresh blocks Access data entry
    By simaonobrega in forum Import/Export Data
    Replies: 4
    Last Post: 06-30-2017, 10:01 AM
  3. Replies: 4
    Last Post: 04-15-2015, 09:15 AM
  4. Replies: 4
    Last Post: 12-16-2014, 02:31 AM
  5. Data update Access to Excel
    By b82726272 in forum Programming
    Replies: 3
    Last Post: 06-19-2014, 08:06 AM

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