How do I import and append data to a table that has a key field ?

[Guest]

I have an excel file with with two columns in it. Part number, and comments.
The part number field does not allow duplicates in the table. I want to be
able to append new comments to the table for existing part numbers, without
duplicating the part numbers. I am able to import new part numbers with
their comments without a problem, I am unable to figure out how to append
comments to existing part numbers in the table.

 

[John Nurick]

Hi Roxanna,

When you say "append comments to existing part numbers", do you mean (1)
adding more text to an existing comment or (2) having multiple separate
comments for each part number?

If (1), access the excel sheet via a linked table (I'll call it
tblXLComments). Then create an update query that joins your existing
table (I'll call it tblParts) with the linked table on PartNumber and
updates tblParts.Comment to something like
tblParts.Comment & Chr(13) & Chr(10) & tblXLComments.Comment

If the latter, you have a 1:many relationship between Parts and
Comments, and need two tables in your database, one for Parts (with no
Comments field and no duplicate PartNumbers allowed) and one for
Comments, with fields for PartNumber (which must allow duplicates) and
Comment.