On Wed, 10 Dec 2008 17:43:29 -0800 (PST), p-rat <osupratt[ at ]yahoo.com> wrote:
[Quoted Text] >I have a simple form that is for payroll data entry. I have a combo
>that allows the data entry people to select the employee number and
>about 8 fields are autofilled with everything from TimeClockID,
>EmployeeName, PayGroupID, etc.
STOP. It sounds like you're missing the main point of how relational databases
work. The relational paradigm is to avoid redundancy, thereby avoiding
redundancy.
Information should be stored in *only one table*. The employee name, paygroup
id, pay category... should exist *once and once only*, in the Employee table;
you should NOT copy this information into the payroll table!
The ONLY field you should put into the payroll table (relevant to the
employee) is the Employee Number. You can *display* the other fields on the
form (e.g. using a Control Source like =cboEmployeeID.Column(n) ) but they
should not be stored.
>The main data entry line is where they choose a Location (CostCenter)
>and PayCategory (Job such as Welder, etc.). Currently there is only
>one line for this entry. This record can then be saved and a new
>record created.
Fine.
>I have a requirement to put a checkbox or button on this form to pop-
>up a subform or something to add more lines as described above where
>the cost center and job title are chosen. This is a rarity, but I have
>the requirement.
How does this relate to the timeclock table? Is the employee working in two
different cost centers, or two different jobs, at the same time? Why two rows?
>What I want to see when done is more than one record if there is more
>than one line chosen. To explain better I guess is that if:
>
>1. On the main form I enter the employeenumber and the fields auto
>populate.
or just display the existing data.
>2. I choose on the form a button or check box that let's me add
>multiple lines (instead of the single line of data).
A Subform perhaps?
>3. A form opens to enter FOR EXAMPLE five total lines of data.
A Subform wll let you add one record, or two, or five, or seventy-three -
however many are needed.
>4. I save this record.
>5. When looking in the table it shows 5 records (not one). All have
>the autofilled information in the records.
>
>Is this possible. If yes then how? Thanks.
Stop, step back, and study the idea of "Normalization" and of "one to many
relationships".
You need at least three tables:
Employees
EmployeeID <primary key>
LastName
FirstName
<other biographical or employee-specific data>
Jobs
JobNo <primary key>
JobDescription
<other info about the job>
Timeclock
EmployeeID <link to Employees>
JobNo <link to Jobs>
StartDateTime
EndDateTime
<other info about THIS employee working on THIS job at THIS time>
There may be other tables, I don't know your business rules... but if you're
trying to store everything in one table you're on the wrong track.
--
John W. Vinson [MVP]
|