How to combine records between 2 tables that have a 1 to many relationship ?

I am developing an Access application using Access as the front end and SQL Server as the back end database.

I have 2 tables that I join and I want to combine the dollar field [Original Commitment] from table
ztbl_Master_Template with the record(s) from another table ztbl_Source_SSFIII.
************************************************************************
For example, the following represent some of the fields from the 2 input tables
1) ztbl_Source_SSFIII  2) ztbl_Master_Template
and there is 1 ouput table titled ztbl_Source_Funds_Ungrouped_SSFIII

************************************************************************
ztbl_Source_SSFIII

Investor     Legal                                                                                         Income          Capital
Name         Entity      GP                     Contributions    Redemptions   Distributions  Distibutions   masterid
-----------   -------      ----------------    ---------------     ---------------        --------------         ----------              -----------
A. Levin     Class A  Non MS LP/GP   54859.00         25                        30                   10                     2868
A. Levin     Class A  Non MS LP/GP       200.00         40                        50                   20                     2868
A. Levin     Class A  Non MS LP/GP       300.00         60                        20                   40                     2868
A. Levin     Class A  Non MS LP/GP       100.00         80                        10                   44                     2868
************************************************************************
ztbl_Master_Template

Account    Legal                               Original
Name         Entity                               Commitment
---------      -------------------------      ---------------  
A. Levin     MS L.P. Class A Unit       25000.00                
************************************************************************
ztbl_Source_Funds_Ungrouped_SSFIII

masterid    originalCommitment      Redemptions    Distributions        masterid   Contributions
-----------   -----------------------             ---------------          -----------------         ----------     ---------------
2868          25000.00                         205                        224                         2868                 55459

My existing SQL Statement is shown in the Attach Code Snippet section and is not correct because it is overstating the originalCommitment value as a value of 25000 taken from
ztbl_Master_Template times 4 = 100000 in the resulting table.

The system takes the original commitment value of 25000 from the table ztbl_Master_Template and assigns it to the 4 records in table ztbl_Source_SSFIII and then sums these 4 records and calculates the total Original Commitment as 100000 in the resulting table.
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

INSERT INTO ztbl_Source_Funds_Ungrouped_SSFIII (masterID, Fund, reportClientName, originalCommitment,
Redemptions, Reinvestments, Nav, Market_app_depre, Distributions, sourceid, reportDt, sourceFund, [Gross_Sales],
[Investor Name], GP, B.[Legal Entity], ExtractType)
SELECT A.id AS masterID, A.[Fund Name] AS Fund,'Morgan Stanley' As reportClientName, 
SUM(ISNULL(A.[original commitment],0)) As [Original Commitment], 
SUM(ISNULL(B.Redemptions,0)) AS Redemptions, 
0.0, 0.0, 0.0, 
SUM(ISNULL(B.[Income Distributions],0)) + SUM(ISNULL(b.[Capital Distributions],0)) As Distributions, 
[Account Name], 
Max(filedate) As [Reporting Date], 
'SSFIII', 
SUM(ISNULL(B.Contributions,0)) AS Contributions, 
B.[Investor Name], B.GP, B.[Legal Entity], 'GPLP' 
FROM ztbl_Master_Template A 
INNER JOIN ztbl_Source_SSFIII B ON 
B.[Investor Name] = A.[Account Name] AND 
A.[Legal Entity]    LIKE '%' + B.[Legal Entity] + '%' 
AND B.GP NOT IN('MS GP','MS LP') AND (B.[Legal Entity] NOT LIKE '%CLASS E%' AND B.[Legal Entity] NOT LIKE '%CLASS O%') AND id = '2868'
GROUP BY B.[Investor Name],B.[Legal Entity], A.id, A.[Fund Name], A.[Account Name],GP