Append Query

[Guest]

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

 

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

 

[Guest]

Thank you

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]