Creating a sum field in access

[David Guice]

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

 

[fredg]

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Access is different from Excel.
In Access there is no need to create a calculated field to store this
total amount, nor to even store it at all.
All you need do is store the AmountPaid data.
Then when you need to see the total value, compute it on a Form or in
a Report, using an UNBOUND text control:

=DSum("[AmtPaid]","TableName","[CustomerID] = " & Me.[CustomerID])

The above assumes the CustomerID is a Number datatype field.

See VBA help on DSum
as well as
Restrict data to a subset of records
for help on the syntax needed for fields of various datatypes.

 

[John W. Vinson]

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Totals Query or in the control source of a Form or a
Report textbox. If you're using your Table for data viewing or data entry...
don't; that's not what tables are for, you need to use a form instead.

 

[David Guice]

Fred

Think we are almost there. When I use this line
=DSum("[Amount]","Receipts","[ID] = " & Me.[ID])
I get a #Name? error. If I remove any reference to the id it then shows the
sum for all the payments. I think I may need to reference the table that id
is in but dont know for sure. any thoughts?

TIA

David

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Access is different from Excel.
In Access there is no need to create a calculated field to store this
total amount, nor to even store it at all.
All you need do is store the AmountPaid data.
Then when you need to see the total value, compute it on a Form or in
a Report, using an UNBOUND text control:

=DSum("[AmtPaid]","TableName","[CustomerID] = " & Me.[CustomerID])

The above assumes the CustomerID is a Number datatype field.

See VBA help on DSum
as well as
Restrict data to a subset of records
for help on the syntax needed for fields of various datatypes.

 

[fredg]

Fred

Think we are almost there. When I use this line
=DSum("[Amount]","Receipts","[ID] = " & Me.[ID])
I get a #Name? error. If I remove any reference to the id it then shows the
sum for all the payments. I think I may need to reference the table that id
is in but dont know for sure. any thoughts?

TIA

David

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Access is different from Excel.
In Access there is no need to create a calculated field to store this
total amount, nor to even store it at all.
All you need do is store the AmountPaid data.
Then when you need to see the total value, compute it on a Form or in
a Report, using an UNBOUND text control:

=DSum("[AmtPaid]","TableName","[CustomerID] = " & Me.[CustomerID])

The above assumes the CustomerID is a Number datatype field.

See VBA help on DSum
as well as
Restrict data to a subset of records
for help on the syntax needed for fields of various datatypes.

My goof.
In an Access control's control source you cannot use the Me. keyword
(you can in VBA code). Access does not recognize Me.[ID].
Try...

=DSum("[Amount]","Receipts","[ID] = " & [ID])

This should work if the criteria field is named [ID]. If it isn't,
change it to whatever the actual RecordID field is named.

 

[David Guice]

Fred,

That did it!
Thanks for the assistance.

David

Fred

Think we are almost there. When I use this line
=DSum("[Amount]","Receipts","[ID] = " & Me.[ID])
I get a #Name? error. If I remove any reference to the id it then shows the
sum for all the payments. I think I may need to reference the table that id
is in but dont know for sure. any thoughts?

TIA

David

On Fri, 9 May 2008 12:59:02 -0700, David Guice wrote:

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Access is different from Excel.
In Access there is no need to create a calculated field to store this
total amount, nor to even store it at all.
All you need do is store the AmountPaid data.
Then when you need to see the total value, compute it on a Form or in
a Report, using an UNBOUND text control:

=DSum("[AmtPaid]","TableName","[CustomerID] = " & Me.[CustomerID])

The above assumes the CustomerID is a Number datatype field.

See VBA help on DSum
as well as
Restrict data to a subset of records
for help on the syntax needed for fields of various datatypes.

My goof.
In an Access control's control source you cannot use the Me. keyword
(you can in VBA code). Access does not recognize Me.[ID].
Try...

=DSum("[Amount]","Receipts","[ID] = " & [ID])

