Append and Update from one table to another

[Sierras]

Hi

I have an append query which takes all the active employees from the
employee table and appends them to a sector table for other purposes. I
tried not to do it this way by adding a field to the employee table for
sectors, but the employee may go to more than one sector.
So I had to do it this way. Anyway,...

This works well:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes));

I also have a delete query for the sector table which removes all inactive
employees. This also works well:

DELETE tblEmployeesSectors.*, tblEmployees.ActiveEmployee, *
FROM tblEmployees INNER JOIN tblEmployeesSectors ON
tblEmployees.IDEmployee = tblEmployeesSectors.IDEmployees
WHERE (((tblEmployees.ActiveEmployee)=No));


The problem is when the user wants to update the Sector table with any new
active employees.

I can not find a way to update just the new records without touching the
ones currently in the sector table. When the append query is run again,
there are duplicate entries for any record that the user updated in the
sector table.

Any way to update or append just the new records based on the EmployeeID?
Bascially, don't update or append that same employee id if it already
exists in the sector table.

 

[Jeff Boyce]

You are running into some of the issues with replicating data in more than
one table ... synchronization can be a pain!

You are not limited to doing it the way you found. Another approach would
be to have your employee table, have a sector table, and use a
"junctions/resolver" table to show valid combinations of employee and
sector. ?You have an employee in 5 sectors? Your junction table would have
5 rows with the same employeeID!

This design allows you to update employee information without having to
ripple the changes through however many tables you've replicated to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sierras]

This is exactly what I've done. I have an Employee table, Sector table
and EmployeeSectors table which is the juction. The only reason for this
automation is to assist the user in creating the junctions faster.
Instead of the user picking a combo box for the employee and then picking
a sector, the automation basically just appends all the employees into the
junction table. Now the user just has to pick the combo for the sector.
It's a lot faster when there are over 400 employees to work with. This
way the sector table is filled with Employees on the left side and a blank
sector combo on the right. If they have to add another sector to an
employee, they just add a record and do it one at a time. But I just
wanted to find a way to help them with the data entry. Basically, I want
the query to do the following, but it's not working:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes) AND
((tblEmployees.IDEmployee)<>[tblEmployeesSectors].[IDEmployees]));

 

[Jeff Boyce]

Are you saying that you have 400+ rows you are adding to the junction table,
with no guarantee that they'll all have corresponding sections? That's not
what a junction table is for ... it probably should only hold valid
combinations, not placeholders.

If you still feel you must create 400+ dummy records in the junction table,
you can open a new query and do an append. If you don't want duplicate
employeeIDs, add a Unique index to the field in your table definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is exactly what I've done. I have an Employee table, Sector table
and EmployeeSectors table which is the juction. The only reason for this
automation is to assist the user in creating the junctions faster.
Instead of the user picking a combo box for the employee and then picking
a sector, the automation basically just appends all the employees into the
junction table. Now the user just has to pick the combo for the sector.
It's a lot faster when there are over 400 employees to work with. This
way the sector table is filled with Employees on the left side and a blank
sector combo on the right. If they have to add another sector to an
employee, they just add a record and do it one at a time. But I just
wanted to find a way to help them with the data entry. Basically, I want
the query to do the following, but it's not working:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes) AND
((tblEmployees.IDEmployee)<>[tblEmployeesSectors].[IDEmployees]));

You are running into some of the issues with replicating data in more
than
one table ... synchronization can be a pain!

You are not limited to doing it the way you found. Another approach
would
be to have your employee table, have a sector table, and use a
"junctions/resolver" table to show valid combinations of employee and
sector. ?You have an employee in 5 sectors? Your junction table would
have
5 rows with the same employeeID!

This design allows you to update employee information without having to
ripple the changes through however many tables you've replicated to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sierras]

I can't create a unique index in the junction table since the
junction table will have duplicate Employee IDs. Each employee could
go to multiple sectors.

What I'd like to do is append the Employee Table to the Junction Table
for only those Employees where their Employee IDs in the Employee
table don't exist in the junction table.

Also, although I am putting the employees into the junction table as
placeholders, these are real valid enties where the user will then
just update each employee sector one at a time.

