Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Microsoft Access - - - Adding calculated field?
I have a form that is based on a table. Each page of the form shows one specific customer's data. I would like to include a field on this form that automatically updates when I key in data - trying to create a "check and challenge" to total sales numbers so I can make sure the calculated total of the individual numbers match the total number keyed in (to catch any data input errors). I can create a field, but it is calculating all the numbers in the table, not just the one specific customer file. I tried a subform, but can't get that to work - what am I doing wrong, or is this not something that can be done?
One more thing - I don't want all the fields calculated, just a few of them related to sales.
I tried putting the calculated field in the form footer - it calculates all records. I tried puting the calculated field in the page footer - it does not show up at all.
I've never worked with dsum but could not get it to work.
I'm trying to show a field that totals
food ppd
supply ppd
labor ppd
fringe ppd
and show a total for these 4 numbers, but only for the diagnostic number visible on the form. the form shows one customer (diagnostic number) per screen. The four fields to total are numeric fields, the diagnostic number is a text field.
Yes, the only primary key is the diagnostic number field.
when I tried to create the query, it would prompt for a diagnostic number before it would run. If I put in a number, it would run that number, but would not carry over to the form. If I did not key in a number, it would not pull anything. (I'm working in Access 2003, if that makes a difference).
2 Answers
- LyreceusLv 41 decade agoFavorite Answer
Create a new query with the correct data source table or query.
Add the key field to the query grid. In its 'criteria,' put a reference to your key field control on your form. It will look like this:
[Forms]![frmYourForm]![txtYourKey]
In the second column of the query grid, enter something like this on the 'Field:' line to calculate the sum in the query:
MySum: [Food]+[Supply]+[Labor]+[Fringe]
'MySum' is the name of the new calculated field. It will be the field on which you use the Dsum function on your form. You can name this whatever you want. The others are your four fields to sum.
My test SQL with my tblFoodSupply table and frmFoodSupply form is this:
SELECT tblFoodSupply.ID,
[Food]+[Supply]+[Labor]+[Fringe] AS MySum
FROM tblFoodSupply
WHERE (((tblFoodSupply.ID)=
[forms]![frmFoodSupplyDsum]![ID]));
Save the query.
------------
On your form, the data control source of your summary field will be this:
=DSum("MySum","qryYourQueryName")
or
=DSum("[MySum]","qryYourQueryName")
(both seem to work fine)
As you move through the records, the sum of the four fields will show in your summary field.
************************
If you are doing data entry on this form, you will want the sum control to recalculate when any of the numbers are changed.
To do that, put these two lines in the AfterUpdate event of each of the controls used in the calculation.
DoCmd.RunCommand acCmdSaveRecord
Me.txtSumField.Requery
==================================
If you want to test the query, you must have the form open on a record. Then, it will not ask you to input the criteria. It sounds like it's working normally. Try the rest of the steps.
