Results 1 to 4 of 4
  1. #1
    MrXYZ is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Aug 2012
    Posts
    5

    How do I show the sum of 2 table fields in a form?

    Hi,



    I have created a new DB to show current stock feeds. I have numerous tables, providing different data of stock within the company. In my form, I have the planned stock due in for each week and also the confirmed stock due in for each week. I have added another row beneath these fields, where I want to show the total of both fields together.

    Do I add coding to the properties of the new fields added? Or would I create an update query (as I have been advised this) and link it to my new fields?

    My tables are linked, I just cant figure out how to sum the 2 fields together and to get it to show in my form?

    Thanks,

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If these two tables are linked in a query, do the two fields you want to add appear on the same query record, or different query records?
    If they are on the same query record, simply create a calculated field right in your query to sum them, i.e.
    MyTotal: [Table1].[Field1] + [Table2].[Field1]

    If this does not help, it may be helpful if you can post a small data sample and show us exactly how you would like it to work.

  3. #3
    MrXYZ is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Aug 2012
    Posts
    5
    Thanks for your reply. This is what I have been trying to do. I created a whole new query so that they were both in the same query to be able to do what you have suggested, but I couldnt get it to work.

    I actually found another solution on this website.

    In the form, within the Properties of the field where I wanted the sum to show, I added the following code to the control source which has given me what I needed for now.

    =Nz([QryConfirmedOrders_35],0)+Nz([QryPlannedUnits_35],0)

    Thanks again for your help though

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In the form, within the Properties of the field where I wanted the sum to show, I added the following code to the control source which has given me what I needed for now.

    =Nz([QryConfirmedOrders_35],0)+Nz([QryPlannedUnits_35],0)
    You can put the same calculation right in the query itself, instead of on the form (assuming your are using the Query as the Source of the Form instead of a Table). I guess it is a matter of personal preference. I like to do all the calculations in the query, if possible. I just think that they are easier to maintain that way.

    You didn't mention that you might have NULL values in your data, or else I would have suggested the NZ function like I did for this person yesterday: https://www.accessforums.net/reports...tml#post134465).

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

Similar Threads

  1. Fields show differently in table versus form.
    By zero3ree in forum Access
    Replies: 1
    Last Post: 08-01-2012, 03:58 PM
  2. Replies: 2
    Last Post: 11-30-2011, 07:24 PM
  3. Replies: 1
    Last Post: 07-30-2011, 03:21 PM
  4. Show data not in table A using multiple fields
    By mikesmith01 in forum Access
    Replies: 3
    Last Post: 02-25-2011, 08:38 AM
  5. Replies: 0
    Last Post: 11-11-2008, 07:15 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
  •  
Other Forums: Microsoft Office Forums