Solved Update two table from one Form (1 Viewer)

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Any help will appreciate.
its a small issue.

I want to change department from frmChangeDepartment form. Then it is also change my Table tblEmployee and tblChangeDepartment.
Db attached, please help me... Thank you.
 

Attachments

  • ChangeDepartment.accdb
    572 KB · Views: 84

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:06
Joined
Feb 28, 2001
Messages
26,996
I rarely open other people's DBs as a matter of preference.

Can you be a bit more specific in what you want to accomplish? It is POSSIBLE that all you need is that your form to change departments merely needs to be powered by a QUERY that joins your Employee and Department tables. But maybe not.

I will tell you that just looking at someone else's work and KNOWING that it isn't doing the right thing just doesn't tell us enough about what you think the right thing should be.
 

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Hi
You are going about this in all the wrong way.

A Department has a number of Employees.

Instead of trying to copy Current Department and allocate a New Department.

You should have a table called tblDepartmentEmployees linked to tblDepartment as shown below:-

tblDepartment
-DepartmentID - PK - Autonumber
-Department

tblDepartmentEmployees
-DepartmentEmployeeID - PK - Autonumber
-DepartmentID - Number - FK -(Linked to tblDepartment PK)
-EmployeeID - Number - FK -(Linked to tblEmployee PK)
-DateFrom - Date/Time
-DateTo - Date/Time
 

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Hi
You are going about this in all the wrong way.

A Department has a number of Employees.

Instead of trying to copy Current Department and allocate a New Department.

You should have a table called tblDepartmentEmployees linked to tblDepartment as shown below:-

tblDepartment
-DepartmentID - PK - Autonumber
-Department

tblDepartmentEmployees
-DepartmentEmployeeID - PK - Autonumber
-DepartmentID - Number - FK -(Linked to tblDepartment PK)
-EmployeeID - Number - FK -(Linked to tblEmployee PK)
-DateFrom - Date/Time
-DateTo - Date/Time
Hello Mr. Mike, Thank you so much.
As your instruction I create new TABLE - DepartmentEmployees
Then....
 

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Please check...
Hi

I created a Main Form based on the tblEmployee and then created a Subform based on the tblDepartmentEmployees.

It is recommended that when you store details of Employees Names then the name should be broken down into the smallest Entity.
I modified the Employee table so that you now enter data for EmployeeFirstname and EmployeeLastname.
This makes searching for a specific Employee is made that much easier by searching based on the EmployeeLastname.

See if this example makes it easier to understand.
 

Attachments

  • Change Depts V1.zip
    44.5 KB · Views: 94

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Hi

I created a Main Form based on the tblEmployee and then created a Subform based on the tblDepartmentEmployees.

It is recommended that when you store details of Employees Names then the name should be broken down into the smallest Entity.
I modified the Employee table so that you now enter data for EmployeeFirstname and EmployeeLastname.
This makes searching for a specific Employee is made that much easier by searching based on the EmployeeLastname.

See if this example makes it easier to understand.
Its not same what the client needed.
tblEmployee table I have deptID. what is the employee joining date day we given him.

you delete the deptID Column . You JOIN to department table. but must be have tblEmployees table in DeptID Column. Because, tblemployee in connection deptID many table & Query in this database.

Actually they're needs HISTORY for Department transaction. And last transaction show Department in the tblEmployee table. if tblEmployee is update the last transaction the others query will be showed.
 

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Are you saying you are creating this database for a Client??
 

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Yes, Mr. Mike.
my manager want like that.
Hi
Please explain what exactly you need.

Normal layout is that an Employee belongs to a Department

Or

A Department has a Number of Employees

Which layout do you need?
 

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Hi
Please explain what exactly you need.

Normal layout is that an Employee belongs to a Department

Or

A Department has a Number of Employees

Which layout do you need?
I want transaction history recored as like that:


DTIDEmployeeIDFromDatePreviousDepartmentNewDepartmentToDate
1​
2​
01/12/2020​
FinanceMaintenance15/12/2020
2​
1​
05/12/2020​
FinanceProjects18/12/2020

And when the employee go to NewDepartment then it is also change in the tblEmployee column DepartmentID.
 

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Hi

The layout that you are requesting is wrong.

I have given you the recommended method of doing this.

If this does not suit then I am sorry this is as far as I can help.

Luck with your project.
 

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Hi

The layout that you are requesting is wrong.

I have given you the recommended method of doing this.

If this does not suit then I am sorry this is as far as I can help.

Luck with your project.
appreciated for your nice advice.
can you talk, if I insert a button CmdUpdate table like below example.
Can possible.
I thing this needs a RUNSQL to change statement in table tblEmloyee.



DTIDEmployeeIDTransactionDateNewDepartmentTransferDate
1​
2​
01/12/2020​
Maintenance15/12/2020CmdUpdate table
2​
1​
05/12/2020​
Projects18/12/2020
 

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Hi

It is possible to Update tblEmployee with a New Department using an Update but as I have pointed out this is the wrong way to record this information.
 

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Hi

It is possible to Update tblEmployee with a New Department using an Update but as I have pointed out this is the wrong way to record this information.
Please do it I need this one.
 

Attachments

  • ChangeDepartment.accdb
    824 KB · Views: 84

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Hi
See if this is what you need
 

Attachments

  • ChangeDepartment V1.zip
    33.2 KB · Views: 90

mike60smart

Registered User.
Local time
Today, 10:06
Joined
Aug 6, 2017
Messages
1,899
Hi

I am not following
When you click Update Table the Employee Stays the same and the Department gets updated as you asked for
 

smtazulislam

Member
Local time
Today, 13:06
Joined
Mar 27, 2020
Messages
806
Hi

I am not following
When you click Update Table the Employee Stays the same and the Department gets updated as you asked for
Employee Table is updated. and go to first data record. Not stayed what Employee department I changed.
ex: if I change departent into employeeID - 5. And when I click the btn Update Table.
then its go to the employeeID - 1.
 

Users who are viewing this thread

Top Bottom