sum in a query

[Guest]

okay, I'm using Access 2003, and I'm trying to generate a calculation in a
query that will give me the totals I need.

I have a table that lists specific items by contractor, this table includes
the contract values that are sorted by cost code.

example: cost code 33400 has three separate line entries, the original
contract value, and two entries with changes to that contract. I need a
formula that will allow me to sort by the cost code and total the value of
the contract with the necessary changes so that only on line item per cost
code is the result.

 

[Allen Browne]

Use a totals query:

1. In query design view, depress the Totals button on the toolbar.
(Upper sigma icon.) Access adds a Total row to the design grid.

2. Accept Group By in the Total row under fields such as contractor and cost
code. Change it to Sum under the amount.

 

[Guest]

Okay, this didn't work, I'm thinking that there is too much going on in the
query so I am attempting to simplify it. Hopefully that will work, however,
I have two expressions that should work but don't.

I have 2 Yes/No fields, one indicates whether the change is part of the
budget, the second indicates if the item is closed. Now, if the item is in
budget in should not appear in the "Approved Revisions", it should only be
added to the To Be committed column (this part is working) however with the
following:

Approved Revisions: IIf(([InBudget]=1 And [COIssued?]=1),(0),([FinalValue]))

it is putting the value in both the Approved revision column AND in the TBC
column. Naturally it only needs to be in one spot.

The second expression is just the opposite:

TBC Scope Change: IIf(([InBudget]=0 And [COIssued?]=0),([ApproxCost]),(0))

If InBudget is no and COissed is No the estimated cost should go into the
TBC field, otherwise it should be 0.

I've tried different ways of writing this expression and I can't figure out
what I'm doing wrong. Am I using the AND statement incorrectly?

 

[Allen Browne]

That seems to be a completely new question, Lori.

It would be good to post it as a new question, so someone who understands it
can answer. I didn't understand it.

True is -1 in Access, but you might be better to use True/False instead
of -1/0. Or perhaps there are not the yes/no fields. I didn't understand the
extra brackets. Neither did I understand what you want for the cases other
than those 2. And neither did I understand how this relates to the Totals
query.

Post a new question, and hopefully someone will follow.

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

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

Okay, this didn't work, I'm thinking that there is too much going on in
the
query so I am attempting to simplify it. Hopefully that will work,
however,
I have two expressions that should work but don't.

I have 2 Yes/No fields, one indicates whether the change is part of the
budget, the second indicates if the item is closed. Now, if the item is in
budget in should not appear in the "Approved Revisions", it should only be
added to the To Be committed column (this part is working) however with
the
following:

Approved Revisions: IIf(([InBudget]=1 And
[COIssued?]=1),(0),([FinalValue]))

it is putting the value in both the Approved revision column AND in the
TBC
column. Naturally it only needs to be in one spot.

The second expression is just the opposite:

TBC Scope Change: IIf(([InBudget]=0 And [COIssued?]=0),([ApproxCost]),(0))

If InBudget is no and COissed is No the estimated cost should go into the
TBC field, otherwise it should be 0.

I've tried different ways of writing this expression and I can't figure
out
what I'm doing wrong. Am I using the AND statement incorrectly?
--
Lori A. Pong

Use a totals query:

1. In query design view, depress the Totals button on the toolbar.
(Upper sigma icon.) Access adds a Total row to the design grid.

2. Accept Group By in the Total row under fields such as contractor and
cost
code. Change it to Sum under the amount.