lktx said:
Philip:
Thank you so much.
I must be doing this backward. I was filling the DB from the form and
having the form calculate my subtotals and totals for me. I was hoping that
the calculated totals from the form could show up in the DB.
I don't really have to worry about changes in information - and if there was
a change - say a price changes, I'd want it to reflect in the DB, the form
and the query.
If I understand, you are saying to fill the DB? (In which case, I'll have
to manually calculate everything which is what I was trying to avoid.)
Sorry - I'm probably being dense.
Philip Herlihy said:
lktx said:
I have created a form and have used the following to calculate my TOTAL:
=Nz([TOTAL PRICE1])+Nz([TOTAL PRICE2])+Nz([TOTAL PRICE3])+Nz([TOTAL
PRICE4])+Nz([TOTAL PRICE5])+Nz([TOTAL PRICE6])+Nz([TOTAL PRICE7])
It's working fine, however, because it is an equation, the result does not
show up in the database. Since I'm using the database for a query that
involves the field TOTAL, I need a real number to show up. HOW!?
Thanks
I think you've half got the right idea! You're calculating this Total
quantity on the fly from information in your database - this is fine.
However, it's a bad idea to try to store the result. What if one of the
components is changed? Your stored values will be inconsistent. If you
can calculate it in a form, then you can calculate it in a query
(although you'd need to include the 'value if null' argument: the zero
shown below).
Put in a cell in the query builder:
MyTotal:Nz([Total Price1],0) + (etc...)
I've avoided naming the expression Total, as it may (haven't tested) be
a reserved word which might cause problems. Try MyTotal first anyway!
HTH
Phil, London
No, you're not being dense, and I should have focused more on the use of
your form for data entry. However, the principle holds true - if you
can derive a quantity from data already held, then you needn't, indeed
shouldn't store the derived value.
I'm not an expert in the way some contributors here undoubtedly are, but
this is how I'd approach it. I'd have a form for data entry which
allowed me to input the raw information. (From your description these
are already totals?) Once the data is safely in table(s) then you can
write a query which can do the calculations. When your data is refined
into multiple tables, which suits many situations, Access's various
wizards can make surprisingly good guesses about the grouping and
summarising you might want, and will ask you relevant questions. In a
single table you have to do it yourself but it's not that hard if you're
prepared to do a little reading. If you use "grouping" in queries you
can use the "aggregate" functions of Sum, Count, Average, and so on.
This is good for producing results from Columns. If you need to combine
"horizontally" (i.e. using more than one field) then you need to use
Expressions in the query. A very simple expression might be:
Combined:=[ThisField]+[ThatField]
.... which will give you an extra column output from your query called
Combined. The Expression Builder (Google for articles or videos) is a
great way to get good at this.
Then, you can use a suitable query as a basis for a form designed to
present the information as you'd like it, or for a report suitable for
printing. Reports have powerful grouping and summarising facilties. The
wizards are a great help getting started.
These might help:
http://office.microsoft.com/en-us/training/CR061829401033.aspx
Phil
