returning a multi-criteria sum from a table to a query field

[Guest]

This site is great. You have helped me a lot and I thank you.
I have query where I'm trying to sum values from another table based on the
value of a record in the current query:

Table:
TypeID ProductID Counterparty Orig Notional
USCPA LBI12376 BONY $50,000
TRV INVE12349 BONY $100,000
USCPA LBI12375 BONY $60,000
USCPA LBI12377 BONY $2,000
TRV INVE12348 BONY $201,000

The DSUM total $301,000

QUERY below: in the Results field I'm using:
DSum("[Orig Notional]","Asset and SLN Data","[Counterparty ]=" & '
[Counterparty ]')
what its giving me is the sum of the entire table, instead of the sum of
only the BONY [Orig Notional] which = $413K. I tried this with variations on
quotes, parentheses and brackets, adding the tablename in front of fieldnames
and it still no dice.
And the sum this calculates is actually more than I need - I need to further
restrict the lookup to [Counterparty]=BONY and TypeID = TRV, so my Results
field in the query for BONY should read $310K. Any ideas - would a subquery
be more fitting?
Thanks.

Counterparty ProductID Orig Notional Result
BofA INVE12357 $199,000 $4,513,805
CHESHAM INVE12358 $210,000 $4,513,805
CHESHAM INVE12358 $210,000 $4,513,805
CHESHAM INVE12359 $205,000 $4,513,805
BONY INVE12348 $201,000 $4,513,805
BONY INVE12348 $201,000 $4,513,805
BONY INVE12349 $100,000 $4,513,805
BONY INVE12349 $100,000 $4,513,805
BONY INVE12349 $100,000 $4,513,805

 

[[MVP] S.Clark]

DSum("[Orig Notional]","Asset and SLN Data","[Counterparty ]=" &

'[Counterparty]')

Because of the apostrophes in the WHERE clause and the extra space in the
square brackets, I would assume that this one returned an error.


DSum("[Orig Notional]","Asset and SLN Data","Counterparty='BONY' AND
TypeID='TRV')
This one should put you in the ballpark.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

This site is great. You have helped me a lot and I thank you.
I have query where I'm trying to sum values from another table based on
the
value of a record in the current query:

Table:
TypeID ProductID Counterparty Orig Notional
USCPA LBI12376 BONY $50,000
TRV INVE12349 BONY $100,000
USCPA LBI12375 BONY $60,000
USCPA LBI12377 BONY $2,000
TRV INVE12348 BONY $201,000

The DSUM total $301,000

QUERY below: in the Results field I'm using:
DSum("[Orig Notional]","Asset and SLN Data","[Counterparty ]=" & '
[Counterparty ]')
what its giving me is the sum of the entire table, instead of the sum of
only the BONY [Orig Notional] which = $413K. I tried this with variations
on
quotes, parentheses and brackets, adding the tablename in front of
fieldnames
and it still no dice.
And the sum this calculates is actually more than I need - I need to
further
restrict the lookup to [Counterparty]=BONY and TypeID = TRV, so my Results
field in the query for BONY should read $310K. Any ideas - would a
subquery
be more fitting?
Thanks.

Counterparty ProductID Orig Notional Result
BofA INVE12357 $199,000 $4,513,805
CHESHAM INVE12358 $210,000 $4,513,805
CHESHAM INVE12358 $210,000 $4,513,805
CHESHAM INVE12359 $205,000 $4,513,805
BONY INVE12348 $201,000 $4,513,805
BONY INVE12348 $201,000 $4,513,805
BONY INVE12349 $100,000 $4,513,805
BONY INVE12349 $100,000 $4,513,805
BONY INVE12349 $100,000 $4,513,805