Please help me with Naturalizing table

[TheRoss]

I have been commissioned to summarize Payroll data from a SharePoint
Site/InfoPath form that is set up to allow 6 different employee's payroll
info. When I link to the data, it has from 1 to 6 employees and their
payroll data all in the same record with fields named: employee; Hours; OT;
employee2; Hours2; OT2; etc.
Any suggestions on how to put all employees into same field (EMPLOYEE)-
rather than 6 different ones - while maintaining relationship to hours worked
(which I also want all in one field instead of 6), etc.?

Do I need to break the table into 6 (or 7 with Key) and then piece back
together? If so, how?

 

[Steve]

TblEmployee
EmployeeID
FirstName
LasstName
etc

TblWorkType
WorkTypeID
WorkType (Regular, Over Time)

TblEmployeeWork
EmployeeWorkID
EmployeeID
WorkDate
WorkTypeID
WorkHours

Steve
(e-mail address removed)

 

[John W. Vinson]

I have been commissioned to summarize Payroll data from a SharePoint
Site/InfoPath form that is set up to allow 6 different employee's payroll
info. When I link to the data, it has from 1 to 6 employees and their
payroll data all in the same record with fields named: employee; Hours; OT;
employee2; Hours2; OT2; etc.
Any suggestions on how to put all employees into same field (EMPLOYEE)-
rather than 6 different ones - while maintaining relationship to hours worked
(which I also want all in one field instead of 6), etc.?

Do I need to break the table into 6 (or 7 with Key) and then piece back
together? If so, how?

A "Normalizing Union Query" is the ticket here. You need to go into the SQL
windo to do this, the query design grid can't handle it:

SELECT employee, Hours, OT FROM tablename
WHERE employee IS NOT NULL
UNION ALL
SELECT employee2, Hours2, OT2 FROM tablename
WHERE employee2 IS NOT NULL
UNION ALL
SELECT employee3, Hours3, OT3 FROM tablename
WHERE employee3 IS NOT NULL
UNION ALL
<etc through all the fields>

Save this query and then base an Append query on it. Not sure what you mean by
"Key" in this context - if you're appending into an hours table with an
autonumber Primary Key, just don't include that field in the Append query and
it will autoincrement.