Difficulties In Designing Functional Forms And Tables...

Hi,

I am a newbie at Access and am basically designing my first database. This database is for a short term project and is designed to track the attendance of employees at acompany. Here is a brief description of the tables and forms in question.

EmployeeInformation - this table stores records of each employee, their ID, DOB and SIN.

AttendanceProfile- this table stores records of prolonged absence periods for each employee. A given employee can have many attendance profiles, meaning that this table is linked to the EmployeeInformation table via a one-many relationship. This table will provide detailed information about the status and history of each Profile. So for example if an employee misses work for 12 days due to an appendicitis operation, the exact nature and dates of this absence would be listed as a new profile. The primary key for this table is an autonumber field called ProfileID

ActionLog - this table stores the actions taken by company employees (if any) in response to each AttendanceProfile. It is connected to the profiles table via a one-one relationship with ProfileID being the foreign key in this table. Another main field is the ProcedureNum field which will list the number of each procedure taken for a given ActionLog record. This table is needed to track what the company has done/is doing to track an employees prolonged absence.

Allow me to illustrate with an example. An employee, John Doe misses 15 days in June 2001 due to an illness in the family. An AttendanceProfile record is then made for this period illustrating the exact nature of Mr. Doe's absence as well as tracking what official documents he has submitted (eg medical note, official company documents). Another matching record containing the same ProfileID is also made in the ActionLog table describing what the company has done so far to track this absence. So if an HR employee calls Mr Doe's physician to verify this illness or sends a letter requiring further documentation, each of these steps is listed in this log for this absence profile.

Sorry for the long-winded explanation, but better now than to have to clarify myself later. Here is where I'm stuck. I would like to set up two data entry forms, to create new AttendanceProfile records and related ActionLog records. Obviously, these corresponding records are linked directly by the ProfileID field. After completing the AttendanceProfile form, I would like the user to be able to click a command button to open another form (I am avoiding a subform in this case because my attendanceProfile form is huge and I cannot conveniantly fit a subform on it) to open a new form where they can enter Procedure descriptions and dates on the ACtion log. For each new procedure the user enters, an autonumber fields automatically increments the procedureNumber for that specific ProfileID.

Finally, my question to any of you is, assuming that I have already designed the AttendanceProfile table and form, how shall I design the ActionLog table AND forms to accomplish this exact task.

Once again, sorry for the excessive detail. Your assistance is greatlys appreciated.

Regards,
Mike J.