Query calc criteria question

[Kathleen Badeau]

Another stupid question...

Is it possible to total on a number field (in a query) only if it meets a criteria for another field in the same record. If so, how do I do it (my head is getting sore from banging on the brick wall) and (there's more) if it doesn't meet the criteria, either leave the amount field blank or with a zero?

Thanks to whomever can help me

 

[Cosmos75]

Iif(Criteria,Calculation if Criteria = True,0)

Then right-click in the query design view and choose TOTAL and then choose Group By SUM in the calculated field.

 

[Kathleen Badeau]

Iif(Criteria,Calculation if Criteria = True,0)

Then right-click in the query design view and choose TOTAL and then choose Group By SUM in the calculated field.

Thanks so much for your help--I will try this out and hope that it works

 

[Kathleen Badeau]

Iif(Criteria,Calculation if Criteria = True,0)

Then right-click in the query design view and choose TOTAL and then choose Group By SUM in the calculated field.

Thanks for your help--I hope this works as it will make my work on this database almost complete

 

[Cosmos75]

Wait, you want to total a field (Number) if another field meets your criteria?

Well you can just add your fields (Another, Number) to a query, your criteria type in your criteria in the criteria field under Another and the do the right click thingy. But any Another that doesn't meet your Criteria won't be shown.

If you need all Another shown you can use the

Iif(Another=Criteria,Number,0)

and do the right-click thingy.

 

[The_Doc_Man]

The brute force way to do this is to use two queries. Perform the filtration in the first query. Do the totals in the second query, which is based on the first one.

I.e. first query is a standard SELECT query that returns only those records meeting your rules. If you have grouping to be done, sort this on the fields you will later want to group by.

Second query is a totals query that takes the first query as its recordsource. You can make THIS one total up or group by whatever you want.

 

[RichMorrison]

If you plan to use a "Totals" query and Sum a field, then you will be better off using a "Where" clause in the query instead of an IIf expression.

Make a Sum of "FieldA"
Make a Where "FieldB" = your criteria

RichM

 

[Kathleen Badeau]

Little more info on my dbase:

I have a field for (contracts signed) amount and also type of contract.

I actually tried your idea first...made a separate query for each of the type of contract that we have (three in total) and then made another query that incorporated the first query--except that when I added the three queries (pulling their respective amounts) I had one record returned--only one Grant actually had a contract signed for each of the type and I could not figure it out as there were no criteria in the second query.

Ultimately I would like to have the following:

Grant amount Awarded $x
Budget - Eval $x (based on a percentage of the award amount --)
Budget - TA $x (same as above)
Budget - DS $x (same as above)

Then:

Contract Signed - Eval (based on actual contracts signed for contractors)
Contract Signed - TA (based on actual)
Contract Signed - DS (based on actual
If I have a Grant Award that does not have a contract signed for any of the types, then I want that field to remain blank or have a zero inserted.

Like I said, when I did the separate query pulling out the amounts for contract signed for each type, it worked; however, when I then put those three queries in the same query (with the budget calc), it only returned the one record that had amounts in all three types.


Ultimately the query will need to have the following fields added:

Payments Made -Eval $x (based on actual)
Payments Made - TA $x (same as above)
Payments Made - DS $x (same as above

Is this possible?

 

[Kathleen Badeau]

And the winner is:

Sum(IIF([TYPE]="EVAL",[AMOUNT]))

 

[Kathleen Badeau]

And the winner is:

Sum(IIF([TYPE]="EVAL",[AMOUNT]))

And (almost forgot)

Group by expression

 

[Cosmos75]

Good for you, Kathleen!

Glad to see you found a way to do it!


There is more than one way to accomplish what you want to do. Am not sure which is the most efficient way, though. Don't know much (actually it's more that I don't know anything) about how the program works or about relational algebra to know that. Am just trying to learn how to get Access to do what I want! (Although, it would be nice to know what Access is doing!)

 

[Kathleen Badeau]

Cosmo--in the same boat as you It's great for this forum because I don't have any experience beyond the simple (canned) queries--let alone sql or vba--but I'm good at mucking about if pointed int he right direction (tenacious)