Results 1 to 3 of 3

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

  1. #1
    JRodko is offline 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. #2
    kd2017 is offline 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. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,852
    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.
    So, like, why do you, like, start every sentence with, like, "so"?

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