key violation

[John]

i have a one-to-many relationship between 2 tables. i am needing to develop a
query that will SUM all the records of one field on the 'many' side of the
relationship and then update a seperate field in the 'one' side table with
the sumation. i believe i have the correct syntax but i keep gettin a key
violation error. please help.

 

[KARL DEWEY]

Well we can not see your query - post your SQL statement. Are you trying to
sum and update in a single query?

A key violation would be if you were add a record to the many side and there
were no corresponding one side record.

 

[John]

yes i am trying to do both in the same query. the results are correct when i
do a select query, but it wont push the data into the field in the main table
when i format it into an append query. It gives me the prompt to allow the
records to change, then comes up with the 'key violation'. I cant figure out
how to add the records of the same field of the 'many' side of the
relationship with a UPDATE query. here is the SQL for the append query i have:

INSERT INTO main ( [Managements Estimate of Value])
SELECT Sum(main.[Managements Estimate of Value]) AS [SumOfManagements
Estimate of Value], collateral.[Asset #]
FROM main INNER JOIN collateral ON (main.[Asset #] = collateral.[Asset #])
AND (main.[Asset #] = collateral.[asset #])
GROUP BY collateral.[asset #];