I have a temporary table that imports employeeID and phone numbers associated to that employee
structure is
tblTempPhone
field: EmpID
field: PHN_1 ' a phone number for residence
field: PHN_2 ' a phone number for cell
field: PHN_3 ' etc
The table it needs to update or amend is laid out differently:
tblEmployeePhone
field: EmployeeID 'linked field with EmpID
field: Type 'text field showing type of number - cell, residence etc
field: Number 'applicable number
So, I am trying to figure out what query to create to update tblEmployeePhone so that it adds the employeeID and then PHN_1 is added as the residence type phone number, PHN_2 is added as the Cell phone type phone number and so on.
I don't think it can be done as a sigle query but maybe by going through the recordset?
Full Version: Adding Records From One Table To Another
Hi,
How many types of phone number are we talking about? If not too many, you could just create a separate query for each one.
Just my 2 cents...
How many types of phone number are we talking about? If not too many, you could just create a separate query for each one.
Just my 2 cents...
just 4 types of numbers. I started with that idea but got lost on is it an append or update query? I though append as I am appending a new record but in the query builder, it did not seem to work for me.
EDIT: I resolved part of the issue with your suggestion. Here is the SQL
What I am wondering is if there is a need to bring that data in as a temporary table (tblCOSSPhone) or could I use an IN clause after the FROM to point to an external table? Would I also need an IN clause with the join?
EDIT: I resolved part of the issue with your suggestion. Here is the SQL
CODE
INSERT INTO [Employee Telephone Data] ( EmployeeID, [Telephone Number], [Telephone Type] )
SELECT [Employee Data].EmployeeID, tblCOSSPhone.PHN1, "Residence" AS Expr
FROM tblCOSSPhone INNER JOIN [Employee Data] ON tblCOSSPhone.PRI = [Employee Data].PRI;
SELECT [Employee Data].EmployeeID, tblCOSSPhone.PHN1, "Residence" AS Expr
FROM tblCOSSPhone INNER JOIN [Employee Data] ON tblCOSSPhone.PRI = [Employee Data].PRI;
What I am wondering is if there is a need to bring that data in as a temporary table (tblCOSSPhone) or could I use an IN clause after the FROM to point to an external table? Would I also need an IN clause with the join?
Hi,
If I understand your description correctly, it should be an APPEND query. For example:
INSERT INTO TableName (EmployeeID, PhoneType, PhoneNumber) SELECT EmpID, "cell", PHN_2 FROM TempTable
Just my 2 cents...
If I understand your description correctly, it should be an APPEND query. For example:
INSERT INTO TableName (EmployeeID, PhoneType, PhoneNumber) SELECT EmpID, "cell", PHN_2 FROM TempTable
Just my 2 cents...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.