This should work if the criteria field is named [ID]. If it isn't,
change it to whatever the actual RecordID field is named.

 

[David Guice]

Fred,

One last question. Will this work in a report as well? When I tryed it
asked for the id which then printed the total for that id number for all
records.

TIA
David

Fred

Think we are almost there. When I use this line
=DSum("[Amount]","Receipts","[ID] = " & Me.[ID])
I get a #Name? error. If I remove any reference to the id it then shows the
sum for all the payments. I think I may need to reference the table that id
is in but dont know for sure. any thoughts?

TIA

David

On Fri, 9 May 2008 12:59:02 -0700, David Guice wrote:

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Access is different from Excel.
In Access there is no need to create a calculated field to store this
total amount, nor to even store it at all.
All you need do is store the AmountPaid data.
Then when you need to see the total value, compute it on a Form or in
a Report, using an UNBOUND text control:

=DSum("[AmtPaid]","TableName","[CustomerID] = " & Me.[CustomerID])

The above assumes the CustomerID is a Number datatype field.

See VBA help on DSum
as well as
Restrict data to a subset of records
for help on the syntax needed for fields of various datatypes.

My goof.
In an Access control's control source you cannot use the Me. keyword
(you can in VBA code). Access does not recognize Me.[ID].
Try...

=DSum("[Amount]","Receipts","[ID] = " & [ID])

This should work if the criteria field is named [ID]. If it isn't,
change it to whatever the actual RecordID field is named.

 

[fredg]

Fred,

One last question. Will this work in a report as well? When I tryed it
asked for the id which then printed the total for that id number for all
records.

TIA
David

Fred

Think we are almost there. When I use this line
=DSum("[Amount]","Receipts","[ID] = " & Me.[ID])
I get a #Name? error. If I remove any reference to the id it then shows the
sum for all the payments. I think I may need to reference the table that id
is in but dont know for sure. any thoughts?

TIA

David

:

On Fri, 9 May 2008 12:59:02 -0700, David Guice wrote:

I am trying to create a sum field in access that will sum payments made by a
customer. the current table has the customer id, amount paid,
date,comments,and the sum field. my problem is I can not figure out how to
add the amount to the sum for just one customer. ie if customer 1 makes a
payment I want the sum field in that record to reflect a sum of all the
payments customer 1 has made. Any help would be appriciated.

Access is different from Excel.
In Access there is no need to create a calculated field to store this
total amount, nor to even store it at all.
All you need do is store the AmountPaid data.
Then when you need to see the total value, compute it on a Form or in
a Report, using an UNBOUND text control:

=DSum("[AmtPaid]","TableName","[CustomerID] = " & Me.[CustomerID])

The above assumes the CustomerID is a Number datatype field.

See VBA help on DSum
as well as
Restrict data to a subset of records
for help on the syntax needed for fields of various datatypes.

My goof.
In an Access control's control source you cannot use the Me. keyword
(you can in VBA code). Access does not recognize Me.[ID].
Try...

=DSum("[Amount]","Receipts","[ID] = " & [ID])

This should work if the criteria field is named [ID]. If it isn't,
change it to whatever the actual RecordID field is named.

DSum operates at the table level.
If you wish to sum only those items in your report, which may not
contain all of the records in the table, you cannot use any of the
Domain Aggregate functions, i.e. DLookUp, DMin, DMax, etc.

To sum values that appear just in your report you would use unbound
controls:
=Sum([Amount]) to sum the [Amount] field of all records in the report,
or to Sum the field for the customer whose ID is 123.....
=Sum(IIf([ID] = 123,[Amount],0))
to sum the [Amount] field for just records whose ID is 123.
Place the control in any report section except the Page Header or Page
Footer.

Look uo DSum, DLookUp, as well as Sum, in VBA help.
Also look up the RunningSum property which can come in handy when
summing values, as you can sum values Over All or Over Group, in your
report.