Trying to write an Append Query

[SeekerFan]

How do I write an Append query that will copy non-duplicated Employee Numbers
from one table to another?

 

[Jeff Boyce]

Why? As in "why do you want to add Employee Numbers to a new table?"

There are some legitimate reasons for doing this, and some less legitimate
reasons (attempts to do something that you really don't need to or want to
do in Access).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer MVP]

The basic SQL would look like this

INSERT INTO TableDestination (EmployeeNumber)
SELECT DISTINCT EmployeeNumber
FROM TableSource

HOWEVER, if tblDestination already has some of the EmployeeNumbers in it you
may need to write a more complex version of this.

INSERT INTO TableDestination (EmployeeNumber)
SELECT DISTINCT EmployeeNumber
FROM TableSource LEFT JOIN TableDestination
ON TableSource.EmployeeNumber = TableDestination.EmployeeNumber
WHERE TableDestination.EmployeeNumber is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[SeekerFan]

What I have are two tables where the employee number is the primary key.
Source table is employee information, where the data goes when I have a new
employee. Destination table is where information for time and service awards
are kept. I have a form that I use to view everything, unfortunately from
the way the two tables are set up, if the source table has the employee
number and the destination table does not, it will not pull up the employee
information on the form.
Right now, the query is set up as below:

Source Table Left Joined to Destination Table

QEB:
Field: txtEmployeeNumber
Table: tblTime&ServiceAward
Sort:
Append To: txtEmployeeNumber
Criteria: Is Null

Thank you,

 

[Jeff Boyce]

Are you saying that EmployeeNumber is primary key in both tables?

Does that mean your second (awards) table has one column for each award, or
one record for each award? If the latter, then EmployeeNumber could not be
the primary key, unless, and only if, each employee only gets one award!

"How" depends on "what" -- please post more information about how your
tables are structured.

Regards

Jeff Boyce
Microsoft Office/Access MVP