Combining multiple records by summing

  • Thread starter Thread starter wesley.allen
  • Start date Start date
W

wesley.allen

Hello, Hope you are doing well.

I am somewaht basic in my databases, and am not good with code. I am
trying to build a query that will pull a bunch of info from one table
(table 1) and one column from another table (table 2). The items that
come from table 1 are unique, and link to one field in table 2. The
field that it links to in table 2 is not unique. My example:

202 John Stone 202 504,300
302 Ty Fields 404 300,000
404 Even Jones 202 300,000


I know I didn't explain it too well, but the idea is, I want to have a
field in my query sum up all the 202's from table two into one field.

If you can understand and help, it would be greatly appreciated.

Thanks.
 
I am somewaht basic in my databases, and am not good with code. I am
trying to build a query that will pull a bunch of info from one table
(table 1) and one column from another table (table 2). The items that
come from table 1 are unique, and link to one field in table 2. The
field that it links to in table 2 is not unique. My example:

202 John Stone 202 504,300
302 Ty Fields 404 300,000
404 Even Jones 202 300,000


I know I didn't explain it too well, but the idea is, I want to have a
field in my query sum up all the 202's from table two into one field.
Click to expand...


SELECT tbl1.ID, tbl1.person, Sum(tbl2.amt) As TotalAmt
FROM tbl1 INNER JOIN tbl2
ON rbl1.ID = tbl2.ID

If you want to get a 0 for people with no related entry in
tbl2, then use LEFT JOIN instead of INNER JOIN.
 
Thank you. Where do I put this code? I am not very good with this
type of programming.

Thanks again.
 
That isn't code, it's a query's SQL statement.

Create a new query and switch to SQL View, then Copy/Paste
the sql statement over whatever Access provided as a start.
Change the names of the fields and tables to the ones you
are actually using. Switch to Datasheet View to run the
query and verify that it does what you want.
 
Sorry, I was trying to make it easy, but I can't get this to work. I
am going to post the actual detail and maybe you can help. Thanks very
much for being so patient.

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.







Marshall said:
That isn't code, it's a query's SQL statement.

Create a new query and switch to SQL View, then Copy/Paste
the sql statement over whatever Access provided as a start.
Change the names of the fields and tables to the ones you
are actually using. Switch to Datasheet View to run the
query and verify that it does what you want.
--
Marsh
MVP [MS Access]

Thank you. Where do I put this code? I am not very good with this
type of programming.
Click to expand...
Click to expand...
 
Sorry, I was trying to make it easy, but I can't get this to work. I
am going to post the actual detail and maybe you can help. Thanks very
much for being so patient.

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.
Click to expand...


The query makes sense, but there is a syntax error in the
expression Portfolio!Shares-[Open Trades]!Shares ( you need
dot instead of !

The only other thought I have is that there may not be any
records for the Left Join to pick up from the Open Trades
table. If that can happen then the expression will have a
Null value, I suspect that you should use:

Portfolio.Shares - Nz([Open Trades].Shares, 0)
 
Back
Top