Lookup

A

Abdul Shakeel

I have three tables name tblDeaprtments, tblEmployees, tblPayroll

tbldepartment contains fields
DepartmentID &
DepartmentName

tblEmployess contains fields
EmployeeID,
EmployeeName,
AccountNo.
BasicSalary,
HoseRentAllowance &
ConveyanceAllowance

tblpayoll contains fields
payrollID,
MonthStartDate,
MonthendDate,
EmployeeID,
DepartmentID

I just want to make a form where I first enter a payroll ID month start date
& month end date then I select department ID which automatically updates
Employees in that particular department when I select a particular employee
his Basic salary, House rent Allowance & Conveyance allowance update
automatically with the figures of tblemployees
 
A

Arvin Meyer [MVP]

Without a 4th table, tblPayrollDetails, you cannot have any history, so when
you change the salary, department, or allowances, all existing records will
change as well. Moving the EmployeeID and DepartmentID to the details table
and adding currency fields for storing the data should solve that problem.
Also, I see by your schema that the EmployeeName is a single field. Since
normalization rules require fields to contain non-decomposible data, I
suggest breaking that field up into at least 2, and better yet, 3 fields.

To do the lookup, use a query to concatenate the EmployeeName back to a
single column, or just add the extra fields, and build a combobox which
contains all the Employee table's fields. In the AfterUpdate event of the
combo, have some code that writes each of the columns to the PayrollDetails
columns. The column index is 0 based so using your table below, one of the
lines of code might look something like:

Me.txtBasicSalary = Me.cboEmployeeID.Column(3)
 
S

Shan

cannot reply!!
Arvin Meyer said:
Without a 4th table, tblPayrollDetails, you cannot have any history, so
when you change the salary, department, or allowances, all existing
records will change as well. Moving the EmployeeID and DepartmentID to the
details table and adding currency fields for storing the data should solve
that problem. Also, I see by your schema that the EmployeeName is a single
field. Since normalization rules require fields to contain
non-decomposible data, I suggest breaking that field up into at least 2,
and better yet, 3 fields.

To do the lookup, use a query to concatenate the EmployeeName back to a
single column, or just add the extra fields, and build a combobox which
contains all the Employee table's fields. In the AfterUpdate event of the
combo, have some code that writes each of the columns to the
PayrollDetails columns. The column index is 0 based so using your table
below, one of the lines of code might look something like:

Me.txtBasicSalary = Me.cboEmployeeID.Column(3)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top