Sum Record per Record

A

alex

Hello,

using Access '03...

I have a simple form based on table.

I have an unbound textbox and am trying to sum 3 other bound textboxes
per record.

The table looks like this:

TimeID CaseHours XYZHours OtherHours
20081215 5 1 3
20081216 1 12 8

I'd like to sum the hours fields per TimeID...e.g, 9 for the first
record. When I scroll to the second record the unbound textbox
(TotalHrs) should read 21.

I've researched DSum, but it doesn't appear to work. I also messed
around with the form header and footer to no avail.

Any ideas?
This seems like it should be pretty simple.
alex
 
A

alex

Hello,

using Access '03...

I have a simple form based on table.

I have an unbound textbox and am trying to sum 3 other bound textboxes
per record.

The table looks like this:

TimeID        CaseHours        XYZHours       OtherHours
20081215          5                     1                      3
20081216          1                     12                    8

I'd like to sum the hours fields per TimeID...e.g, 9 for the first
record.  When I scroll to the second record the unbound textbox
(TotalHrs) should read 21.

I've researched DSum, but it doesn't appear to work.  I also messed
around with the form header and footer to no avail.

Any ideas?
This seems like it should be pretty simple.
alex

I should have mentioned that there's a UserID within the table as
well! I am basically looking to sum an individual record (row).
 
K

Ken Sheridan

Alex:

Set the ControlSource property of the unbound text box to:

=Nz([CaseHours],0)+Nz([XYZHours],0)+Nz([OtherHours],0)

The Nz function is called here to return a zero if the column in the current
row is Null. Otherwise, if any of the three columns were Null the result of
the expression would be Null regardless of the values of the other columns.
This is because in arithmetical expressions Null 'propagates'.

Ken Sheridan
Stafford, England
 
A

alex

Alex:

Set the ControlSource property of the unbound text box to:

=Nz([CaseHours],0)+Nz([XYZHours],0)+Nz([OtherHours],0)

The Nz function is called here to return a zero if the column in the current
row is Null.  Otherwise, if any of the three columns were Null the result of
the expression would be Null regardless of the values of the other columns.  
This is because in arithmetical expressions Null 'propagates'.

Ken Sheridan
Stafford, England



I should have mentioned that there's a UserID within the table as
well!  I am basically looking to sum an individual record (row).- Hide quoted text -

- Show quoted text -

Hi Ken,

Thanks for your help...I needed the nz function. I also got the DSum
function to work and have posted the code below:

=DSum("nz([CaseHours],0)+nz([XYZHours],0)+nz([OtherHours],
0),"[tblBusinessTracking]","[When] = [forms]![sfrmBusinessTracking]!
[When] And [UserID] = [forms]![sfrmBusinessTracking]![UserID]")

When and UserID combine to make dual primary keys (because I have
multiple users entering hours on the same day; however, no user will
add two records on the same day!)

A problem, however. The code above works perfect on my form. When I
combine it to another form (as a subform) I get a #Error message in
the textbox. Any thoughts.

alex
 
K

Ken Sheridan

Alex:

Calling the DSum function is very inefficient here. Domain functions are
notoriously inefficient per se. Also the DSum function designed for summing
data from a set of rows, which in your case is a set of 1 of course, so you
could equally well have used the DLookup function. But stick with the simple
arithmetic.

The reason your expression doesn't work in a subform is that a subform is
not included in the database's Forms collection, which only includes open
main forms. To reference a subform you do so by the Form property of the
parent form's subform control, which is the control in the parent form which
houses the subform. If you want to show the sum of the subform's currently
selected row in the subform just use the simple arithmetic. If you want to
show it in the parent form do the same, but make the control in the subform
hidden and reference it with a control in the parent form. Take a look at
the Orders form in the sample Northwind database which comes with Access;
you'll se that it includes a Subtotal control which references a control in
the order details subform with:

=[Orders Subform].Form!OrderSubtotal

Ken Sheridan
Stafford, England
 
A

alex

Alex:

Calling the DSum function is very inefficient here.  Domain functions are
notoriously inefficient per se.  Also the DSum function designed for summing
data from a set of rows, which in your case is a set of 1 of course, so you
could equally well have used the DLookup function.  But stick with the simple
arithmetic.

The reason your expression doesn't work in a subform is that a subform is
not included in the database's Forms collection, which only includes open
main forms.  To reference a subform you do so by the Form property of the
parent form's subform control, which is the control in the parent form which
houses the subform.  If you want to show the sum of the subform's currently
selected row in the subform just use the simple arithmetic.  If you want to
show it in the parent form do the same, but make the control in the subform
hidden and reference it with a control in the parent form.  Take a lookat
the Orders form in the sample Northwind database which comes with Access;
you'll se that it includes a Subtotal control which references a control in
the order details subform with:

