Please help me with Naturalizing table

  • Thread starter Thread starter TheRoss
  • Start date Start date
T

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?
 
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?
Click to expand...

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.
 
Back
Top