How do I use the Dsum() funntion in a TABLE(e.g Quantity field) and later on
subtract from another Dsum() funtion in another TABLE(Quantity field)
Ahh, not the answer you expected ... you don't. Access is NOT a
spreadsheet, it can't do calculations. It's not meant to.
--------------- About Calculations in Access ---------------
Access tables are just dumb row/column intersections that hold data.
This is not a bad thing, it's good, very good. If you changed a total
figure in a table, it has no way of being updated. There's no
re-calculation. There is also no need to hard-store calculated data
because it is changing all of the time and you need to know "now" what
the total is. For example a calculation for Age is out of date 24hr
after entering it. You want the Forms and Reports to show the actual
position.
With Access, you base all of your Forms and Reports on Queries.
There's really not many exceptions to this, it's a really good habit
to get into because it will make so many things so much easier. So
all your calculations are done in either the Query, Form or Report and
never in the table.
Your calculations can be done in 3 places
1) in the Query that the Form or Report is based on .. done like this
- in a new column in the design grid use this syntax (without
the quotes)
"calTotCost: [NumField1]+[NumField2]"
You have just created a new Object called "calTotCost" which can be
used in Forms and Reports just as if it's a Table's field. The
prefix "cal" tells you always that this is a calculated field and not
one from a Table.
2 & 3) in a Form or Report,
- you add a new TextBox (off the toolbar), they're called
"Controls" in the Report or on the Form.
- Open the Properties pane
- click on the new Control you added
- click on the Data tab
- in the ControlSource property line put
=[NumField1]+[NumField2]
- click on the Other tab
- Change the Name to calTotCost
That has done the same thing as the Query. However you have done it in
just that one location, if you want that information on another Form
or in the Report, you have to go through the whole process again ...
which is why I use Queries for calculated controlsources. In the
Query you do it once and then because your Form or Report is based on
it, you just drag and drop it onto your Form or Report design view.
The continuing process is easy now ... calTotOwed:
[calTotCost]-[Payments]
For additional information and examples ...
http://members.iinet.net.au/~allenbrowne/AppInventory.html
Cheers,
Brett
