1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2010 - Merge a query to Word 2010 data issues/errors

Discussion in 'Business Applications' started by rainbowparrot, Jan 15, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. rainbowparrot

    rainbowparrot Thread Starter

    Joined:
    Jan 15, 2016
    Messages:
    3
    Hi, I have set up a new Access 2010 database (saved as .accdb) (copying tables, forms and queries from my current one then deleting the old data and modifying a lot of fields). I have a query in with some simple calculations. All the calculations work perfectly in Access. When I merge this into Word 2010 (set up as a letter document) it doesn’t use/show the correct data.

    The calculated fields in the query are “number of people * price tea & coffee = cost tea & coffee” and “number of people * price buffet = cost buffet". I then use these costs in the same query to work out “VAT” (another calculated field adding up the calculated "cost tea & coffee" and calculated "cost buffet" as well as a number of other non-calculated fields and multiplying the total by 0.2) and “Total Cost” (a calculated field adding up various non-calculated fields, the calculated "cost tea & coffee", calculated "cost buffet" and the calculated VAT field).

    There are two errors in the data that ends up in the merged Word document:
    The "VAT" doesn’t add in the two calculated fields, it only uses the data from non-calculated fields in the query.
    Also the "Total Cost" doesn’t add in any of the calculated fields (teas & coffees, buffet and VAT), it only uses non-calculated fields from the query.

    It doesn’t ping up any error messages. Why is it changing the data between Access (where the query works perfectly) and Word? It does the same errors when I have experimented merging the data into Excel, when I set up a completely new Word document and also when I have set up a completely new query with the same calculations.

    It all works perfectly in the current database (also 2010 but started in a much older version, saved as .mdb) and my current Word document (again started in an old version, now Word 2010) linked to the current database.

    Any help gratefully received (please keep it simple!!) I can send screen shots if that helps.
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    The simplest solution I can think of would be to create tables with queries (with totals already calculated) and see if Word would then pull the correct amounts from the table(s).
     
  3. rainbowparrot

    rainbowparrot Thread Starter

    Joined:
    Jan 15, 2016
    Messages:
    3
    Thank you for the suggestion. Everywhere I have read about calculations in tables strongly recommend Not to do them.

    This does work though - I had tried calculating just the "price t&C" in the table then using this in the VAT calculation in the query with the same problem when it takes the data to Word. I have just tried doing the VAT calculation in the Table as well then still taking the data into the query to filter it & IT WORKS!

    What are the problems with calculations in tables? I didn't understand the talk about "normalisation"...! Other people have said it is not reliable - will it go wrong at a later date & get the figures wrong (which would upset audit!)?
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I'm not recommending that the calculations be done in a table, I've never done that. I do a cost allocation process where I build a table using calculation's on an input file, then update that table with some accounting data, then export a query to an excel for a pivot table for totals. Comes out to the penny every time.

    I have another one for mailing cost that uses percentages from one input applied to another input. This result usually has to be tweaked by a couple of pennies because of rounding. FYI Access uses bankers rounding or 'round to even' when breaking the 5 tie.

    What I recommend is turning your calculations query into a make table query, then use that table (or query of the table) as your output (never liked outputting numbers to word).
     
  5. rainbowparrot

    rainbowparrot Thread Starter

    Joined:
    Jan 15, 2016
    Messages:
    3
    Hi,
    Many thanks for taking the time to reply, unfortunately I don't understand!! I am very much a fairly basic user...

    I am going to start again & see where it is getting itself (or myself) in a muddle by checking each part as I go. Otherwise I will try the calculations in the table & keep a close eye on it.
     
  6. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I'm recommending doing your calculations in a Query(s) and make table or update a table with those calculation. Then take that tables results to word (since that seems to be your issue which I don't really understand). Like you've pointed out people are leery of doing the calculation there(in table). If you calculation are linear (i.e. all the numbers you need are in the same row) it will probably work. But then why are you Not doing the calculation on build?

    I think the 'normalization' reference may be about text fields. Like combining Name fields where some are present or not standard (Mister, Mr, Mr.)
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,827
    You should be able to create a second "output" query based on the one doing the calculations, in the second query the calculated values should be fixed.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1164245

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice