Store Calculations

[Millisa Eubanks]

Ok I know everyone is going to say you should not store calculations, but I
have no choice!!!!!!!!!! I have the form ORDERFORM (recordsource ORDERS
table) with a subform ORDERFORMsubPROD (recordsource ORDERSPROD table).
Essently theses 2 forms togather allow me to place an order for multiple
products and store them in the ORDERS (key: ORDERID) and ORDERSPROD (foregin
key: ORDERID) tables. The ORDERFORM includes information such as an ORDERDT
and the ORDERFORMsubPROD includes a sum of cost the products ordered which is
shown in ORDERFORM with a boundcontrol = to the ORDERFORMsubPROD control that
does the actual calculations.

Next I need to be able to pull specific informtaion from the ORDERS table
and group it togather by the SALESMANID. To this end I have a form SALESMAN
with a subform SALESMANsubORDERS that displayes the ORDERID, ORDERDT, and the
calculated sum of the products ordered on that ORDERID based on the
SALESMANID of the order.

So far everything stated above is correct and should not require that I
store calculations. My problem comes in when I am trying to make a form that
will filter this information based on user supplied criteria (my user does
not want to have to use the filer buttions supplied by Access). I'm using
DoCmd.OpenForm and setting my WHERE criteria based on a bunch of If .. Then
statements that check the various filter text/combo boxes to determine if
they are populated and then admend the WHERE statement accordingly. At least
that is how it works on a very similar form that filters ORDERFORM. I have
discovered that because the subform, SALESMANsubORDERS, is based on a query
that includes calculations the WHERE statement needs to be phrased as HAVING.
I can not figure out how to get Access to comply with this so all I can think
of is to store the calculated values (and be able to allow them to be updated
later) then pull them directly from the ORDERS table.

Anyone who can write precise/generic code and most importantly tell me where
(what event) that code should be placed on please let me know. This is the
first time I have run into this problem and it is really my last problem
before completing a DB application that needs to be completed by Wed
1/19/2005. Thanks in advance for any advise/help I receive.

 

[Allen Browne]

Hi Millisa

You want to create a query that includes a WHERE clause on an aggregated
value? One way around this would be to leave the OrdersProd table out of the
query, and use a subquery in the WHERE clause to get the total.

This example pulls up all orders over $1000:
SELECT Orders.* FROM Orders
WHERE (SELECT Sum([Amount]) FROM OrdersProd
WHERE OrdersProd.OrderID = Orders.OrderID ) > 1000

Basic explanation of subqueries:
http://support.microsoft.com/?id=209066

If you really must store the total, and all edits/entries/deletes are done
through forms (not directly on the tables or through action queries), you
can use the form events to write the total. Use the AfterUpdate and
AfterDelConfirm events of the main form to DSum() the total directly from
the subform's table (in case the subform is filtered or not yet updated).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Eric Schittlipz]

Hi Millisa

You want to create a query that includes a WHERE clause on an aggregated
value? One way around this would be to leave the OrdersProd table out of
the query, and use a subquery in the WHERE clause to get the total.

This example pulls up all orders over $1000:
SELECT Orders.* FROM Orders
WHERE (SELECT Sum([Amount]) FROM OrdersProd
WHERE OrdersProd.OrderID = Orders.OrderID ) > 1000

Basic explanation of subqueries:
http://support.microsoft.com/?id=209066

If you really must store the total, and all edits/entries/deletes are done
through forms (not directly on the tables or through action queries), you
can use the form events to write the total. Use the AfterUpdate and
AfterDelConfirm events of the main form to DSum() the total directly from
the subform's table (in case the subform is filtered or not yet updated).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Ok I know everyone is going to say you should not store calculations, but
I
have no choice!!!!!!!!!! I have the form ORDERFORM (recordsource ORDERS
table) with a subform ORDERFORMsubPROD (recordsource ORDERSPROD table).
Essently theses 2 forms togather allow me to place an order for multiple
products and store them in the ORDERS (key: ORDERID) and ORDERSPROD
(foregin
key: ORDERID) tables. The ORDERFORM includes information such as an
ORDERDT
and the ORDERFORMsubPROD includes a sum of cost the products ordered
which is
shown in ORDERFORM with a boundcontrol = to the ORDERFORMsubPROD control
that
does the actual calculations.

Next I need to be able to pull specific informtaion from the ORDERS table
and group it togather by the SALESMANID. To this end I have a form
SALESMAN
with a subform SALESMANsubORDERS that displayes the ORDERID, ORDERDT, and
the
calculated sum of the products ordered on that ORDERID based on the
SALESMANID of the order.

So far everything stated above is correct and should not require that I
store calculations. My problem comes in when I am trying to make a form
that
will filter this information based on user supplied criteria (my user
does
not want to have to use the filer buttions supplied by Access). I'm using
DoCmd.OpenForm and setting my WHERE criteria based on a bunch of If ..
Then
statements that check the various filter text/combo boxes to determine if
they are populated and then admend the WHERE statement accordingly. At
least
that is how it works on a very similar form that filters ORDERFORM. I
have
discovered that because the subform, SALESMANsubORDERS, is based on a
query
that includes calculations the WHERE statement needs to be phrased as
HAVING.
I can not figure out how to get Access to comply with this so all I can
think
of is to store the calculated values (and be able to allow them to be
updated
later) then pull them directly from the ORDERS table.

Anyone who can write precise/generic code and most importantly tell me
where
(what event) that code should be placed on please let me know. This is
the
first time I have run into this problem and it is really my last problem
before completing a DB application that needs to be completed by Wed
1/19/2005. Thanks in advance for any advise/help I receive.

The OP has had this all explained a few threads down but got into a sulk and
created a new thread with a copy of the question. Perhaps if it is posted
often enough, someone will write the code for him/her.