none
Total in form doesn't match RRS feed

  • Question

  • Hi,

    I use a subquery in a form something like this

    Select tableA.*, tableA.ID,(select sum(x) from tableB where tableB.ID=tableA.ID) AS SumX from tableA

    In the form footer i use a textfield with source object: =sum(Sumx)

    The result in the textfield doesn't match with the sum of the rows in the form

    I can use =dsum("x";"tableB";"ID=" & tableA.ID), but this is slow and the query is not so easy as shown.

    Anybodie who knows why?

    Monday, March 9, 2015 9:30 PM

Answers

  • Hi Lteu,

    Based on the description, the expression on the foot of form to get the sum of the specfic filed doesn't match the value which sum of the rows in the form.

    Based on my understanding the sum expression should return the sum of values containd in a specifited filed on a query.

    I am trying to reproduce the issue however failed. Here are my steps:
    1. Create tableA which has two fileds(ID, Field1) and add some records

    2. Create tableB which has three fileds(ID, X, Field1) and add some records

    3. Create a single form(formA) for tableA and change the record source like query below:

    Select tableA.*, tableA.ID,(select sum(x) from tableB where tableB.ID=tableA.ID) AS SumX from tableA

    4. Add a textbox control at the footer of formA and set the control source of this textbox as "=sum(sumx)"

    The value is expected as the sum of all rows. Did I misunderstood?

    Would you mind sharing with us a demo project to help us to understand this issue exactly? You can upload it via OneDrive and please don't contain private data in the demo.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 11, 2015 3:10 AM
    Moderator

All replies

  • Hi Lteu,

    Based on the description, the expression on the foot of form to get the sum of the specfic filed doesn't match the value which sum of the rows in the form.

    Based on my understanding the sum expression should return the sum of values containd in a specifited filed on a query.

    I am trying to reproduce the issue however failed. Here are my steps:
    1. Create tableA which has two fileds(ID, Field1) and add some records

    2. Create tableB which has three fileds(ID, X, Field1) and add some records

    3. Create a single form(formA) for tableA and change the record source like query below:

    Select tableA.*, tableA.ID,(select sum(x) from tableB where tableB.ID=tableA.ID) AS SumX from tableA

    4. Add a textbox control at the footer of formA and set the control source of this textbox as "=sum(sumx)"

    The value is expected as the sum of all rows. Did I misunderstood?

    Would you mind sharing with us a demo project to help us to understand this issue exactly? You can upload it via OneDrive and please don't contain private data in the demo.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 11, 2015 3:10 AM
    Moderator
  • Although Fei is suggesting a workaround, that does not explain why your solution does not work, while it works in MANY other applications. The suggestion to upload a sample program, stripped to the bare essentials, is a good one.


    -Tom. Microsoft Access MVP

    Wednesday, March 11, 2015 4:51 AM