Append to existing records only

[barkly]

Would appreciate some help with an append query.



Context: Project relates to a share portfolio database where a monthly interest cost is incurred. The append query is used to distribute the monthly interest cost across all held stock in the portfolio proportionally.

If I enter just one month of interest into tbl_Interest the append action distributes the costs into each stock. However if I enter two or more months of interest (into tbl_Interest) the append action then gives multiple entries of each record and their respective interest costs in my receiving table.

All I want is for each month to append an updated value into the existing records should there be more than one month of interest cost data available – not the multiple entries.

tbl_Interest holds each months’ interest.

tbl_InterestCost holds the split of the monthly interest against each company held – (there may be thirty or forty individual companies in the port folio)

The append action is to add the new monthly interest split values to the existing values.

I thought the SELECT DISTINCT might have been an assistance but it wasn’t.

The append query is here

INSERT INTO tbl_InterestCost ( TransactKey, MonthlyInterestSplit, LastUpdate, InterestCostKey )
SELECT DISTINCT qry_Holdings.TransactKey, [MonthlyInterest]*[MyMonRatio]+[MonthlyInterestSplit] AS IntSplit, tbl_Interest.DateInterest, tbl_Interest.InterestKey
FROM (qry_Holdings INNER JOIN tbl_InterestCost ON qry_Holdings.TransactKey = tbl_InterestCost.TransactKey) INNER JOIN tbl_Interest ON qry_Holdings.AccounID = tbl_Interest.AccountID
WHERE (((tbl_Interest.Distributed)<>"Yes"))
GROUP BY qry_Holdings.TransactKey, [MonthlyInterest]*[MyMonRatio]+[MonthlyInterestSplit], tbl_Interest.DateInterest, tbl_Interest.InterestKey;

.

Thanks

barkly

[June7]

INSERT is to add new records to a table.

UPDATE is to modify existing records.