I've almost got it. I created a query to display the same employees
in both tables. Now all I have to do is figure out how to append the
opposite. (not sure if I'm being very clear on this last one..)

Are you saying that you have 400+ rows you are adding to the junction table,
with no guarantee that they'll all have corresponding sections? That's not
what a junction table is for ... it probably should only hold valid
combinations, not placeholders.

If you still feel you must create 400+ dummy records in the junction table,
you can open a new query and do an append. If you don't want duplicate
employeeIDs, add a Unique index to the field in your table definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is exactly what I've done. I have an Employee table, Sector table
and EmployeeSectors table which is the juction. The only reason for this
automation is to assist the user in creating the junctions faster.
Instead of the user picking a combo box for the employee and then picking
a sector, the automation basically just appends all the employees into the
junction table. Now the user just has to pick the combo for the sector.
It's a lot faster when there are over 400 employees to work with. This
way the sector table is filled with Employees on the left side and a blank
sector combo on the right. If they have to add another sector to an
employee, they just add a record and do it one at a time. But I just
wanted to find a way to help them with the data entry. Basically, I want
the query to do the following, but it's not working:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes) AND
((tblEmployees.IDEmployee)<>[tblEmployeesSectors].[IDEmployees]));

You are running into some of the issues with replicating data in more
than
one table ... synchronization can be a pain!

You are not limited to doing it the way you found. Another approach
would
be to have your employee table, have a sector table, and use a
"junctions/resolver" table to show valid combinations of employee and
sector. ?You have an employee in 5 sectors? Your junction table would
have
5 rows with the same employeeID!

This design allows you to update employee information without having to
ripple the changes through however many tables you've replicated to.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi

I have an append query which takes all the active employees from the
employee table and appends them to a sector table for other purposes. I
tried not to do it this way by adding a field to the employee table for
sectors, but the employee may go to more than one sector.
So I had to do it this way. Anyway,...

This works well:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes));

I also have a delete query for the sector table which removes all
inactive
employees. This also works well:

DELETE tblEmployeesSectors.*, tblEmployees.ActiveEmployee, *
FROM tblEmployees INNER JOIN tblEmployeesSectors ON
tblEmployees.IDEmployee = tblEmployeesSectors.IDEmployees
WHERE (((tblEmployees.ActiveEmployee)=No));


The problem is when the user wants to update the Sector table with any
new
active employees.

I can not find a way to update just the new records without touching
the
ones currently in the sector table. When the append query is run again,
there are duplicate entries for any record that the user updated in the
sector table.

Any way to update or append just the new records based on the
EmployeeID?
Bascially, don't update or append that same employee id if it already
exists in the sector table.

 

[Sierras]

Well I finally figured out how to do this.

First I run an unmatched query between the Employee table and the
junction table. Then I append the results of that query with an
append query to the juction table.

Probably not the best way, but it works.

Just for your info, the reason for doing this is because at the
beginning of the year, there are hundreds of new employees and
hundreds that are no longer active. This is a school system so as the
students (employees) graduate, their gone. Also the table with the
active and inactive employee is being taken care of somewhere else.
This way, as new students join the system, they are easily spotted in
the junction table as their sectors are blank. Anyway, just to let
you know that there was a methos to this placeholder madness...

Thanks again...

I can't create a unique index in the junction table since the
junction table will have duplicate Employee IDs. Each employee could
go to multiple sectors.

What I'd like to do is append the Employee Table to the Junction Table
for only those Employees where their Employee IDs in the Employee
table don't exist in the junction table.

Also, although I am putting the employees into the junction table as
placeholders, these are real valid enties where the user will then
just update each employee sector one at a time.

