Table Design and Relationship issues

[chriswinter]

Hello, I have been trying to set up a good Normalized database and need
someone to help me set up forms. What I am setting up is a training
database. I recieve calls from a "requesting Agency" and then set up a
training class and assign it an instructor. I am just starting out,
heck I have re did the database about 5 times, so its not by any means
complete. Here are my tables and their set up relationships, I am
having a problem grabbing all the fields from tblinstructor (parent
table), tblInstcertification (child table of
tblinstructor),tblCertificationType (child of tblInstcertification ),
tblInstPhone(Child table of tblinstructor), tblInstPhoneType (Child
table of tblInstPhone). I end up with a blank form. This form
completed would give me a instructor form to collect the data for an
instructor. Can somone verify that I have my tables Normalized and
that I am set up right with relationships and tell me how to set up a
form with all these different fields from all the different tables.
Thanks in advanced!


tblCertificationType
---CertificationTypeID (autonumber, PK)
---CertificationType (text)

tblClassRequested
---ClassRequestedID (autonumber, PK)
---Classes (text field)
---CEU (text field)

tblContactPerson
---ContactPersonID (autonumber, PK)
---RequestingAgencyID (number, FK)
---First Name (text field)
---Last Name (text field)

tblInstCertification
---InstCertificationID (autonumber, PK)
---InstructorID (number, FK)
---CertificationTypeID (number, FK)
---ExpDate (text field)

tblInstructor
---InstructorID (autonumber, PK)
---FirstName (text field)
---LastName (text field)
---Address (text field)
---City (text field)
---State (text field)
---SSN (text field)
---EmployeeNumber (text field)


tblInstPhone
---PhoneID (autonumber, PK)
---InstructorID (number, FK)
---PhoneTypeID (number, FK)
---PhoneNumber (text field)

tblInstPhoneType
---PhoneTypeID (autonumber, PK)
---PhoneType (text feild)

tblRequestingAgency
---RequestingAgencyID (autonumber, PK)
---AgencyName (text field)
---Address (text field)
---City (text field)
---State (text field)

tblRequestingEmail
---RequestingAgencyEmailID (Autonumber, PK)
---RequestingAgencyID (number, FK)
---EmailAddress (text field)

tblSkills
---skillsID (autonumber, PK)
---skills (text field)

tblAgencyPhone
---PhoneID (autonumber, PK)
---RequestingAgencyID (number, FK)
---PhoneTypeID (number, FK)
---PhoneNumber (text field)

tblAgencyPhoneType
---PhoneTypeID (autonumber, PK)
---PhoneType (text field)

 

[chriswinter]

Ok I deleted the tables that had phone number, number types and email
address...I stuck the phone numbers in their respective tables along
with that email field into the requesting agency table. So now I am
down to 7 tables. About the class and being one-on-one...they are not.
How its going to work is I will hopefully be able to track a agency
that called for a training and add a count to that class once it gets
between 5 to 10 people requesting that class, then we will have the
class. At this point names of students are not needed. Just a running
count. I am in need of help setting up the class form or tables needed
to put all my table data together with some added fields to that form.
So I am able to select from the "main form" instructors (from the
instructor table), a service (from the requesting agency table, just
the name). And what i wanted to end up doing, which I was told I could
do, but never shown how to is, a Not In List Event to beable to add a
service to the requesting agency if entering the service is not in the
requesting agency list I would click add service then it would bring me
to the requesting agency to add a new one. Any help doing this would
be greatly appricated. Thanks

 

[chriswinter]

What I am shooting for in the "main form" would be the following
fields on one form:

Today's Date
Class Number (autonumber assigned to each class that was set up)
Service (tblRequestingAgency.AgencyName drop down with the Not In List
Event)
Class Size
Class Location
Directions to Training
Street (tblRequestingAgency.Address)
City (tblRequestingAgency.City)
State (tblRequestingAgency.State)
Zip (tblRequestingAgency.Zipcode)
Contact Person (tblContactPerson.First and Last Name)
Contact Person Phone Number (tblRequestingAgencyPhone)
Contact Person Email (tblRequestingAgency.Email)
Requesting Month of Training
Training Night
Training Date
Training Start Time
Memo
Class Requested (tblclassrequested)
FR 44 Plan (tblskills)
Thats what I want my "main form to be, but when I add all my fields
thoughout my tabels I end up with a form thats blank. Any help would
be appreciated! Thanks again!

 

[lewie]

When you link your tables in your query behind your main form be sure
to right click on the link and make the correct join. You don't want an
inner join cause if one of the fields in a lookup table is empty your
main query will be blank thus your main form will be empty. run your
query if it is empty so is your form....
Lewie

 

[winty03]

I am still looking for some guidance to seeing up the "main" class form
with the above data, I am not having luck with having a single form
putting form different tables fields. thanks

 

[lewie]

i don't see a main table to link all these together. Maybe a
tblCurrentClasses that would have a pk fieldID(you want
autonumber),service, classID(which shold take care of class size and
location),date schedule = date(), requesting agencyId(which should take
care of street ect), contactPersonID(which will take care of contact
person ect, requesting month,
so you need training night date and start time are they part of the
class tbl? and you need directions will that be part of any table? and
then memo field.
once you make this table with all your fields id, date scheduled,
requesting agencyid,contactpersonid, requesting month,and memo field,
you would lthen create a query linking this main tables to your lookup
tables to get all the fields you need. then you generate a form using
that query. You will have to address those fields I had questions on
and add them either in lookup tables or the main table. when you get
this far you will at least have a structure to see what you like and
don't.