Database Design

[DontKnow]

Hi Guys,

If I have a table containing details of personal attributes named:
Employeedetails and another table called: FileDetails. The table "StaffFile"
contains 15 questions that are to be answered for every record that is
contained in table Employeedetails.

To do this I have a form that is bound to the table Employeedetails and a
subform (in the form of a tab control) that has all the fields in the table
StaffFile.

My problem is that I am unsure of the best way to have all the 15 questions
displayed as soon as a record is produced in table Employeedetails.

So far I have come up with an insert query that populates the subform with
all 15 questions as soon as the person is entered into the database. Is this
the best way to do this??

The table desin is:

EmployeeDetails
RegisterID (PK)
Surname
Firstname
Employer
Office Location
Department

StaffFile
StaffFileNo (PK)
RegisterID (FK)
FileDetails
DocumentLink

In Table "StaffFile" field "FileDetails" has 15 questions that are to be
answered for each person in table EmployeeDetails.

Please help!!

Cheers
Ps. I hope this is clear??

 

[Armen Stein]

My problem is that I am unsure of the best way to have all the 15 questions
displayed as soon as a record is produced in table Employeedetails.

So far I have come up with an insert query that populates the subform with
all 15 questions as soon as the person is entered into the database. Is this
the best way to do this??

In Table "StaffFile" field "FileDetails" has 15 questions that are to be
answered for each person in table EmployeeDetails.

Yes, this is an acceptable way to do it. Sometimes called
"pre-loading", basically you are inserting a template set of child
records whenever the parent record is created.

Something to consider: What if you add another question? Do you add
the 16th question to all the existing employees? This would require a
combination of a missing records and Append query. Or do you just
preload the questions for employees created after that time? Then
you're fine with what you're doing.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[DontKnow]

hi Armen, Thnaks for replying!!

yes thats a valid pouint that you raise with regards to the extra question.
What I have already setup is a unique index on the person ID and the question
so that the same question cannot be added to the same person. If the
questions numbers are increased then when the user would enetr the subform
this would triggerthe query to activate and therby adding only the additional
question.

Another question I have is what is the best way to activate the query to add
the records (the 15 or so questions to the subform.

1. is it best to add when the user hovers over the subform or
2. tries to enter a value in the subform?

I don't know, I currently have when a user goes to the new record PB but if
the user does not wish to enter a new user the questions have already been
preloaded, and this looks rather a dirty situation??

What are your thoughts as to how/when the activation occurs??

Cheers,

Thnaks for your help!!

 

[Armen Stein]

Another question I have is what is the best way to activate the query to add
the records (the 15 or so questions to the subform.

1. is it best to add when the user hovers over the subform or
2. tries to enter a value in the subform?

Neither, actually. The hover would happen way too often and would be
very disconcerting to the user. And refreshing the form to show the
new records while the user is trying to enter a value won't work well.

I don't know, I currently have when a user goes to the new record PB but if
the user does not wish to enter a new user the questions have already been
preloaded, and this looks rather a dirty situation??

Well, you could add the records in the Current event of the main form.
That event fires each time you move to a new record, including when
the form first opens. Some developers avoid the Current event because
it fires too often, but it might work in your situation.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com