Query Expressions

  • Thread starter Thread starter Steven Phillips via AccessMonster.com
  • Start date Start date
S

Steven Phillips via AccessMonster.com

In my query I have Expr1:---,Expr2:---, etc. How can I sum these expression
columns into another column within the same query?
 
It's me,
Sorry I did not mention that I am using DLookUp in the event it impacts
your answer.
Thanks!
 
Thanks Ken,
I put this in and it was accepted but rendered no output.
MySummedValue: [Expr1]+[Expr3]+[Expr5]
MySummedValue: [Expr1] + [Expr2] + [Expr3]
Click to expand...
 
Thanks again but I'm not getting the right answer. SumOfExprs: yielded an
answer of 121233321, it should have been 675.




In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?
SumOfExprs:[Expr1] + [Expr2] + [Expr3]
Click to expand...
 
Post the entire SQL statement. It may be that you'll need to repeat the
expression from each of the fields and add them.

--

Ken Snell
<MS ACCESS MVP>

Steven Phillips via AccessMonster.com said:
Thanks Ken,
I put this in and it was accepted but rendered no output.
MySummedValue: [Expr1]+[Expr3]+[Expr5]
MySummedValue: [Expr1] + [Expr2] + [Expr3]
Click to expand...
In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?
Click to expand...
Click to expand...
 
Ken,

