Should i be having a subform?

[Joso]

Hi there, i recieved this design from doc man in the table design forum and thought since this is a form question i should post here:

tblPerson
fldPersID, autonumber, PK
fldFirstN, text
fldMidN, text
fldLastN, text
fldAddrLine1, text
fldAddrLine2, text
etc.

tblDoctors
fldPersID, Long, FK
info specific to doctors.

tblClients
fldPersID, Long, FK
info specific to clients

tblContacts
fldPersID, Long, FK
info specific to business contacts

tblMeetings
fldMtgID, Autonumber, PK
fldMtgTime, Date/Time
fldMtgEnd, Date/Time
fldMtgLoc, text
etc.

tblAttendees
fldMtgID, Long, FK
fldPersID, Long, FK
fldRSVP, integer, encoded: Accepted, Tentative, Declined, Unreachable, Purposely scheduled extraction of wisdom teeth or adult circumcision to avoid this meeting, Accepted only if it's a cold day in Hell, etc.


Since i will be dealing with 3 types of roles and a person can be all 3 if they wish that is why i have 1 to 1 relationship between tblperson and the 3 detail tables. Should i be having 1 main form and a subform with it for each role. So, 3 main forms with 3 subforms? Then should i have a combo box that has all the person names in it so i can select it if they are already in the database as another role?

Thanks

Joso

 

[RV]

Joso,

don't open multiple threads for one and the same issue.

RV

 

[Joso]

I thought it would be better to place a new thread since this is a form issue not a design issue.

 

[Joso]

thanks Pat, i knew i can count on you. Thank you for guiding me in the right direction, i completely overlooked that option.

So, on the main form i should have the tblPerson info? Then for the 3 role tables, have tabs? Would you recommend queries to bring the tables together or the wizard?

Just another quick question, i want to be able to select the person from a combo box to bring up their info. What do you do in a situation where there is more than one person with the same name in the combo box? Especially in my situation with the 3 types of people, i want to be able to bring up their information every so often.

Thanks again for all your help. i am learning alot.

joso

 

[Joso]

I am trying to find some identifyer for the combo box along with the first and last name. Right now, i have the tblPerson info on the main form then a subform for each of the 3 tabs for the role tables, with each the tabs named "client info", "doctor info" and "business info". For the combo box on the main form the only info i have is the names and gender, nothing unique to select. Except for the autonumber primary key. Any ideas?

Joso

 

[Joso]

I figured tblperson should hold the names instead of putting it in each role table. the fields in tblPerson are the only similar fields between the client, doctor and business contact. but all the othe rinformation in the role tables are unique. i didn't want to repeat the names if i where to put the lastname and firstanme in the role tables just in case if a person is more than one role.
I have a few fields in each of the role tables that are unique for example:

In client table i have "DOB", "MaritalStatus", "HomeAddress", "Children" etc

In doctor table i have "Specialty", "ClinicName"(this goes to lookup for clinc info including address" etc

In Business Contact table i have "Work Address", "Website", "description" etc

I originally had the last name and first name in the role tables and took out tblPerson, because it wasn't very often that a doctor was a client also.

The identifying info is in the role tables

Is there a better solution?

 

[Joso]

Hi, this is what my design looks like. I had to change doc man's suggestion a little bit.

tblPerson
fldPersID, autonumber, PK
fldFirstN, text
fldMidN, text
fldLastN, text
fldGender

tblDoctors
fldPersID, Long, FK
ClinicID, FK
Specialty
Email
etc

tblClients
fldPersID, Long, FK
DOB,
MaritalStatus,
Occupation,
HomeAddress,
etc

tblContacts
fldPersID, Long, FK
BusinessAddress,
Website,
Description
etc

tblMeetings
fldMtgID, Autonumber, PK
fldMtgTime, Date/Time
fldMtgEnd, Date/Time
fldMtgLoc, text
etc.

tblAttendees
fldMtgID, Long, FK
fldPersID, Long, FK
fldReason

I am hoping someone has an idea on how to go about this and guide me in the right direction to setup this form and how i should use a combo box to look up with some sort of identifier.

I want to be able to create a form for appointments with all 3 types of people.

Thanks, i appreciate this.

Joso

 

[Joso]

Pat, what about using a cascade combo box in the header? In the first combo box wil be a filter that has "CLIENT', "DOCTOR" and "BUSINESS CONTACT". Then in the second combo box will have the selection based on a UNION query. Will a union query work? In that union query i can bring the unique info from the role tables connecting to tblPerson along with putting something like "CLIENT" AS ROLE in the query which can be used in the union. Then after the selection it will set focus on one of the tabs (Client info, doctor info, business info).

Something along the lines of this with a unique identifier:

SELECT PersID, [LastName] & ", " & [FirstName] AS [Full name], "DOCTOR" as Role, ClinicName FROM tblPerson INNER JOIN tblDoctors ON PersonID etc.
UNION
SELECT PersID, [LastName] & ", " & [FirstName] AS [Full name], "CLIENT" as Role, DoB FROM tblPerson INNER JOIN tblClient on PersonID etc.
UNION
SELECT PersID, [LastName] & ", " & [FirstName] AS [Full name], "BUSINESS CONTACT" as Role, Address FROM ...

Will that work? Or if anyone else knows, i would really appreciate this.

If there is a better solution, please let me know. I'm all ears.

Thanks

Joso