Criteria for an Expression Field

[kmkting]

Hi - i have a field that is an expression, "Total C" that adds up "Total A" field and "Total B" field. I want to write an expression in the "Total C" field that excludes any totals of ZERO.

If i put <>0 or other such things in the expression "total C" it asks for parameters of "total A" field and "Total B" field which i do not want. It does not appear when running this query normally.

is there a way to solve this?

Total A Total B Total C
# # Expression {Total A + Total B}

Thanks in advance!

 

[giacomo]

Hello. Can you please provide some clarification as to what you're needing to do? ...like some sample data. Not wanting 0's in an average I could understand, but not sure I get how/why it is effecting your sum.

 

[kmkting]

okay here it goes - i have a table that feeds data to run the query which is run for a specific date (time period).

[Date] [Total A] [Total B] [expression: Total A + Total B]
9/5/06 2 5 7
9/5/06 0 2 2
9/5/06 3 0 3
9/5/06 0 0 0

in this example, i would not want the last entry to appear when running this query. i DO want the two above it to show up.

so i didnt know if there was criteria i could include under the [expression: A + B] field, or write another expression field - i really don't know.

hopefully this makes it easier and i'd appreciate any input. Thanks!

 

[kmkting]

sorry - i didn't preview my post and the data got distorted.

i wanted each number to go under each heading


[Date] [Total A] [Total B] [expression: Total A + Total B]
9/5/06 --- 2 ---------5 ---------------7
9/5/06 --- 0 ---------2 ---------------2
9/5/06 --- 3 ---------0 ---------------3
9/5/06 --- 0 ---------0 ---------------0

so the 2 + 5 gives 7, etc.

 

[giacomo]

That clears it up for me. I had completely misunderstood your question. I believe this could be easily solved by making your criteria be A <> 0 AND B <> 0.

hth,
Giacomo

 

[Joe4]

I don't understand why you are having problems using "<>0" as your Criteria under your expression. It should work.

If you are being prompted for parameters, the question I would ask is this query based on any parameter queries? If you have a parameter query somewhere along the way, the parameter prompts will show up in this query also.

I just had another thought. Are the Total A and Total B fields also calculations in this same query? If so, I can see why you would be getting prompts.

 

[kmkting]

When the query is run regularily there are no prompts...

The Total A and Total B fields are "SUM" fields (as opposed to Group By, Avg, Min, etc)

When i try putting in the <>0 i still get the parameter pop-ups for Total A and Total B.

Let me know if more information is needed.

 

[giacomo]

please post your sql. Go to View >> SQL View in the query editor.

 

[Joe4]

It sounds like you are working with an Aggregate Query. Trying to do additional calculations in an Aggregate Query can be tricky.

I prefer to break it up into 2 queries, one for the Aggregate Query (to Sum up A and B), and then a second query based on your Aggregate Query which does your addition and uses your criteria.

 

[kmkting]

Here is the SQL view:

SELECT REPORT_REPORT_NM_DAILY_PROMOID.PROMO_ID, [RR - Club Abbrev].[Club Abbrev], Sum(REPORT_REPORT_NM_DAILY_PROMOID.APPROVED_CREDIT_CARD) AS SumOfAPPROVED_CREDIT_CARD, Sum(REPORT_REPORT_NM_DAILY_PROMOID.APPROVED_BILL_ME) AS SumOfAPPROVED_BILL_ME, Sum(REPORT_REPORT_NM_DAILY_PROMOID.TOTAL_VISITS) AS SumOfTOTAL_VISITS, [SumOfAPPROVED_CREDIT_CARD]+[SumOfAPPROVED_BILL_ME] AS Expr1, [Expr1]/[SumOfTOTAL_VISITS] AS Expr2, REPORT_REPORT_NM_DAILY_PROMOID.START_DATE
FROM [RR - Club Abbrev] INNER JOIN REPORT_REPORT_NM_DAILY_PROMOID ON [RR - Club Abbrev].[Club Code] = REPORT_REPORT_NM_DAILY_PROMOID.CLUB_CODE
GROUP BY REPORT_REPORT_NM_DAILY_PROMOID.PROMO_ID, [RR - Club Abbrev].[Club Abbrev], REPORT_REPORT_NM_DAILY_PROMOID.START_DATE, REPORT_REPORT_NM_DAILY_PROMOID.CLUB_CODE
HAVING (((REPORT_REPORT_NM_DAILY_PROMOID.PROMO_ID) Like "034*") AND ((REPORT_REPORT_NM_DAILY_PROMOID.START_DATE)>=#8/27/2006# And (REPORT_REPORT_NM_DAILY_PROMOID.START_DATE)<=#9/2/2006#));