Head is Spinning Redux or Almost there.

[r.m.c]

As per (Professor) LiveOak suggestions I revisted my assumptions of what I thought was Structure and what was Data.

So now I have a simple DB that has:

Employee_Name,Date,Hours _Worked and Daily_Detail.

I can use a calculation to find week# and total hours per day etc

My Dilemma is that I want a field for a weekly summary. Not a report on each weeks daily_detail, but a separate field for summary. It will be faxed off to a Human Resource admin who doesn't want the daily detail that the manager wants.

Now if each record is basically based on that each date is a new record. How do I have a summary field available to add/enter text into for every daily entry, but only while its the same week.

So I would like one summary field for each employee per week.

I have the idea a separtate file would be needed, with a unique_id based on (week_number&employee_id). But what I now need is maybe a script that creates a record every time in this separate file whenever a date is entered that equates to a new week and a corresponding portal in the main file that shows the summary field based on the unique_id of (Employee_ID&week_number). ?

If none of this makes sense, please ignore the ramblings of a newbie.

Any and all help once again appreciated..but not expected. ;-)

Rich

[LiveOak]

You can perform entry from a employee record in the employee file or create a dummy file in which you create records with no real field (only global fields). You isolate various sets of records by creating clever compound key field. For a give employee/week the key field might be:

EmpWkKey (calculation, text, indexed) Right("0000" & EmployeeID, 4) & Right("00" & Week#, 2) & Year

The zeros are used to pad out the keys to an equal length. If you just slam together employeeID and Week#, EmployeeID 1 and Week# 11 look identical to EmployeeID 11 and Week# 1 --- big trouble. As an alternative you can use:

EmpWkKey (calculation, text, indexed, ASCII) EmployeeID & "-" & Week# & "-" & Year

BUT make sure you change the indexing on the calculated result from "English" to "ASCII" or the dashes will be ignored and you'll have the same problem.

To find the total hours for an employee/week/year, set a global field gKey to the appropriate key, build a relationship with this global on the left and the EmpWkKey in your transaction file on the right. Create a total field defined as Sum(YourRelationship::Hours), and viola, you have a week total. By changing the gKey, you can select any employee, week, and year.

The same principle can be used to isolate a single employee for a year, or all employees for a month, etc. Just build the necessary compound keys.

-bd