Re: I need help with my design

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 14:58:58 -0600

First, you need to understand a bit about normalization. Your fields:
    Reason for consult check off all of the following that apply:
    High cholesterol/triglycerides
    High blood pressure
    Weight loss
    Weight gain
    Vegetarian
    Diabetes
    Sports nutrition
    General healthy eating
    Disordered eating
should all be in a separate but related table. Assuming you have a primary
key on your Client table of ClientID, you would create a table of reasons

    tblReasons
    ReasonID autonumber primary key
    ReasonTitle values like "High Choles..", Weight Loss", "Diabetes",...

and a table the links clients to reasons

    tblClientReasons
    ClientReasonID autonumber primary key
    ClientID Link to Clients.ClientID
    ReasonID link to tblReasons.ReasonID

You would do something similar with the various doctors seen by the client 

-- 
Duane Hookom
MS Access MVP
--
<joso__g@hotmail.com> wrote in message 
news:eXktd.458224$%k.380795@pd7tw2no...
> Sorry, i didn't know that i wasn't allowed to use attachments, this is my 
> first time posting in a newsgroup, so i will try this again. First, here 
> is the requirements:
>
> CLIENTS Name Last name, first name, middle initial
> Address Street, city, postal code
> Phone numbers Home, work, cell including area code
> Fax number Fax # including area code
> Email Email
> Date of birth Date of birth and age
> Gender male or female
> Occupation job
> Marital status married or single
> children yes or no and what ages
> Reason for consult check off all of the following that apply:
> High cholesterol/triglycerides
> High blood pressure
> Weight loss
> Weight gain
> Vegetarian
> Diabetes
> Sports nutrition
> General healthy eating
> Disordered eating
> **** please put empty boxes where I can add additional categories if 
> needed
> Family Doctor Doctors name
> Doctors street, city, postal code
> Doctors phone # including area code
> Insurance coverage Check yes or no or unsure
> Insurance Company name
> Insurance coverage details (empty box I can write in details)
> Referral source check off all of the following that apply:
> Doctor - have sublists of doctors: Dr. Joe Blow, Dr Bob Simpson etc 
> (please leave me space to add)
> 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
> Track interactions Date and time of interaction
> Type of contact (phone, email, appointment)
> Details (leave space here for me to write in details)
> Next action (leave space here for me to write in details)
> Date for next action and description - can you work this into a calender 
> on my palm pilot or in microsoft outlook?
> Other Space to write in anything else
>
> DOCTORS/HEALTH PROFESSIONALS Name Last name, first name, middle initial, 
> Dr.
> Address clinic name, street, city, postal code
> Phone numbers work phone number, alternate phone number including area 
> code
> Fax number fax number including area code
> Email email
> Gender male or female
> Specialty check off all of the following that apply:
> GP (general practicioner)
> Cardiologist
> Pediatrician
> Chiropractor
> Therapist
> Personal Trainer
> **** please put empty boxes where I can add additonal categories if needed
> Clients referred can the client referred (first name, last name, middle 
> initial) automatically be added to this database when referral source is 
> added in the above client database?  (see red above)
> Track interactions Date and time of interaction
> Type of contact (phone, email, appointment)
> Details of contact (leave space here for me to write in details)
> Next action (leave space here for me to write in details)
> Date for next action and description - can you work this into a calender 
> on my palm pilot or in microsoft outlook?
> Other Space to write in anything else
>
> BUSINESS CONTACTS Name Last name, first name, middle initial
> Address street, city, postal code
> Phone numbers work, cell, home phone number including area code
> Fax number fax number including area code
> Email email
> Website website
> Gender male or female
> Description how I know them, what they do
> Track interactions Date and time fo interaction
> Type of contact (phone, email, appiintment)
> Details of contact (leave space here for me to write in details)
> Next action (leave space here for me to write in details)
> Date for next action and description - can you work this into a calender 
> on my palm pilot or in microsoft outlook?
> Other Space to write in anything else
>
>
> Database should have capability to have a report on any of the above 
> variables.  For example track… addresses which can be used to make 
> labels/letters; to send emails to a clump of people; to pull of lists of 
> people seen for specific reasons eg. High choletsterol; and more!
>
> Hi, i am having some trouble decided how to design this database.  So far 
> this is
> what i have come with:
>
> tblPerson
> -PersonId (PK)
> -LastName
> -FirstName
> -MiddleInitial
> -Title
> -DoB
> -Gender
> -MaritalStatus
>
> tblPersonRoleAddress
> -PersonRoleAddressID (autonumber pk)
> -PersonId (unique idx fld1) (FK - tblPerson)
> -PersonTypeId (unique idx fld2) (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
> -PersonRoleAddressID (pk) (fk -tblPersonRoleAddress)
> -Occupation
> -Notes
>
> tblBusinessDetail
> -PersonRoleAddressID (pk) (fk -tblPersonRoleAddress)
> -Website
> -Description
> -Notes
>
> tbldoctorDetail
> -PersonRoleAddressID (pk fld1) (fk -tblPersonRoleAddress)
> -ClinicID (pk fld2) (FK - tblClinicDetail)
>
> I'm not to sure if this is correct so far, if anyone has a better 
> suggestion on how to go about this, let me know.  I was thinking of using 
> a switchboard with 3 different types
> of contacts to enter into the database with fields unique to the type of 
> role.  Plus i have a multi select listbox that i have to do and don't know 
> where to link that.
>
> If anyone can help, i would really appreciate it.  Or even email me. 
> Thanks in advance
>
> joe
>
>

Relevant Pages

  • Re: Re: I need help with my design
    ... Is there a way for me to create a form for each type of person (client, ... the same tblPerson for all of them, that data will appear automatically or be selected when entering them for another role. ... You would do something similar with the various doctors seen by the client ... > Phone numbers Home, work, cell including area code ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Install Fax Client
    ... What area code are you configure in the Location ... Create Area Code Rules, an area code rule determines how phone numbers ... >Thread-Topic: Install Fax Client ...
    (microsoft.public.windows.server.sbs)
  • RE: Install Fax Client
    ... I am looking to deploy the Fax client to my workstatios running XP and 2000. ... Can i force it to install upon login without they being able to stop it? ... What area code are you configure in the Location ... Create Area Code Rules, an area code rule determines how phone numbers ...
    (microsoft.public.windows.server.sbs)
  • Re: Adobe Flex for MV GUI
    ... I don't disagree too much with ... There's no reason to take rules out of the server ... and put them into a client. ... be highly compelling reasons for MV and Caché ...
    (comp.databases.pick)
  • Re: VPN, split DNS and name resolution
    ... No, they ARE good reasons. ... Tom and Deb Shinder's Configuring ISA Server 2004 ... MVP -- ISA Firewalls ... VPN client trys to access the website. ...
    (microsoft.public.isa)