If Then Else?

[Alberta Rose]

In the detail portion of my report, I have a field that is Est As % of
Labour. The calculation I need is dependant on if a cost code and a cost
type is this, then perform this calculation. Example:

If [costcode]="020110" and [costype]="05320, take the EstimatedCost (of
this costcode/costtype) and divide by EstimatedCost of costcode=013210 and
costtype=05320.

another:

If [costcode]="036110" and [costype]="05511, take the EstimatedCost (of
this costcode/costtype) and divide by EstimatedCost of costcode=031101 and
costtype=05110

Any suggestions?

 

[vanderghast]

have a query which returns the cost, given a costcode and a costtype, for
each code and type, something like:

SELECT costcode, costtype, SUM( whatever) AS totalCost
FROM somewhere
GROUP BY costcode, costtype

-- saved as costByCodeType (That may be an existing table, too)



Next, have a table (or two) which identifies which code is associated to
which, such as

OriginalAndAssociate ' table
OriginalCode, OriginalType, AssociatedCode, AssociatedType ' fields
020110 05320 013210 05320
036110 05511 031101 05110
.... 'data



then, in a new query, bring that table and the query costByCodeType TWICE.
Join OriginalCode and OriginalType to costcode and costtype of one
'reference' and join AssociatedCode, AssociatedType to costcode and
costType of the second 'reference' of the query. In the grid, bring
OrignalCode, OriginalType, and the totalCost of each reference to the query.
That makes four fields in the grid. It seems you need to divide the third by
the fourth, as computed expression.


Note that the table "OriginalAndAssociate" is used to SAY to SQL which is
associated to which. The 'logic' is then handled by the data in a table,
rather than having a formula filled with magic constants coming out of
nowhere and hard to maintain (you have to modify the formula, rather than
data in a table, to handle new cases).



Vanderghast, Access MVP

 

[Alberta Rose]

Ok, need a little more help.

I have created the table as you described and listed all the original and
associated cost codes and cost types and named it tblOriginalAndAssociate.

In the query, I have added my main table (tblContractHoursCosts) and brought
in the fields CostCode and CostType. I have selected "Group By" in the
"Total:" line. iI have saved it as qrycostByCodeType. How do I put the Sum
as total cost from in?? Do I put the Sum as an expression?

have a query which returns the cost, given a costcode and a costtype, for
each code and type, something like:

SELECT costcode, costtype, SUM( whatever) AS totalCost
FROM somewhere
GROUP BY costcode, costtype

-- saved as costByCodeType (That may be an existing table, too)



Next, have a table (or two) which identifies which code is associated to
which, such as

OriginalAndAssociate ' table
OriginalCode, OriginalType, AssociatedCode, AssociatedType ' fields
020110 05320 013210 05320
036110 05511 031101 05110
... 'data



then, in a new query, bring that table and the query costByCodeType TWICE.
Join OriginalCode and OriginalType to costcode and costtype of one
'reference' and join AssociatedCode, AssociatedType to costcode and
costType of the second 'reference' of the query. In the grid, bring
OrignalCode, OriginalType, and the totalCost of each reference to the query.
That makes four fields in the grid. It seems you need to divide the third by
the fourth, as computed expression.


Note that the table "OriginalAndAssociate" is used to SAY to SQL which is
associated to which. The 'logic' is then handled by the data in a table,
rather than having a formula filled with magic constants coming out of
nowhere and hard to maintain (you have to modify the formula, rather than
data in a table, to handle new cases).



Vanderghast, Access MVP

In the detail portion of my report, I have a field that is Est As % of
Labour. The calculation I need is dependant on if a cost code and a cost
type is this, then perform this calculation. Example:

If [costcode]="020110" and [costype]="05320, take the EstimatedCost (of
this costcode/costtype) and divide by EstimatedCost of costcode=013210 and
costtype=05320.

another:

If [costcode]="036110" and [costype]="05511, take the EstimatedCost (of
this costcode/costtype) and divide by EstimatedCost of costcode=031101 and
costtype=05110

Any suggestions?

 

[vanderghast]

If you already have the table, or query, giving the totalCost per costcode
and costtype, the last query is NOT a total query. Only if you don't already
have the table (or query) giving the totalCost per costcode and costtype do
you need a total query like:

SELECT costcode, costtype, SUM( whatever) AS totalCost
FROM somewhere
GROUP BY costcode, costtype


Once you have that, the last query, again, is a standard query, as described
in my first reply.



Vanderghast, Access MVP

Ok, need a little more help.

I have created the table as you described and listed all the original and
associated cost codes and cost types and named it tblOriginalAndAssociate.

In the query, I have added my main table (tblContractHoursCosts) and
brought
in the fields CostCode and CostType. I have selected "Group By" in the
"Total:" line. iI have saved it as qrycostByCodeType. How do I put the
Sum
as total cost from in?? Do I put the Sum as an expression?

have a query which returns the cost, given a costcode and a costtype, for
each code and type, something like:

SELECT costcode, costtype, SUM( whatever) AS totalCost
FROM somewhere
GROUP BY costcode, costtype

-- saved as costByCodeType (That may be an existing table, too)



Next, have a table (or two) which identifies which code is associated to
which, such as

OriginalAndAssociate ' table
OriginalCode, OriginalType, AssociatedCode, AssociatedType ' fields
020110 05320 013210 05320
036110 05511 031101 05110
... 'data



then, in a new query, bring that table and the query costByCodeType
TWICE.
Join OriginalCode and OriginalType to costcode and costtype of one
'reference' and join AssociatedCode, AssociatedType to costcode and
costType of the second 'reference' of the query. In the grid, bring
OrignalCode, OriginalType, and the totalCost of each reference to the
query.
That makes four fields in the grid. It seems you need to divide the third
by
the fourth, as computed expression.


Note that the table "OriginalAndAssociate" is used to SAY to SQL which is
associated to which. The 'logic' is then handled by the data in a table,
rather than having a formula filled with magic constants coming out of
nowhere and hard to maintain (you have to modify the formula, rather than
data in a table, to handle new cases).



Vanderghast, Access MVP

In the detail portion of my report, I have a field that is Est As % of
Labour. The calculation I need is dependant on if a cost code and a
cost
type is this, then perform this calculation. Example:

If [costcode]="020110" and [costype]="05320, take the EstimatedCost
(of
this costcode/costtype) and divide by EstimatedCost of costcode=013210
and
costtype=05320.

another:

If [costcode]="036110" and [costype]="05511, take the EstimatedCost
(of
this costcode/costtype) and divide by EstimatedCost of costcode=031101
and
costtype=05110

Any suggestions?