Specific Expression Help

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

Guest

Can anyone figure out why this expression would give me sums which include
cases where Co.Revision ="Void" if (and apparently only if) there are other
Change orders which are not void with the same number. That is exactly what
I am trying to avoid, and I don't know what I am missing.

CumChange: (SELECT Sum(AmtChOrder) FROM [tblChgeOrders]
WHERE((Co.[Revision] <> "Void") AND ( [ContrNo] = CO.[ContrNo] )AND
([Change#] <= CO.[Change#] )))

Thanks in advance for any advice you can offer.
 
Try using an exists clause to filter out

CumChange: (SELECT Sum(AmtChOrder)
FROM [tblChgeOrders]
WHERE NOT EXISTS(
SELECT *
FROM tblChangeOrders As A
WHERE A.Revision = "Void"
AND A.ContrNo=CO.ContrNo
AND A.[Change#] <=CO.[Change#] <<<<----????
)
AND [ContrNo] = CO.[ContrNo]
AND [Change#] <= CO.[Change#] )

You may not need the line flagged with "<<<<----????". It depends on the
logic you want for the sum clause
 
John Spencer said:
Try using an exists clause to filter out

CumChange: (SELECT Sum(AmtChOrder)
FROM [tblChgeOrders]
WHERE NOT EXISTS(
SELECT *
FROM tblChangeOrders As A
WHERE A.Revision = "Void"
AND A.ContrNo=CO.ContrNo
AND A.[Change#] <=CO.[Change#] <<<<----????
)
AND [ContrNo] = CO.[ContrNo]
AND [Change#] <= CO.[Change#] )

You may not need the line flagged with "<<<<----????". It depends on the
logic you want for the sum clause

r. howell said:
Can anyone figure out why this expression would give me sums which include
cases where Co.Revision ="Void" if (and apparently only if) there are
other
Change orders which are not void with the same number. That is exactly
what
I am trying to avoid, and I don't know what I am missing.

CumChange: (SELECT Sum(AmtChOrder) FROM [tblChgeOrders]
WHERE((Co.[Revision] <> "Void") AND ( [ContrNo] = CO.[ContrNo] )AND
([Change#] <= CO.[Change#] )))

Thanks in advance for any advice you can offer.
Click to expand...
Click to expand...
 
Oops--I apologize for the blank reply above.

Both from what I can follow in the logic and what appears to be happening
when I run this, this expression filters out every change order on a project
after a void has been entered. What I need to do is filter out all the
voids, and only the voids.

John Spencer said:
Try using an exists clause to filter out

CumChange: (SELECT Sum(AmtChOrder)
FROM [tblChgeOrders]
WHERE NOT EXISTS(
SELECT *
FROM tblChangeOrders As A
WHERE A.Revision = "Void"
AND A.ContrNo=CO.ContrNo
AND A.[Change#] <=CO.[Change#] <<<<----????
)
AND [ContrNo] = CO.[ContrNo]
AND [Change#] <= CO.[Change#] )

You may not need the line flagged with "<<<<----????". It depends on the
logic you want for the sum clause

r. howell said:
Can anyone figure out why this expression would give me sums which include
cases where Co.Revision ="Void" if (and apparently only if) there are
other
Change orders which are not void with the same number. That is exactly
what
I am trying to avoid, and I don't know what I am missing.

CumChange: (SELECT Sum(AmtChOrder) FROM [tblChgeOrders]
WHERE((Co.[Revision] <> "Void") AND ( [ContrNo] = CO.[ContrNo] )AND
([Change#] <= CO.[Change#] )))

Thanks in advance for any advice you can offer.
Click to expand...
Click to expand...
 
Hmm in that case I would have thought your original statement would work.

So you want a "running" sum for a specific control number all the AmtChOrder
except where the record is marked as "Void".

Is that correct? Let's introduce an alias for the subquery table, so we are
sure to get the right references to the right instance of the table.

(SELECT Sum(Tmp.AmtChOrder) FROM [tblChgeOrders] as Tmp
WHERE Tmp.[Revision] <> "Void"
AND Tmp.[ContrNo] = CO.[ContrNo]
AND Tmp.[Change#] <= CO.[Change#] )

Hopefully, I better understand what you are trying to do.

r. howell said:
Oops--I apologize for the blank reply above.

Both from what I can follow in the logic and what appears to be happening
when I run this, this expression filters out every change order on a
project
after a void has been entered. What I need to do is filter out all the
voids, and only the voids.

John Spencer said:
Try using an exists clause to filter out

CumChange: (SELECT Sum(AmtChOrder)
FROM [tblChgeOrders]
WHERE NOT EXISTS(
SELECT *
FROM tblChangeOrders As A
WHERE A.Revision = "Void"
AND A.ContrNo=CO.ContrNo
AND A.[Change#] <=CO.[Change#] <<<<----????
)
AND [ContrNo] = CO.[ContrNo]
AND [Change#] <= CO.[Change#] )

You may not need the line flagged with "<<<<----????". It depends on the
logic you want for the sum clause

r. howell said:
Can anyone figure out why this expression would give me sums which
include
cases where Co.Revision ="Void" if (and apparently only if) there are
other
Change orders which are not void with the same number. That is exactly
what
I am trying to avoid, and I don't know what I am missing.

CumChange: (SELECT Sum(AmtChOrder) FROM [tblChgeOrders]
WHERE((Co.[Revision] <> "Void") AND ( [ContrNo] = CO.[ContrNo] )AND
([Change#] <= CO.[Change#] )))

Thanks in advance for any advice you can offer.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top