Re: Sum in Query problem/question STILL LOOKING FOR HELP. CAN ANYONE ASSIST??

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks Steve. Sorry I was not very clear.

My query pulls a share position from my portfolio table. The portfolio
table links to an Open Trades Table using a CUSIP Number. The CUSIP
only appears once in the Portfolio Table, but multiple times in Open
Trades Table. What I am trying to do is get the multiple lines in the
Open Trades table with the same CUSIP to add up and only appear once in
my query.

Is this possible?

Thanks.



Steve Schapel wrote:
Wesley,

You are grouping by fields that return unique values. It seems to me
that you will need to remove such fields from the query. I am not sure
of the meaning of all of the fields, but for a start try removing the
Portfolio.Shares and [Open Trades].Shares fields and see what you get.

--
Steve Schapel, Microsoft Access MVP

wesley.allen@xxxxxxxxx wrote:
Hello,

I am trying to create a query that will sum several lines. Details are
below, but I am trying to have several occurances of Open Trades from a
table sum into one number in my query. When I set it up, it shows a
seperate line for each. I am basic at this, and don't have much
experience with SQL. Can anyone help?

Thanks.


Tables: Fund Name Info, Portfolio, Open Trades
Fields in Query: Portfolio.AccNum, Portfolio.CUSIP,
Portfolio.Description, Portfolio.SECClass, Portfolio.Shares, Open
Trades.Shares, Adjusted Position


SQL:
SELECT Portfolio.AccNum, Portfolio.CUSIP, Portfolio.Description,
Portfolio.SECClass, Portfolio.Shares, [Open Trades].Shares,
Sum((Portfolio!Shares-[Open Trades]!Shares)) AS [Adjusted Position]
FROM [Fund Name Info] INNER JOIN (Portfolio LEFT JOIN [Open Trades] ON
(Portfolio.AccNum = [Open Trades].AccNum) AND (Portfolio.CUSIP = [Open
Trades].CUSIP)) ON [Fund Name Info].[Fund Number] = Portfolio.AccNum
GROUP BY Portfolio.AccNum, Portfolio.CUSIP, Portfolio.Description,
Portfolio.SECClass, Portfolio.Shares, [Open Trades].Shares
HAVING (((Portfolio.SECClass)="TERM" Or (Portfolio.SECClass)="LOC"));


CUSIP is the common field in Portfolio and Open Trades. The open
trades may have several occurances of CUSIP that have different
positions, and I want to SUM those positions in one field.


Thanks Again.


.

Relevant Pages

  • Re: Combining multiple records by summing
    ... CUSIP is the common field in Portfolio and Open Trades. ... The query makes sense, but there is a syntax error in the ...
    (microsoft.public.access.queries)
  • Re: Sum in Query problem/question
    ... What I meant was, if you want the totals to be for each CUSIP, then you will need to remove all fields from the query where the value is different for records *within* each CUSIP grouping. ... My query pulls a share position from my portfolio table. ... table links to an Open Trades Table using a CUSIP Number. ...
    (microsoft.public.access.queries)
  • Re: Modify Sum in a query
    ... SELECT AccNum, CUSIP ... FROM [Open Trades] ... particular CUSIP (Found in the OpenTrades Query column CUSIP) that is ...
    (microsoft.public.access.queries)
  • Re: Sum in Query problem/question
    ... Thanks Again Steve. ... I removed these from the query. ... trying to sum the amounts in the open trades table, ... What I meant was, if you want the totals to be for each CUSIP, then you ...
    (microsoft.public.access.queries)
  • Re: Sum in Query problem/question STILL LOOKING FOR HELP. CAN ANYONE ASSIST??
    ... My query pulls a share position from my portfolio table. ... table links to an Open Trades Table using a CUSIP Number. ...
    (microsoft.public.access.queries)