Irishimp23 said:
I have a query which contains the fields "total amt" "increase" and
"decrease". If the total amount increases or decreases 10% ONLY... I need to
note it in the indicated fields. I need help with devising a formula to show
if there is a 10 % increase or decrease using the total amount field as a
factor. If the percentage is higher or lower than 10% I need the two fields
to be null. I hope I explained this correctly and I appreciate any assistance.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
This begs the question "increase/decrease compared to what?"
Anyway the usual answer is to write a query that calculates this
information - not to store the increase/decrease (the change) in the
table. Any time you need to know the change you'd open the query (this
query works like a View in "real" RDBMS - Views can be considered like
tables, except they don't save the data, they just give you a "view" of
the data in a specific configuration). You could even create a form
based on this View - so instead of opening the query you'd open the
form.
My guess is that you're wanting to know the change over time (the usual)
so the query would be something like this (if you insist you can use
this query as the basis of an UPDATE command to store the values in the
table. I'd recommend storing just one value in a column named Change:
an increase would be a positive number and a decrease would be a
negative number).
SELECT ID_column, date_column, <other columns>, [tot amount],
(SELECT [tot amount]
FROM table_name
WHERE ID_column = T.ID_column
AND date_column = (SELECT MAX(date_column)
FROM table_name
WHERE ID_column = T.ID_column
AND date_column < T.date_column)) As
previous_amount,
100 * (([tot amount]-previous_amount)/previous_amount) As Change
FROM table_name As T
WHERE <criteria>
HAVING ABS(100*(([tot amount]-previous_amount)/previous_amount) = 10
The subqueries in the SELECT clause calculate the change between the
previous date's tot amount and the current date's tot amount. If there
is more that determines the previous from the current you'll have to
include those columns in the criteria clauses of both subqueries.
The HAVING clause will filter out any records that don't have a 10 pct
change. (Yes, a HAVING clause can be used on queries that do not have a
GROUP BY clause.)0
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRAN7p4echKqOuFEgEQIbVgCfbrb1Nkt10onSi+nPp1iz04JQvmAAn0Cd
pHNGdl66fcVqjfQeSnBAkk0y
=OLcA
-----END PGP SIGNATURE-----
