Update or Append Query

[ramez75]

Hey,

I am working on a database and one of the tables is tblemployee which had the following fields
- Name
- Department
- Date of Hire
- Date of Transfer
- Date Inactive

Also I have tblemployeeArchive which have the following fields
- Name
- Department
- Date of Hire
- Date of Transfer
- Date Inactive

I have created an Append Query (qryAppendtotblemployeeArchive) which works great when adding new employee.

The problem I am having is when the employee transfers to another department. I want to append that information to tblemployeeArchive but for some reason it doesn't work

FOR EXAMPLE:
John Doe in Department "X" hired on 1/1/2014
He got transferred to department "Y" on 5/1/2014.
He got transferred again to department "Z" on 11/1/2014
So my tblemployeeArchive should show
ID Name Dept DateofHire DateofTransfer
1 John Doe X 1/1/2014 Null
2 John Doe Y 1/1/2014 5/1/2014
3 John Doe Z 1/1/2014 11/1/2014

So how can I modify "qryAppendtotblemployeeArchive" to work for both conditions adding new employees and when employees have changed departments as per the example above

Thank you in advance

RB

 

[plog]

That's not the way databases are to work. You don't move data among tables, you add data to the record to designate things. And when making a transaction log, you usually need an additional table.

First a little best practices:

1. 'Name' is a poor choice for a field name, it's a reserved word (http://support.microsoft.com/kb/286335) and will cause issues down the line. I suggest using a prefix to designate what the name is (e.g. EmployeeName, CustomerName, etc.).

2. It looks like you are using 'Name' as a way to unique identify employees, instead you should use an autonumber primary key and assign one to every employee in a different table (Employees). There's more than one 'John Smith' in the world.

With those out of the way, this should be the structure of your data for this:

Employees
EmployeeID, EmployeeName, HireDate, TerminationDate

EmployeeAssignments
EmployeeeID, Department, StartDate, EndDate

That's how you need to structure your tables to do what you want to accomplish. Then, whenever an employee transfers, you add a record to EmployeeAssignments instead of all this data shifting.

 

[MarkK]

I would recommend one employee table, with one record per employee. There is no need to have an archive of exactly the same structure.

Then add a Transfer table structured like . . .

tblTransfer
TransferID (PK)
DepartmentID (FK)
EmployeeID (FK)
TransferDate

So for each employee there might be many transfers, and each transfer does not re-store the employee's name, and so on.

Hope this helps,

 

[ramez75]

Thank you for the prompt response plog.
The field names I provided was just an example. The actual field names are as follows
- strEmpNum (primary Key)
- strLastName
- strDpt
- dtmHireDate
- dtmInactiveDate
-dtmTransferDate


I read and try what you are suggesting

That's not the way databases are to work. You don't move data among tables, you add data to the record to designate things. And when making a transaction log, you usually need an additional table.

First a little best practices:

1. 'Name' is a poor choice for a field name, it's a reserved word (http://support.microsoft.com/kb/286335) and will cause issues down the line. I suggest using a prefix to designate what the name is (e.g. EmployeeName, CustomerName, etc.).

2. It looks like you are using 'Name' as a way to unique identify employees, instead you should use an autonumber primary key and assign one to every employee in a different table (Employees). There's more than one 'John Smith' in the world.

With those out of the way, this should be the structure of your data for this:

Employees
EmployeeID, EmployeeName, HireDate, TerminationDate

EmployeeAssignments
EmployeeeID, Department, StartDate, EndDate

That's how you need to structure your tables to do what you want to accomplish. Then, whenever an employee transfers, you add a record to EmployeeAssignments instead of all this data shifting.

 

[ramez75]

Hi MarkK,

I just wanted to retain the employee's training information and that's why I created the other table in order to capture what departments/areas the employee had been from the date of hire.

How do I add a transfer table structure can you tell me more. Is that an update query or an Append query

I would recommend one employee table, with one record per employee. There is no need to have an archive of exactly the same structure.

Then add a Transfer table structured like . . .


So for each employee there might be many transfers, and each transfer does not re-store the employee's name, and so on.

Hope this helps,

 

[MarkK]

It's a table. Here's how to add a table . . .
https://support.office.com/en-us/article/Create-a-table-8fdc65f9-8d40-4ff5-9212-80e6545e8d87

 

[ramez75]

plog,

My form is linked to tblemployees. This what I have so far:

tblemployees.
- strEmpNum (Primary Key)
- strLastName
- strDpt
- dtmHireDate
- dtmInactiveDate
- dtmTransferDate

tblemployeesTransfer
- strEmpNum
- strLastName
- strDpt
- dtmTransferDate

Right now I have an Append query which works well when I add new employee using "frmEmployee" which is linked to tblemployees. I have a button on the form which on click will activate the append query and add the new employee to tblemployee. I still don't understand how can I populate tblemployeeTransfer with anyone that gets transferred. I don't want to replace what I have in tblemployeeTransfer I want to keep adding to it so I keep history of every employee transfer

