relationship problem

[Joso]

Hi, i run into a problem deciding how i am going to go about this as i am just learning access. Here it is, it's for a nutritional company. I have 3 different types of clients (Average customer, Doctor, Business Contact). Between these 3 clients they have some similar fields and some different. I decided to create this structure and i'm not sure if it is right so correct me if i am wrong.

tblPerson
-PersonId (PK)
-PersonTypeId (FK - tblPersonType)
-LastName
-FirstName
-MiddleInitial
-Title
-Email
-DoB
-MaritalStatus

tblPersonType (goign to be a lookup)
-PersonTypeId (PK)
-PersonType (determine whether they are a customer, doctor, business)

tblClientDetail
-PersonId (PK)
-Occupation

tblBusinessDetail
-PersonId (PK)
-Website
-Description

tbldoctorDetail
-PersonId (FK - tblPerson)
-ClinicName (FK - tblClinicDetail)

there will be a 1 to 1 relationship from the all the detail tables to tblPerson.

Also, i can't decide what i will do about street address, do to the fact that a client is ther home address, business is there company address and doctors have there clinic name and address, which there different. I can't put that in one field under tblPerson just in case if a doctor or business associate becomes a client and comes in for a weight problem then i will have to create another record for the same person.

I was originally going to put all the fields in one table such as website and description from tblBusinessDetails into tblPerson and just create a form for each type of client with only the necessarey fields, but thought it would be more organized to seperate into different tables where fields are unique for each type of client. I hope this is enough decription of my problem. Any ideas and help will be appreciated. Thanks in advance.

Joso

 

[SteveC24]

Could you maybe divide up the tables even more, and have one for each type of client?

So you would have 1 table for average customers, one for doctors etc etc. Then, you can put in whatever fields you require in each.

A query could be used to pull them all together if necessary.

 

[Joso]

thanks for your help.

Steve, that's what the tables (tblClientDetail, tbldoctordetail and tblbusinessdetail) do, each having unique fields that do not belong to tblPerson. I created a 1 to 1 relationship with those and it seems to work, but it's not future proof when it comes to people having more than one role.

Pat, i see what you are saying is have a composite key containing PersonId and PersonTypeId and that will help with home or business addresses also with work and home emails too. good idea

is splitting each type of person like i stated above ok? then adding a table as pat said?

Pat, is it ok if i send a private message with my relationship diagram?

thanks everyone!

joso

 

[Joso]

tblPerson
-PersonId (PK)
-LastName
-FirstName
-MiddleInitial
-Title
-DoB
-Gender
-MaritalStatus

tblPersonRoleAddress
-PersonId (PK) (FK - tblPerson)
-PersonTypeId (PK) (FK - tblPersonType)
-Street
-City
-Province
-PostalCode
-Email

tblPersonType (going to be a lookup)
-PersonTypeId (PK)
-PersonType (determine whether they are a customer, doctor, business)

tblClientDetail
-PersonId (PK) (FK - tblPersonRoleAddress)
-PersonTypeId (Pk) (FK - tblPersonRoleAddress)
-Occupation
-Notes

tblBusinessDetail
-PersonId (PK) (FK - tblPersonRoleAddress)
-PersonTypeId (PK) (FK - tblPersonRoleAddress)
-Website
-Description
-Notes

tbldoctorDetail
-PersonId (PK) (FK - tblPersonRoleAddress)
-PersonTypeId (FK - tblPersonRoleAddress)
-ClinicName (FK - tblClinicDetail)

I'm not to sure if this is correct, please let me know. Thanks

Joso

 

[Joso]

Thanks Pat for all your help so far. Do you think this is the way to go about it? Is there a better way?

What really puzzles me is how i'm going to create a form for this, will i need a form with 2 sub forms? I am going to create 3 different types of forms for each type of individual linked from the switchboard. Is there way for me to put (for example, occupation and notes from tblclientdetail) with the same form for person?