I've almost got it. I created a query to display the same employees
in both tables. Now all I have to do is figure out how to append the
opposite. (not sure if I'm being very clear on this last one..)

Are you saying that you have 400+ rows you are adding to the junction table,
with no guarantee that they'll all have corresponding sections? That's not
what a junction table is for ... it probably should only hold valid
combinations, not placeholders.

If you still feel you must create 400+ dummy records in the junction table,
you can open a new query and do an append. If you don't want duplicate
employeeIDs, add a Unique index to the field in your table definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is exactly what I've done. I have an Employee table, Sector table
and EmployeeSectors table which is the juction. The only reason for this
automation is to assist the user in creating the junctions faster.
Instead of the user picking a combo box for the employee and then picking
a sector, the automation basically just appends all the employees into the
junction table. Now the user just has to pick the combo for the sector.
It's a lot faster when there are over 400 employees to work with. This
way the sector table is filled with Employees on the left side and a blank
sector combo on the right. If they have to add another sector to an
employee, they just add a record and do it one at a time. But I just
wanted to find a way to help them with the data entry. Basically, I want
the query to do the following, but it's not working:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes) AND
((tblEmployees.IDEmployee)<>[tblEmployeesSectors].[IDEmployees]));


You are running into some of the issues with replicating data in more
than
one table ... synchronization can be a pain!

You are not limited to doing it the way you found. Another approach
would
be to have your employee table, have a sector table, and use a
"junctions/resolver" table to show valid combinations of employee and
sector. ?You have an employee in 5 sectors? Your junction table would
have
5 rows with the same employeeID!

This design allows you to update employee information without having to
ripple the changes through however many tables you've replicated to.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi

I have an append query which takes all the active employees from the
employee table and appends them to a sector table for other purposes. I
tried not to do it this way by adding a field to the employee table for
sectors, but the employee may go to more than one sector.
So I had to do it this way. Anyway,...

This works well:

INSERT INTO tblEmployeesSectors ( IDEmployees )
SELECT tblEmployees.IDEmployee
FROM tblEmployees
WHERE (((tblEmployees.ActiveEmployee)=Yes));

I also have a delete query for the sector table which removes all
inactive
employees. This also works well:

DELETE tblEmployeesSectors.*, tblEmployees.ActiveEmployee, *
FROM tblEmployees INNER JOIN tblEmployeesSectors ON
tblEmployees.IDEmployee = tblEmployeesSectors.IDEmployees
WHERE (((tblEmployees.ActiveEmployee)=No));


The problem is when the user wants to update the Sector table with any
new
active employees.

I can not find a way to update just the new records without touching
the
ones currently in the sector table. When the append query is run again,
there are duplicate entries for any record that the user updated in the
sector table.

Any way to update or append just the new records based on the
EmployeeID?
Bascially, don't update or append that same employee id if it already
exists in the sector table.

 

[Michael Gramelspacher]

I can't create a unique index in the junction table since the
junction table will have duplicate Employee IDs. Each employee could
go to multiple sectors.

What I'd like to do is append the Employee Table to the Junction Table
for only those Employees where their Employee IDs in the Employee
table don't exist in the junction table.

Also, although I am putting the employees into the junction table as
placeholders, these are real valid enties where the user will then
just update each employee sector one at a time.

I've almost got it. I created a query to display the same employees
in both tables. Now all I have to do is figure out how to append the
opposite. (not sure if I'm being very clear on this last one..)

I cannot imagine that a single person will tell you that what you are
attempting is correct. Very nice and compassionate for sure. Maybe
something resembling this.

CREATE TABLE Employees
(employee_id integer NOT NULL PRIMARY KEY);

CREATE TABLE Sectors
(sector_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE EmployeeSector
(employee_id INTEGER NOT NULL,
sector_id INTEGER NOT NULL,
start_date DATETIME NOT NULL,
stop_date DATETIME,
job_title VARCHAR (30),
PRIMARY KEY (employee_id, sector_id, start_date));

This allows an employee to be assigned to many sectors over time.
Possibly an employee can be assigned to multiple sectors at the same
time. Definitely you cannot assign an employee to a sector where he is
already assigned. If the sectors are the same, then the new start date
would have to be after the last stop date. You would always need to
check that start_date < stop_date.

Mike Gramelspacher

 

[Sierras]

Thanks Mike for the support.

I had a feeling I was going to get hosed for this request because I did an
extensive search on the newsgroups for a similar request and found scores
of responses not to make placeholders, that it's not a relational database
solution, questionable table design, etc, etc,..

But I think that for every general rule, there will be exceptions. I
believe this is one of them. And even if it isn't, the user wants to see
it this way so who am I to tell them that it's not a sound database
solution...