Sum Up multiple fields' value in a form (1 Viewer)

ZaheerAliBaig

New member
Local time
Today, 03:02
Joined
Jan 19, 2020
Messages
1
Hi...
i have created a form in access. as below

Test Names Amount
Test1 100
Test2 200
Test3 50
Test4 120

When i select the test name the amount field autopopulates (by fetching data from another table)

i want the total of All 4 tests in the same form

Like Below

Total Amount 470 i.e:100+200+50+120=470

Please tell me how can i get the total Amount. its not working by Sum expression.:confused:

Thanks :)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:02
Joined
Jul 9, 2003
Messages
16,243
Hi...
i have created a form in access. as below

Test Names Amount
Test1 100
Test2 200
Test3 50
Test4 120

When i select the test name the amount field autopopulates (by fetching data from another table)

i want the total of All 4 tests in the same form

Like Below

Total Amount 470 i.e:100+200+50+120=470

Please tell me how can i get the total Amount. its not working by Sum expression.:confused:

Thanks :)

Add an extra field to your table to create a group, I added "TestGrp". Fill it with the same information, I used an integer "1". Then run the following query:-

Code:
SELECT tblTestNames.TestGrp, Sum(tblTestNames.[Amount]) AS Expr1
FROM tblTestNames
GROUP BY tblTestNames.TestGrp;
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:02
Joined
Jul 9, 2003
Messages
16,243
On a second look at your question what I thought was a table design is probably text boxes on a form? It that's the case, my solution won't work.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:02
Joined
May 21, 2018
Messages
8,463
its not working by Sum expression.
It should. Can you show us what you are doing wrong. What is the formula and where is it located?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:02
Joined
Feb 19, 2002
Messages
42,970
If the "tests" are separate fields on a form, your schema is not properly normalized. What if you have 5 tests? are you going to rebuild the form and table and queries and code? If the "tests" are properly normalized as rows in a separate table, then you can use a subform to display them and it won't matter how many there are. No code would ever have to change. In that case Uncle's suggestion would work correctly.

Even though you have a design problem, the answer to your question as written is to use the Nz() function to get past potential null values.

Me.MyTotal = Nz(Me.test1,0) + Nz(Me.test2,0), + Nz(Me.test3, 0) + Nz(Me.test4, 0)

A better option for your poor design would be to include a calculated value in the query that the form is bound to.

Select ..., Nz(test1,0) + Nz(test2,0), + Nz(test3, 0) + Nz(test4, 0) As MyTotal
From YourTable;

In a relational database, aggregate functions work on ROWS. They NEVER work on columns. So Sum(test) would sum the test column in the rows of a table. You might want to do some reading on normalization and converting spreadsheets to databases before continuing.
 

Users who are viewing this thread

Top Bottom