Joso

 

[Joso]

I am not sure i understand. i attached my relationship so far and maybe you can see if i did it correct. I will also be having different multiselect listbox's for each type of role and wondering how you would implement that so when you enter the doctors information, only the doctors listbox appears on the form.


any ideas?

 

[Joso]

just out of curiousity. would it be possible to put the address in the detail tables, since they are different types of addresses? and take out the tblPersonroleaddress table. Along with the business address i can put the business name, and the same goes for clinicname and it's address for dctordetails.

let me know if this is possible. take care

 

[Joso]

Question 2 and 4 is a no. My guess is question 3 will happen rarely for the client. Number 1 is a yes. That is why i split the addresses up into different role tables. I hope that willl work ok.

I came with this design and if you have a chance maybe you can critique it. I have 2 questions on things i need to implement. First question is about my Interactions table, with nextaction info (next appointment). You think i should create a seperate table for future appointments?

Second question is about a second multiselect listbox that i will be creating for clients. It will be linked to the client table and interactions, sort of the same thing i did with clientreasons multiselect listbox. the new multiselect listbox will be referral source, the list is this:

Referral source check off all of the following that apply:
*Doctor - have sublists of doctors: Dr. Joe Blow, Dr Bob Simpson and space to add more doctors)
*Chiropractor (please leave me space to add): eg. Dr. John Micheals
*Therapist (please leave me space to add): eg. Lois Smith
*Personal Trainer - have sublists (please leave me space to add)
Company Website
Yellowpages
Past client referred
Brochure
**** please put empty boxes where I can add additional categories if needed

The 2 listboxes will go hand in hand, a referral source with the reasons for consultation.

As you can see it will be a multiselect listbox in a multiselect listbox or sublistbox for each source (that has a * beside it). what's even worse, i need the name of this client (whoever i am entering data for on this form) to goto the tblDocHealthDetail table (which contains doctor, Chiropractor, Therapist and Person Trainer) for the selections.

Can this be implemented? If anyone else knows, feel free to let me know, thanks!

Joso

 

[Joso]

Does anyone know if this can be done?

Or maybe have mulitple yes/no checkboxes, and if it's a yes, a combox appears next to it to select let's say a doctors name and so forth?

Joe

 

[The_Doc_Man]

Or maybe have mulitple yes/no checkboxes, and if it's a yes, a combox appears next to it to select let's say a doctors name and so forth?

Well, there are ways to do this. First, search the forum for "cascading combo boxes" as a way to see how to get one combo box to depend on the selection in another combo box.

As to dynamically appearing controls that are context sensitive, you can write some Event code as a fieldname_OnChange event. The code can look at the value in the control just changed and determine what context is implied by it. Then you can use the .Visible and .Enabled properties of other controls to turn them on and off. You can, if you wish, dynamically adjust where they appear. (This is NOT recommended for the VBA-challenged.)

You can use the .Top and .Left properties to locate one corner of the control, then use the .Width and .Height properties to locate the other corners. (You also need to consider the label associated the control.) You can dynamically reposition the controls BEFORE you make them visible. You also have to keep track of where you want to set the focus, and you should perhaps position the controls based on the order of their tab order properties. That would be similar to what you requested, I think.

Like I said, though, it ain't for the VBA-squeamish.

 

[Joso]

thanks doc man for replying. Holy moly, that looks tough. I will do some research on this. What would you recommend me doing? The cascading combo boxes is easier to do, i've seen some samples.

I should just change the concept about a refferal source, usually people have one main referral source when they go to see a professional. I then can use an option box which will then have specific details in the listbox appear.

Also, if you have time, maybe you can look at my relationship diagram? I am having trouble with the interactions table and it's placement, it needs to be asssociated with all three types of people, plus reasons for consultation has to be associated to the interactions for the client, and that's my multiselect listbox (tblClientReasons).

Thanks for your help

Joe