That's not the way databases are to work. You don't move data among tables, you add data to the record to designate things. And when making a transaction log, you usually need an additional table.

First a little best practices:

1. 'Name' is a poor choice for a field name, it's a reserved word (http://support.microsoft.com/kb/286335) and will cause issues down the line. I suggest using a prefix to designate what the name is (e.g. EmployeeName, CustomerName, etc.).

2. It looks like you are using 'Name' as a way to unique identify employees, instead you should use an autonumber primary key and assign one to every employee in a different table (Employees). There's more than one 'John Smith' in the world.

With those out of the way, this should be the structure of your data for this:

Employees
EmployeeID, EmployeeName, HireDate, TerminationDate

EmployeeAssignments
EmployeeeID, Department, StartDate, EndDate

That's how you need to structure your tables to do what you want to accomplish. Then, whenever an employee transfers, you add a record to EmployeeAssignments instead of all this data shifting.

 

[plog]

I know forms and reports are the sexy part of building a database, but you aren't to that part yet. You need to properly structure your data. You've got redundant data all over the place-

tblemployees should not have dtmTransferDate nor strDpt, that data should only be in tblemployeesTransfer.

tblemployeesTransfer should not have strLastName, that data should only be in tblemployees.

The term 'Transfer' is also a poor choice for what that table should hold. You should think of it as an 'Assignment' table. You assign a person to a department when they start; if they transfer you update that record with an end date and add another record to show where they were transfered to; when they leave the company you update their last assignment with an end date.

Just because your system is working now, doesn't mean its properly set up to work for what you want it to. The structure I outlined is the proper one to accomplish what you want.

 

[ramez75]

plog,
I followed your recommendation and re-did my tables as below. What do I need to do to retain the information in tblemployeesAssignment.

I need to keep all records of an individual as they move through different areas of the organization. Right now using my form "frmEmployeeAssignment" which is linked to tblemployeeAssignment will over write the older record instead of adding. Do I use an append query. Or do I make "frmEmployeeAssignment" open up as a new record.

I know forms and reports are the sexy part of building a database, but you aren't to that part yet. You need to properly structure your data. You've got redundant data all over the place-

tblemployees should not have dtmTransferDate nor strDpt, that data should only be in tblemployeesTransfer.

tblemployeesTransfer should not have strLastName, that data should only be in tblemployees.

The term 'Transfer' is also a poor choice for what that table should hold. You should think of it as an 'Assignment' table. You assign a person to a department when they start; if they transfer you update that record with an end date and add another record to show where they were transfered to; when they leave the company you update their last assignment with an end date.

Just because your system is working now, doesn't mean its properly set up to work for what you want it to. The structure I outlined is the proper one to accomplish what you want.

 

[plog]

Whenever you have a one to many relationship (1 Employee to many Assignments) you use a subform. The main form would be the Employee form, it would have inputs for all the fields in tblemployee (FirstName, HireDate, etc.). Then in the bottom you would have a sub-form that lists all their assignments.

When an employee transfers, you open the Employee form to their record, go to the bottom, enter a date to close out their open Assignment, then add a new record to denote where they are going to.

 

[ramez75]

Plog,

Thank you, I got it to work as you suggested. With that resolved I can continue with the rest of the forms I need

Whenever you have a one to many relationship (1 Employee to many Assignments) you use a subform. The main form would be the Employee form, it would have inputs for all the fields in tblemployee (FirstName, HireDate, etc.). Then in the bottom you would have a sub-form that lists all their assignments.

When an employee transfers, you open the Employee form to their record, go to the bottom, enter a date to close out their open Assignment, then add a new record to denote where they are going to.

 

[ramez75]

I used the same approach to capture the documents along with their appropriate revision.
tblDocs (strDocNum (PK), strProcName, strProcNum, dtmObsDate)
tblDocRev (strDocNum, strProcRev, dtmRevDate)

The above table feeds a form and a subform (frmDocument and frmDocRev).

All works well.

My next attempt was to somehow link the area, function, department with the documents they are required to be trained on. The way I did that is I created "frmAreaDoc" which is linked to tblArea and a subform "frmDocEmpSub" linked to a tblRequired, tblDoc and tblRev.
tblRequired (lngID, strPRocNum, strProcRev, strDpt, strProcName).

I did it this way to be able to assign what documents every area are required to be trained on. The only issue I am having is when in the subform "frmDocEmpSub" I see all the docs revisions. I wanted only to show the max revision of the document.

Is my approach wrong how can I go around it. I know I can create a query to show the max revision but I cant tie it to "frmDocEmpSub" as I will need to keep tblRequired to record the data which is linking documents with the most current revision to the appropriate Areas, Functions, Groups, Departments, etc.