Append Key Violation error

[Guest]

I have 5 tables trying to append approx 18 fields. 4 of the fields I
converted to a Y or N from a Y/N data type. The target table is set up as a
Text field with a manuel Y or N added in. Is this why I am getting a Key
Violation? All of the tables seem to have the same data type (besides the
above Y/N) fwith no dupes? Any reason why this is not appending? Does the
Required field need t be the same in the source and target table? Where
should I begin or what else do I need to look at? At this time I am only
trying to move 2 records. Should not be that tough? Thank you

 

[Guest]

Does the > Required field need t be the same in the source and target table?

yes - the same 'type' - - this is Access trying to help you ; for better or
for worse....

you can change the data type of the originating table before you
append....or you can append as same types and then change the type of the
final table....

when you change types in a table it will warn you that you may lose
data...do an experiment and see if the y or n remains as you want it....

 

[Guest]

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 #];

 

[Guest]

this is a quick off-the-cuff reply as I really did not analyze your query
details in being pressed for time at the moment....but just a comment that
you state that your problem is that you are attempting to do this in one
query: note that if you can make work in 2 queries - then don't
over-complicate. You can use a macro to string 2 queries to work one after
the other at the press of a button....
--
NTC

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 #];

Does the > Required field need t be the same in the source and target table?

yes - the same 'type' - - this is Access trying to help you ; for better or
for worse....

you can change the data type of the originating table before you
append....or you can append as same types and then change the type of the
final table....

when you change types in a table it will warn you that you may lose
data...do an experiment and see if the y or n remains as you want it....