Best use of tables in forms

[Nikki]

Hi,

I have created ten tables to have in my database in order to track the
following:
Employee Details
Emergency Contacts for Employees
Cellphones and who they are assigned to
Computers and who they are assigned to
Training Courses that employees have attended

The tables I have created are:
Employees
EmployeesContacts
CellphoneTypes
CellphoneContracts
EmployeeCellphones
EquipmentType
Equipment
EmployeesEquipment
Courses
EmployeeCourses
Cellphone Contracts

I would like to create a form for data entry that has employee details on
the front page, Cellphone data on the second page, equipment data on the
third page and courses on the fourth etc.

My question is now that all these tables are linked together through
relationships what is the best way to create the form? Should I create a
query that has all the fields I want on the form or should I create lots of
subforms within my main form? This is always the part I get stuck and things
seem to go wrong.

Any advice would be really appreciated

Cheers

 

[John W. Vinson]

My question is now that all these tables are linked together through
relationships what is the best way to create the form? Should I create a
query that has all the fields I want on the form or should I create lots of
subforms within my main form? This is always the part I get stuck and things
seem to go wrong.

Subforms, unquestionably. The main form would be based on the Employees table;
I'd suggest using a Tab Control with one or more subforms on each tab page.
The subforms would be based on the junction tables - EmployeeCourses, for
example; there'd be a popup form to enter a new course.

For cellphones and emergency contacts - unless multiple employees would have
the same cellphone, or the same contact, you may want to do away with the
junction table and simply have a one to many relationship from Employees to
the Cellphones and Contacts tables, with an EmployeeID foreign key. You might
lose a bit of efficiency if you have a rare case where two employees are
siblings and both list their mother as the emergency contact, but that
situation will be rare and not worth bothering about the duplicate entry of
the one person.

John W. Vinson [MVP]

 

[Nikki]

Thanks. Contacts yes currently no one has the same emergency contact but
cellphones and equipment is a different story. The cellphone and computers
are company equipment so when someone leaves (or upgrades) someone else gets
their old gear so yes multiple employees would end up having the same
equipment. Not at the same time but throughout the life of the database.

 

[John W. Vinson]

Thanks. Contacts yes currently no one has the same emergency contact but
cellphones and equipment is a different story. The cellphone and computers
are company equipment so when someone leaves (or upgrades) someone else gets
their old gear so yes multiple employees would end up having the same
equipment. Not at the same time but throughout the life of the database.

Then I'd suggest keeping the many to many, and having separate maintenance
forms to enter new computers or new cellphones as needed. You could pop these
forms up in the NotInList event of the subform combo box which lets you pick
an existing device, to make it easier to add new devices.

John W. Vinson [MVP]