advice on table structure

[slinky]

I am working on a database that will be used for tracking employee
activity based on projects and weeks consumed. So I need a table for
the EMPLOYEES (Name, ID, Role, etc.) and one for PROJECTS (ProjectID,
Name, etc.). Basically I need to emulate an Excel spreadsheet already
in existence which lists something like the below:

Employee: Jane Livensen April 2-8 April
9-15 April 16-22
Project: Area 19 Receivables 80%
65% 50%
Project: Area 22 Benefits 20%
35% 50%

Thus I may be looking at a third table for WEEKS (?)
One hurdle is that I want to display each employee within a subform
like the strcture above, but if I avoid the idea of a table just for
WEEKS then my only thought is to have all 52 weeks as 52 different
fields within the EMPLOYEE or PROJECTS tables.

Ideally the end result needs to have a continuous-form style main form
scrolling vertically through all the employees and within each
employee a subform (also on continuous-form style to allow editing and
conditional formatting) to display the weeks - but these need to
scroll HORIZONTALLY by week. This tool will also serve for forecasting
staffing needs with hypotheticals like if during a particular week an
employee is 80% on one project, only 20% is left for other work.

Any ideas?
Thanks!!!!

[BillCo]

ok, first get the data structure right and then worry about the
interface.

as far as i understand your data, I'm seeing 4 normalised tables: (PK
= primary key, CK = composit key)

tblPersonnel: EmployeeID (PK); Name
tblAreas: AreaNo (PK); AreaDescripition
tblWeeks: WeekNo (PK); DateFrom; DateTo
tblPercentages: EmployeeID (CK); AreaNo (CK); WeekNo (CK); Percentage

....and if employees have a restricted set of areas in which they
operate, you'll need a fifth:
tblPersonnelAreas: EmployeeID (CK); AreaNo (CK)

As for interface - it's often a big mistake to design an interface
using a spreadsheet as inspiration. I think it would be a mistake
here. What you are describing is probably more a more useful layout
for a report then a data interface. As a data input / update mechanism
what you've described would be at best confusing.

Personally, I would have a single form for Personnel with a list box
listing the employee's areas of responsability. Another list box would
hold the weeks worked, which would drill down to the percentage
breakdown of time per area in that week on a seperate modal form.

- Based on your question, the above interface set-up may be a bit
advanced for you - you may wish to just use sub-forms instead. But
which ever way you go:
1. stay away from nested sub-forms. just... no!
2. use sanity checking mechanisms to ensure percentages add up to 100%
for each employee-week. you'll be glad you did!

good luck