SELECT DLookUp("[SumOfSumOfPieces]","[DAILY periphery grind ManEwag]") AS
Expr1, DLookUp("[SumOfPieces]","[month prhy grind ManEwag]") AS Expr2,
DLookUp("[SumOfSumOfPieces]","[DAILY periphery grind cncewag]") AS Expr3,
DLookUp("[SumOfPieces]","[month prhy grind cncewag]") AS Expr4, DLookUp("
[SumOfSumOfPieces]","[DAILY Periphery Grind waida's]") AS Expr5, DLookUp("
[SumOfPieces]","[month prhy Grind waida's]") AS Expr6, DLookUp("
[SumOfSumOfPieces]","[Total Daily Grind Kyon Query]") AS Expr7, DLookUp("
[SumOfSumOfPieces]","[Total month Prhy Grind Kyon]") AS Expr8, [Expr1]+
[Expr3]+[Expr5] AS MySummedValue;
 
Can Expr1 or Expr2 or Expr3 contain Null values? If yes, then the Null
propogates through the addition and the result will always be Null. For
example:
Expr1 = Null
Expr2 = 15
Expr3 = 10

Expr1 + Expr2 + Expr3 = Null + 15 + 10 = Null

Assuming that they might contain Null, and that in those cases the Null
should be treated as a zero, use this calculated field:
Nz([Expr1],0)+Nz([Expr3],0)+Nz([Expr5],0) AS MySummedValue
 
By the way, did you notice that you used Expr5 instead of Expr3 in your
expression?

--

Ken Snell
<MS ACCESS MVP>

Steven Phillips via AccessMonster.com said:
Thanks Ken,
I put this in and it was accepted but rendered no output.
MySummedValue: [Expr1]+[Expr3]+[Expr5]
MySummedValue: [Expr1] + [Expr2] + [Expr3]
Click to expand...
In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?
Click to expand...
Click to expand...
 
Check your data types --

the answer of 121233321 appears to be the sum of these text fields being
added together: '121' + '233' + '321'. Try Using CInt or the like to make
certain that they are the correct data type.

Steven Phillips via AccessMonster.com said:
Thanks again but I'm not getting the right answer. SumOfExprs: yielded an
answer of 121233321, it should have been 675.




In my query I have Expr1:---,Expr2:---, etc. How can I sum these
expression
columns into another column within the same query?
SumOfExprs:[Expr1] + [Expr2] + [Expr3]
Click to expand...
Click to expand...
 
Ken,
I did need the correction you stated for null values. I discovered after
my first response to you that there was a null value in one of the fields
and therefore it resulted as you said. I have updated the query with this
new calculation but my result is incorrect. The response from another
individual (below***) is on target but I don't recognize the function. The
sum I get is as he stated.
Expr1 = Null
Expr2 = 15
Expr3 = 10
Click to expand...
Expr1 + Expr2 + Expr3 = Null + 15 + 10 = Null
Click to expand...
Assuming that they might contain Null, and that in those cases the Null
Click to expand...
should be treated as a zero, use this calculated field:
Nz([Expr1],0)+Nz([Expr3],0)+Nz([Expr5],0) AS MySummedValue
Thanks again but I'm not getting the right answer. SumOfExprs: yielded an
Click to expand...
answer of 121233321, it should have been 675.

***
the answer of 121233321 appears to be the sum of these text fields being
added together: '121' + '233' + '321'. Try Using CInt or the like to make
certain that they are the correct data type.
 
Change the calculated field to this:

CInt(Nz([Expr1],0))+CInt(Nz([Expr3],0))+CInt(Nz([Expr5],0)) AS MySummedValue


Note that using the CInt function assumes that the numbers will be integers
between -32768 and 32767. If the number will be larger (or smaller) than
this (numbers between -2,147,483,648 to 2,147,483,647), then use CLng
function instead of CInt.

If the numbers can be fractional (e.g., 34.57), then use CDbl function.
--

Ken Snell
<MS ACCESS MVP>



Steven Phillips via AccessMonster.com said:
Ken,
I did need the correction you stated for null values. I discovered after
my first response to you that there was a null value in one of the fields
and therefore it resulted as you said. I have updated the query with this
new calculation but my result is incorrect. The response from another
individual (below***) is on target but I don't recognize the function. The
sum I get is as he stated.
Expr1 = Null
Expr2 = 15
Expr3 = 10
Click to expand...
Expr1 + Expr2 + Expr3 = Null + 15 + 10 = Null
Click to expand...
Assuming that they might contain Null, and that in those cases the Null
Click to expand...
should be treated as a zero, use this calculated field:
Nz([Expr1],0)+Nz([Expr3],0)+Nz([Expr5],0) AS MySummedValue
Thanks again but I'm not getting the right answer. SumOfExprs: yielded an
Click to expand...
answer of 121233321, it should have been 675.

***
the answer of 121233321 appears to be the sum of these text fields being
added together: '121' + '233' + '321'. Try Using CInt or the like to make
certain that they are the correct data type.
Click to expand...
 
Ken,
That is what it needed, Thanks so much!! Can I possibly ask another
question of you? This query pulls it's data from a table which will
accumulate production data month after month, etc. Two of the expressions
we have been discussing will need to recognize a new month and therefore
not include the prior month in the total. Is it better to do this here, or
when I create the report based on this data.
Expressions 2,4,6 are monthly totals for 3 groups, this calculation will
need to recognize the new month, or the individual expressions
themselves,whichever you suggest.
Expressions 1,3,5 are daily totals and are self maintained with Date()-1


Clng(Nz([Expr2],0))+Clng(Nz([Expr4],0))+Clng(Nz([Expr6],0)) AS
MySummedValue1

Clng(Nz([Expr1],0))+Clng(Nz([Expr3],0))+Clng(Nz([Expr5],0)) AS
Click to expand...
MySummedValue
 
I really can't give you a good answer, as I have no idea of the table
structure nor the data that are in them. In general, it's best to filter the
data as early in the process as possible, so the initial queries would be
best for such date/month filtering, not in this final query. But, as I
noted, it's really difficult to give any specific suggestions based on what
I know (and don't know) about your data and database.

Also, do note that DLookup function is not very fast in a query, so you may
find that the query will run much slower than if you used a query to get the
data from your tables and queries.

--

Ken Snell
<MS ACCESS MVP>

Steven Phillips via AccessMonster.com said:
Ken,
That is what it needed, Thanks so much!! Can I possibly ask another
question of you? This query pulls it's data from a table which will
accumulate production data month after month, etc. Two of the expressions
we have been discussing will need to recognize a new month and therefore
not include the prior month in the total. Is it better to do this here, or
when I create the report based on this data.
Expressions 2,4,6 are monthly totals for 3 groups, this calculation will
need to recognize the new month, or the individual expressions
themselves,whichever you suggest.
Expressions 1,3,5 are daily totals and are self maintained with Date()-1


Clng(Nz([Expr2],0))+Clng(Nz([Expr4],0))+Clng(Nz([Expr6],0)) AS
MySummedValue1

Clng(Nz([Expr1],0))+Clng(Nz([Expr3],0))+Clng(Nz([Expr5],0)) AS
Click to expand...
MySummedValue
Click to expand...
 
Back
Top