Append Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jacinda A - 18 Jul 2007 21:08 GMT

I am using Access2003

I am trying to set up an append query that will combine similar fields from
two tables onto one.

ex: FName, LName from Table A
FName, LName from Table B
dump into FName, LName on Table C
This is for a human resources type of database... Table A being the employee
and Table B being the dependents.
Table C would represent everyone from those two lists who let's say have
elected health insurance... so the employee from Table A that person's
dependents from Table B... all together in a single list on Table C...

I tried two separate append tables, and I am getting that key violation
error... can someone enlighten me...

Reply to this Message

John W. Vinson - 18 Jul 2007 23:09 GMT

>I am using Access2003
>
[quoted text clipped - 12 lines]
>I tried two separate append tables, and I am getting that key violation
>error... can someone enlighten me...

Key violation? That would suggest that you have a unique Index (such as a
primary key) on the combination of FName and LName. If you have an employee
named George Foreman with his five sons named George, you'll get this error!

I think you want a UNION query:

SELECT FName, LName FROM TableA
UNION ALL
SELECT FName, LName FROM TableB
ORDER BY LName, FName;

to get one list of all names from both tables. You could base an Append query
on this table to populate TableC, but I'd ONLY do that if you have very good
reasons to store data redundantly!

John W. Vinson [MVP]

Reply to this Message

Jacinda A - 19 Jul 2007 14:46 GMT

Thank you :-)

> >I am using Access2003
> >
[quoted text clipped - 29 lines]
>
> John W. Vinson [MVP]

Reply to this Message