Append Query

J

Jacinda A

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...
 
J

John W. Vinson

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...

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]
 
J

Jacinda A

Thank you :)

John W. Vinson said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top