Unique "ID" problem in Append Query

[TonyT]

Hi,

I am having a problem with the Append Query!!

I have a table in MS Lists that I am importing into Access (all ok with that!), the issue is that because Access and Lists are both using a number as the "ID" I cannot merge the two table into one table as the same Unique ID appears in both the Access table and the List table. How can I merge two tables with the same ID and keep both records?

 

[tvanstiphout]

Add another column, to indicate if the data came from tableA or tableB.

 

[Pat Hartman]

How can I merge two tables with the same ID and keep both records?

You need a new PK. Use an autonumber. If you need the old value for reference, add two columns. OldPK and Source

 

[TonyT]

Sorry for the late reply
Thanks for the info, that's one problem solved!

 

[Pat Hartman]

I'm assuming that you didn't have child tables so you had no FK's to fix up. I add the old PK as well as the source in the combination table. This allows me to update the FK's in the child table when I append them. I join to the new table on the OLD ID and use a source value to restrict the join, then the append can use the new PK as the FK in the append.

 

[TonyT]

your correct, I don't have Child tables. I've only been using Access for a few months so still getting to grips with all its functions buy so far I love it!!

 

[GPGeorge]

Perhaps the best use of your time right now would be to suspend work on a relational database application and devote your efforts to learning how relational database applications work. This is the foundation and without it, nothing good is going to happen. Sound dire? Well, yes, but not starting from a properly normalized table design leads to an overwhelming percentage of problems newer Access developers encounter. So, while it may sound dramatic, it is nonetheless true.

Invest your efforts in learning how to create normalized table designs and you'll be well on your way to resolving the current problem and heading off many others.

I like this set of YouTube videos as an entry point, even though some may object that they are oversimplified. They can get you going in the right direction.