Is this a time to use a field with repeating values?

[guywithpipe]

I have a basic table called "Daily Staffing" which has a date, and lists employee's names (with a relationship to table "employees"), the hours they worked, the employee's total pay, and the total amount paid to all employee's that day. So it looks like:

Date:

Employee Name: Hours: Payrate: Amount:

Employee Name 2: Hours2: Payrate2: Amount2::

Employee Name 3: Hours 3: etc. etc.

So the calculation should be simple: Amount= hours x payrate, Amount2=Hours2 x payrate 2

etc.

But I would like pay-rate field to be pulled automatically from the "employee" table. Instead of manually entering it as above.

Another problem I've run into, which is somewhat related, is that I would like to be able to run a payroll report by employee based on the data I entered in daily staffing.

I can tell I'm not setting this up correctly. Any suggestions will be greatly appreciated!

[comment]

No, you should have a separate record for each time period. i.e.

• Date

• EmployeeID

• Hours

• Rate (lookup from Employees)

• cAmount (Hours * Rate)

Then you can run any kind of report: by date, by employee, or whatever else you wish.

[guywithpipe]

Thanks for the quick response!!!

That makes perfect sense except for the fact that a manager will be entering all the employees at once and it would be very convenient see all the employees listed on one page.

Would this work?

1.)Create a table as you suggested, lets call it "Hours and Earnings"

2.)Create a table called "Daily Staffing" which is basically a portal of "Hours and Earnings" and a date field.

3.) Set the relationship of the two tables by date

4.)Create a button on "Daily Staffing" which is a script that will create a new "Hours and Earnings" record and fill it's date field with the matching date field of "Daily Staffing"

5.) The newly created record should now appear on the "Daily Staffing" Table's Portal and the Manager could then fill out the remaining information from the "Daily Staffing" table.

Correct?

[comment]

1.)Create a table as you suggested, lets call it "Hours and Earnings"

Let's call it WorkLog - because "Hours and Earnings" is not a valid name for a table.

2.)Create a table called "Daily Staffing" which is basically a portal of "Hours and Earnings" and a date field.

I don't see what would be recorded in such table.

How about:

1. Add a gDate field (Date, global) to the WorkLog table.

2. Define a self-join of the WorkLog table as:

Worklog::gDate = WorkLog 2::Date

and enable automatic creation of records in WorkLog 2.

3. Place a portal to WorkLog 2 on the layout of WorkLog. Select the date in the global field, and enter the data (EmployeeID and Hours) into the portal.