=[Orders Subform].Form!OrderSubtotal

Ken Sheridan
Stafford, England




Thanks for your help...I needed the nz function.  I also got the DSum
function to work and have posted the code below:
=DSum("nz([CaseHours],0)+nz([XYZHours],0)+nz([OtherHours],
0),"[tblBusinessTracking]","[When] = [forms]![sfrmBusinessTracking]!
[When]  And [UserID] = [forms]![sfrmBusinessTracking]![UserID]")
When and UserID combine to make dual primary keys (because I have
multiple users entering hours on the same day; however, no user will
add two records on the same day!)
A problem, however.  The code above works perfect on my form.  WhenI
combine it to another form (as a subform) I get a #Error message in
the textbox.  Any thoughts.
alex- Hide quoted text -

- Show quoted text -

Hi Ken,

I understand exactly what you're saying expect probably the most
simple part "simple arithmetic"!

I tried summing my form with simple arithmetic but it gives me an
erroneous result (I've looked at the sample database and the sum
function on the subform returns the total COLUMN price of 1.2 mil)
I'm looking for the total per row and not per column. What's strange,
is that when this control is brought to the mainform, it sums per
record (which is what I want). I may have issues with my
relationships.

For example, the code you gave me above [=Nz([CaseHours],0)+Nz
([XYZHours],0)+Nz([OtherHours],0)] will return all hours. I only want
the hours for a particular day for a particular UserID (DSUM is the
only way i've been able to do it).

How can I use simple math to return the following on my SubForm or
MainForm:

UserID TimeID CaseHours XYZHours
OtherHours
001 20081215 5
1 3
002 20081215 1
12 8

First Record = 9 Total hours
Second Record = 21 Total hours
 
A

alex

Calling the DSum function is very inefficient here.  Domain functionsare
notoriously inefficient per se.  Also the DSum function designed for summing
data from a set of rows, which in your case is a set of 1 of course, soyou
could equally well have used the DLookup function.  But stick with the simple
arithmetic.
The reason your expression doesn't work in a subform is that a subform is
not included in the database's Forms collection, which only includes open
main forms.  To reference a subform you do so by the Form property ofthe
parent form's subform control, which is the control in the parent form which
houses the subform.  If you want to show the sum of the subform's currently
selected row in the subform just use the simple arithmetic.  If you want to
show it in the parent form do the same, but make the control in the subform
hidden and reference it with a control in the parent form.  Take a look at
the Orders form in the sample Northwind database which comes with Access;
you'll se that it includes a Subtotal control which references a control in
the order details subform with:
=[Orders Subform].Form!OrderSubtotal
Ken Sheridan
Stafford, England
Hi Ken,
Thanks for your help...I needed the nz function.  I also got the DSum
function to work and have posted the code below:
=DSum("nz([CaseHours],0)+nz([XYZHours],0)+nz([OtherHours],
0),"[tblBusinessTracking]","[When] = [forms]![sfrmBusinessTracking]!
[When]  And [UserID] = [forms]![sfrmBusinessTracking]![UserID]")
When and UserID combine to make dual primary keys (because I have
multiple users entering hours on the same day; however, no user will
add two records on the same day!)
A problem, however.  The code above works perfect on my form.  When I
combine it to another form (as a subform) I get a #Error message in
the textbox.  Any thoughts.
alex- Hide quoted text -
- Show quoted text -

Hi Ken,

I understand exactly what you're saying expect probably the most
simple part "simple arithmetic"!

I tried summing my form with simple arithmetic but it gives me an
erroneous result (I've looked at the sample database and the sum
function on the subform returns the total COLUMN price of 1.2 mil)
I'm looking for the total per row and not per column.  What's strange,
is that when this control is brought to the mainform, it sums per
record (which is what I want).  I may have issues with my
relationships.

For example, the code you gave me above [=Nz([CaseHours],0)+Nz
([XYZHours],0)+Nz([OtherHours],0)] will return all hours.  I only want
the hours for a particular day for a particular UserID (DSUM is the
only way i've been able to do it).

How can I use simple math to return the following on my SubForm or
MainForm:

UserID           TimeID        CaseHours        XYZHours
OtherHours
001               20081215          5
1                      3
002               20081215          1
12                    8

First Record = 9 Total hours
Second Record = 21 Total hours- Hide quoted text -

- Show quoted text -

I don't know why Ken, but it's sort of working now.

I've got my sum textbox on the subform (which sums all the records).
My textbox on the mainform is now summing per record!

I'll need to dig further, but I wanted to let you know so you didn't
have to craft a message telling me it should work!

alex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top