How can this be done?

L

Lori

Okay, I have two queries, one provides data based on original contract
budgets and values, the second provides adjusted contract values based on the
original budgets plus changes. How can I combine the data from these so that
it will provide the original values if nothing has been changed and the
changes if applicable?

To provide a little more info, What I'm trying to do is create a report that
will:
1. Add the original budget with any changes (if applicable) to come up with
a revised budget.
2. Add the original contract values, with any changes (if applicable) to
come up with a project final contract value.

My problem is my queries only seem to give me one or the other, either
original or only items with changes, but not both. WHAT AM I DOING WRONG????
 
M

Michel Walsh

SELECT original.contractID, original.cost + Nz(modified.additionalCost, 0)
AS revisedCost
FROM original LEFT JOIN modified ON original.contractID =
modified.contractID



assuming two tables, original and modified, each with a field contractID
that is used to cross reference the two tables.



Hoping it may help,
Vanderghast, Access MVP
 
L

Lori

Okay this isn't working. Maybe I need to explain a little better.

I have the contracts and change orders on one table and the details (whether
they are scope changes or not) on another. This includes two fields (1: is
the item in budget, yes/no and 2: COIssued, yes/no). What I need is a formula
that will look at these two yes/no fields and if InBudget = No and COIssued
=Yes, it should provided me with the "finalvalue" number from the same table.
Otherwise it should give me a 0.

Currently I'm using the following: Approved Revisions: IIf(([Cost
Items]![InBudget]=No And [Cost Items]![COIssued?]=Yes),([Cost
Items]![FinalValue]),(0))

I currently have one item that meets this criteria but I'm getting a 0 back
as an answer.

Also, I need the query to add: The table has a CostCode field and this field
should only be seen once but whenever I have a Change (which would be
calculated above) I'm getting multiple fields for each cost code.

so if a Change has been incorporated into the query it should show the
Original Budget, followed by the proper calculation for the formula above,
followed by a total of these two numbers ONCE for each CostCode. HELP!
I'm sure this is possible but HOW?
Lori A. Pong
 
M

Michel Walsh

Are your field Boolean or strings? If they are Boolean, you don't need to
compare them to logical values, since they are already logical values:

Approved Revisions: iif( (NOT InBudget) AND [ClosedIssue?], [Cost Items],
0 )


That computed expression will be evaluated for all the rows satisfying the
criteria of the query. It won't automatically make a sum over all these
records.


If you need that result into another computed expression, use it, on a
column at the right of its definition:


Total Cost: [Approved Revisions] + somethingElse



Hoping it may help,
Vanderghast, Access MVP


Lori said:
Okay this isn't working. Maybe I need to explain a little better.

I have the contracts and change orders on one table and the details
(whether
they are scope changes or not) on another. This includes two fields (1: is
the item in budget, yes/no and 2: COIssued, yes/no). What I need is a
formula
that will look at these two yes/no fields and if InBudget = No and
COIssued
=Yes, it should provided me with the "finalvalue" number from the same
table.
Otherwise it should give me a 0.

Currently I'm using the following: Approved Revisions: IIf(([Cost
Items]![InBudget]=No And [Cost Items]![COIssued?]=Yes),([Cost
Items]![FinalValue]),(0))

I currently have one item that meets this criteria but I'm getting a 0
back
as an answer.

Also, I need the query to add: The table has a CostCode field and this
field
should only be seen once but whenever I have a Change (which would be
calculated above) I'm getting multiple fields for each cost code.

so if a Change has been incorporated into the query it should show the
Original Budget, followed by the proper calculation for the formula above,
followed by a total of these two numbers ONCE for each CostCode. HELP!
I'm sure this is possible but HOW?
Lori A. Pong


Michel Walsh said:
SELECT original.contractID, original.cost + Nz(modified.additionalCost,
0)
AS revisedCost
FROM original LEFT JOIN modified ON original.contractID =
modified.contractID



assuming two tables, original and modified, each with a field contractID
that is used to cross reference the two tables.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Top