Summing an Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following expression in a query. I need to sum this colum. How?

I tried changing the total to sum. No change in data. Still displays all
records.

Expr1:
IIf([PUBLIC_T_DIARY_ADJ.CRED_DA_VAL]=1,0,IIf([PUBLIC_T_DIARY.CRED_DA_VAL]=1,0,1))
 
I have the following expression in a query. I need to sum this colum. How?

I tried changing the total to sum. No change in data. Still displays all
records.

Expr1:
IIf([PUBLIC_T_DIARY_ADJ.CRED_DA_VAL]=1,0,IIf([PUBLIC_T_DIARY.CRED_DA_VAL]=1,0,1))
Click to expand...

Please post the SQL view of your query. I suspect you're grouping by
all fields, but without seeing the whole query it's hard to tell.

John W. Vinson[MVP]
 
John Vinson said:
I have the following expression in a query. I need to sum this colum. How?

I tried changing the total to sum. No change in data. Still displays all
records.

Expr1:
IIf([PUBLIC_T_DIARY_ADJ.CRED_DA_VAL]=1,0,IIf([PUBLIC_T_DIARY.CRED_DA_VAL]=1,0,1))
Click to expand...

Please post the SQL view of your query. I suspect you're grouping by
all fields, but without seeing the whole query it's hard to tell.

John W. Vinson[MVP]
Click to expand...
Here id the SQL view you requested.

SELECT
IIf([PUBLIC_T_DIARY_ADJ.CRED_DA_VAL]=1,0,IIf([PUBLIC_T_DIARY.CRED_DA_VAL]=1,0,1)) AS Expr1
FROM (PUBLIC_T_DIARY INNER JOIN PUBLIC_T_CD_TBL_DTL ON
PUBLIC_T_DIARY.CRED_REAS_T = PUBLIC_T_CD_TBL_DTL.CD_ID) LEFT JOIN
PUBLIC_T_DIARY_ADJ ON (PUBLIC_T_DIARY.CONT_ID = PUBLIC_T_DIARY_ADJ.CONT_ID)
AND (PUBLIC_T_DIARY.DWR_DT = PUBLIC_T_DIARY_ADJ.DWR_DT)
GROUP BY PUBLIC_T_DIARY.CONT_ID, PUBLIC_T_DIARY.DWR_DT,
PUBLIC_T_DIARY.CRED_DA_VAL, PUBLIC_T_DIARY_ADJ.CRED_DA_VAL,
PUBLIC_T_CD_TBL_DTL.CD_DESC
HAVING (((PUBLIC_T_DIARY.CONT_ID)=[Enter Contract]+Space(10)))
ORDER BY PUBLIC_T_DIARY.DWR_DT;
 
Mike,

If I'm reading this correctly, the only situation in which you get a '1' as
the value of Expr1 is when

[PUBLIC_T_DIARY_ADJ.CRED_DA_VAL] <> [PUBLIC_T_DIARY.CRED_DA_VAL]

Is that correct? Or is there a value reversal here?

If my reading is correct, maybe you should try substituting the simpler
expression and simply count the number of records where these two fields are
not the same?
 
GROUP BY PUBLIC_T_DIARY.CONT_ID, PUBLIC_T_DIARY.DWR_DT,
PUBLIC_T_DIARY.CRED_DA_VAL, PUBLIC_T_DIARY_ADJ.CRED_DA_VAL,
PUBLIC_T_CD_TBL_DTL.CD_DESC
HAVING (((PUBLIC_T_DIARY.CONT_ID)=[Enter Contract]+Space(10)))
Click to expand...

Well, this will show you one line for every distinct value of Cont_ID,
DWR_DT, Cred_Da_Val in both tables, and CD_Desc. That's what the GROUP
BY expression does. Sounds like you're getting what you're ASKING for.

However, that's evidently not what you *want* - and since I don't know
what your tables contain nor what you want to see, I'm not sure how to
change the query to give you what you want.

What is your desired output? What's (in brief) the nature of the data
in the tables?


John W. Vinson[MVP]
 
Chaim said:
Mike,

If I'm reading this correctly, the only situation in which you get a '1' as
the value of Expr1 is when

[PUBLIC_T_DIARY_ADJ.CRED_DA_VAL] <> [PUBLIC_T_DIARY.CRED_DA_VAL]

Is that correct? Or is there a value reversal here?

If my reading is correct, maybe you should try substituting the simpler
expression and simply count the number of records where these two fields are
not the same?

--
Chaim


Mike Johnson said:
I have the following expression in a query. I need to sum this colum. How?

I tried changing the total to sum. No change in data. Still displays all
records.

Expr1:
IIf([PUBLIC_T_DIARY_ADJ.CRED_DA_VAL]=1,0,IIf([PUBLIC_T_DIARY.CRED_DA_VAL]=1,0,1))
Click to expand...
Click to expand...

Chaim, after thinking about it, you're right. But how do I count the records
has you have seguested?
 
Mike,

I'm not all that comfortable with the QEB grid yet- I tend to use the SQL
View as a result of my work history- but you should be able to set the
expression as a field in a Select query (with the Summation symbol depressed
on the Toolbar so that the Total line shows on the grid), and select Count on
the Total line.

Grid should look like:

Field: [PUBLIC_T_DIARY_ADJ.CRED_DA_VAL] <> [PUBLIC_T_DIARY.CRED_DA_VAL]
Total: Count

The SQL would look like:

select [PUBLIC_T_DIARY_ADJ.CRED_DA_VAL] <> [PUBLIC_T_DIARY.CRED_DA_VAL]
as Expr1, Count (Expr1) from ....

I don't know what if any join conditions there are, so I can't tell what the
From clause will look like.

Good Luck!
--
Chaim


Mike Johnson said:
Chaim said:
Mike,

If I'm reading this correctly, the only situation in which you get a '1' as
the value of Expr1 is when

[PUBLIC_T_DIARY_ADJ.CRED_DA_VAL] <> [PUBLIC_T_DIARY.CRED_DA_VAL]

Is that correct? Or is there a value reversal here?

If my reading is correct, maybe you should try substituting the simpler
expression and simply count the number of records where these two fields are
not the same?

--
Chaim


Mike Johnson said:
I have the following expression in a query. I need to sum this colum. How?

I tried changing the total to sum. No change in data. Still displays all
records.

Expr1:
IIf([PUBLIC_T_DIARY_ADJ.CRED_DA_VAL]=1,0,IIf([PUBLIC_T_DIARY.CRED_DA_VAL]=1,0,1))
Click to expand...
Click to expand...

Chaim, after thinking about it, you're right. But how do I count the records
has you have seguested?
Click to expand...
 
Back
Top