Troubleshoot Append Query 1

[splats]

Hello

I am trying to get an append query to work properly. Basically, I am able to import the updated employee data into a table (ImportData). I would then like to have that data added to another table(Employee). There are 336 records in the ImportData table (which is current employees). There are 322 records in the Employee table
(old employees, some of which may not be around anymore). I would like the append query to update the employee list to add the new employees that are not currently on the list and to update any information on current employees. I would still like to keep the employees that are not with the company anymore as well. What happens next is that the append query wants to add approx 132048 records to the employee table. I don't understand why it wants to multiply the number of records to add. I have created a one to one relationship between both tables. The employee table has an ID (autonumber) as well however it is not the primary key. Employee.EMPNO and ImportData.F1 are the primary keys for each. I would like the data to be updated in the Employee table so that it gets the most current information.

The code for the append query is:

INSERT INTO Employee ( EMPNO, NAME, DOB, OCCUPATION, DEPCODE )
SELECT ImportData.F1, ImportData.F2, ImportData.F3, ImportData.F4, ImportData.F5
FROM Employee, ImportData;

The code for the command button to initiate the import on my form is:

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2000, "ImportData", "C:\Documents and Settings\Tina\My Documents\Datalogica Information Design\Safety WCB Database\Test1.xls", False

DoCmd.OpenQuery "QuEmployeeImport", acNormal, acEdit

DoCmd.RunSQL "delete * from ImportData"


Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub


Any ideas on how to get this to work properly would be greatly appreciated.

Thank you

tina

 

[lupins46]

FROM ImportData
(remove Employee)

 

[lupins46]

But an append query will only ADD records (and it will add ALL records unless there is a unique index duplication. It will not update records.

To Insert records only where there is no current record you need:

INSERT INTO Employee ( EMPNO, [NAME], DOB, OCCUPATION, DEPCODE )
SELECT ImportData.F1, ImportData.F2, ImportData.F3, ImportData.F4, ImportData.F5
FROM ImportData
Where F1 Not in (Select EmpNo from Employee)

To Update existing records you can use :
Update Employee Inner join Import
on Employee.Empno = Import.F1
set EMPNO = Import.F1, [NAME]= Import.F2, DOB = Import.F3, OCCUPATION = Import.F4,DEPCODE =Import.F5

 

[splats]

Thank you Lupins46! I get it now. Your help is much appreciated!

Cheers

Tina