Being a newbie to access, I have been given a task of creating a db for the
patient management and tracking. This DB will have around 50 patients per
year. It will contain the history of patient's contact info, pediatrician,
case manager,Genetic center info, Insurance info and so on. I have created
the tables. pls review them and suggest me your ideas to improve the DB, as
mine maynot be best.

tblPatients
RegID - PK
PatientID,FirstName,LastName,DOB,Gender,Hospital,MA,CaseStatus,Diagnosis,Address,City,Zip,county,MFirstName(mom),MLastName,MAddress,MCity,MState,MZip,MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father),FLastName,FAddress,FCity,FState,FZip,FHomePhone,FcellPhone,CurrentPed,FormerPed,GenCenter,Geneticist,CaseManager,CaseManagement

tblPediatrician_1 and also tblPediatrician_2
PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax

tblGenCenter
center_Id-PK,Name,Address,City,state,zip,phone,Fax

tblGencounselor
CounselorID-PK,FName,LName,Phone,Pager,Fax

tblCaseMgr
MgrID-Pk,FName,LName,Phone,Pager,Fax

tblCaseMgmt
MgmtId-Pk,Name, address,........

tblInsurance
InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone,fax,insuranceName,policyno,CS_Phone

tblInsuranceType
InsuranceType-ID-Pk,RegID,InsuranceID

tblGuardian
guardianId-pk,Refdate,type,fName,LName,address,state,city,zip,hPhone,Cphone,wphone

tblpatientguardian
patguardianID-Pk
Regid
GuardianID

Not an answer to your question, but if you are in the US, keep in mind the
HIPAA privacy and security requirements, Pat. an Access
database cannot be completely secured, so you have to consider whether it
will satisfy HIPAA regulations.

See:
http://www.microsoft.com/office/comm...8-bec2bf26d20c
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Simba" wrote:

> Being a newbie to access, I have been given a task of creating a db for the
> patient management and tracking. This DB will have around 50 patients per
> year. It will contain the history of patient's contact info, pediatrician,
> case manager,Genetic center info, Insurance info and so on. I have created
> the tables. pls review them and suggest me your ideas to improve the DB, as
> mine maynot be best.
>
> tblPatients
> RegID - PK
> PatientID,FirstName,LastName,DOB,Gender,Hospital,MA,CaseStatus,Diagnosis,Address,City,Zip,county,MFirstName(mom),MLastName,MAddress,MCity,MState,MZip,MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father),FLastName,FAddress,FCity,FState,FZip,FHomePhone,FcellPhone,CurrentPed,FormerPed,GenCenter,Geneticist,CaseManager,CaseManagement
>
> tblPediatrician_1 and also tblPediatrician_2
> PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax
>
> tblGenCenter
> center_Id-PK,Name,Address,City,state,zip,phone,Fax
>
> tblGencounselor
> CounselorID-PK,FName,LName,Phone,Pager,Fax
>
> tblCaseMgr
> MgrID-Pk,FName,LName,Phone,Pager,Fax
>
> tblCaseMgmt
> MgmtId-Pk,Name, address,........
>
> tblInsurance
> InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone,fax,insuranceName,policyno,CS_Phone
>
> tblInsuranceType
> InsuranceType-ID-Pk,RegID,InsuranceID
>
> tblGuardian
> guardianId-pk,Refdate,type,fName,LName,address,state,city,zip,hPhone,Cphone,wphone
>
> tblpatientguardian
> patguardianID-Pk
> Regid
> GuardianID

Generally, I can see 4 areas you'll need/want to come up to speed on if you
are going to "create a database for ..." pretty much anything!

1) Relational database design & normalization (Access expects data
organized this way)
2) Access tricks/tips -- Access doesn't do things the same way Word or
Excel do things
3) Graphical user interface design -- if it isn't drop-dead simple to
use, it won't get used
4) Application development -- if you've never built one, how do you know
what to do?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Simba" <(E-Mail Removed)> wrote in message
news:9C500F01-4573-4490-9E8A-(E-Mail Removed)...
> Being a newbie to access, I have been given a task of creating a db for
> the
> patient management and tracking. This DB will have around 50 patients per
> year. It will contain the history of patient's contact info, pediatrician,
> case manager,Genetic center info, Insurance info and so on. I have created
> the tables. pls review them and suggest me your ideas to improve the DB,
> as
> mine maynot be best.
>
> tblPatients
> RegID - PK
> PatientID,FirstName,LastName,DOB,Gender,Hospital,MA,CaseStatus,Diagnosis,Address,City,Zip,county,MFirstName(mom),MLastName,MAddress,MCity,MState,MZip,MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father),FLastName,FAddress,FCity,FState,FZip,FHomePhone,FcellPhone,CurrentPed,FormerPed,GenCenter,Geneticist,CaseManager,CaseManagement
>
> tblPediatrician_1 and also tblPediatrician_2
> PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax
>
> tblGenCenter
> center_Id-PK,Name,Address,City,state,zip,phone,Fax
>
> tblGencounselor
> CounselorID-PK,FName,LName,Phone,Pager,Fax
>
> tblCaseMgr
> MgrID-Pk,FName,LName,Phone,Pager,Fax
>
> tblCaseMgmt
> MgmtId-Pk,Name, address,........
>
> tblInsurance
> InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone,fax,insuranceName,policyno,CS_Phone
>
> tblInsuranceType
> InsuranceType-ID-Pk,RegID,InsuranceID
>
> tblGuardian
> guardianId-pk,Refdate,type,fName,LName,address,state,city,zip,hPhone,Cphone,wphone
>
> tblpatientguardian
> patguardianID-Pk
> Regid
> GuardianID

>>tblPediatrician_1 and also tblPediatrician_2

Big mistake. All your Pediatricians should be in the same table. What if you
have a 3rd Pediatrician?

Also putting in all the mother and father info in the tblPatients could be a
mistake. (1) what about other relatives that might have legal rights such as
stepfathers and grandparents? (2) What if two siblings are also patients? It
might be better to have a tblRelatives. Of course with as few records as you
suggest, you could denormalize it.

Here's something to consider: Has anyone looked at trade journals or other
resources to see if someone already has a product that will meet your needs?
It will almost always be faster and cheaper to buy and off-the-shelf solution
than build your own.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Simba" wrote:

> Being a newbie to access, I have been given a task of creating a db for the
> patient management and tracking. This DB will have around 50 patients per
> year. It will contain the history of patient's contact info, pediatrician,
> case manager,Genetic center info, Insurance info and so on. I have created
> the tables. pls review them and suggest me your ideas to improve the DB, as
> mine maynot be best.
>
> tblPatients
> RegID - PK
> PatientID,FirstName,LastName,DOB,Gender,Hospital,MA,CaseStatus,Diagnosis,Address,City,Zip,county,MFirstName(mom),MLastName,MAddress,MCity,MState,MZip,MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father),FLastName,FAddress,FCity,FState,FZip,FHomePhone,FcellPhone,CurrentPed,FormerPed,GenCenter,Geneticist,CaseManager,CaseManagement
>
> tblPediatrician_1 and also tblPediatrician_2
> PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax
>
> tblGenCenter
> center_Id-PK,Name,Address,City,state,zip,phone,Fax
>
> tblGencounselor
> CounselorID-PK,FName,LName,Phone,Pager,Fax
>
> tblCaseMgr
> MgrID-Pk,FName,LName,Phone,Pager,Fax
>
> tblCaseMgmt
> MgmtId-Pk,Name, address,........
>
> tblInsurance
> InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone,fax,insuranceName,policyno,CS_Phone
>
> tblInsuranceType
> InsuranceType-ID-Pk,RegID,InsuranceID
>
> tblGuardian
> guardianId-pk,Refdate,type,fName,LName,address,state,city,zip,hPhone,Cphone,wphone
>
> tblpatientguardian
> patguardianID-Pk
> Regid
> GuardianID

Hi!

Thanks for pointing out. In fact, I have only one tblPediatricians. I was
looking at the relationship diagram while typing the previous message and
made such a mistake. Those two relationships will be pointing to CurrentPed
and FormerPed respectively but still from the main tblPediatrician.

I am trying to normalize as much as possible. I put the mother and father in
the main tblPatient being one to one. I thought, I have a separate
tblGuardian for capturing stepmom and dad or grandparent.

Thanks!


"Jerry Whittle" wrote:

> >>tblPediatrician_1 and also tblPediatrician_2
>
> Big mistake. All your Pediatricians should be in the same table. What if you
> have a 3rd Pediatrician?
>
> Also putting in all the mother and father info in the tblPatients could be a
> mistake. (1) what about other relatives that might have legal rights such as
> stepfathers and grandparents? (2) What if two siblings are also patients? It
> might be better to have a tblRelatives. Of course with as few records as you
> suggest, you could denormalize it.
>
> Here's something to consider: Has anyone looked at trade journals or other
> resources to see if someone already has a product that will meet your needs?
> It will almost always be faster and cheaper to buy and off-the-shelf solution
> than build your own.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Simba" wrote:
>
> > Being a newbie to access, I have been given a task of creating a db for the
> > patient management and tracking. This DB will have around 50 patients per
> > year. It will contain the history of patient's contact info, pediatrician,
> > case manager,Genetic center info, Insurance info and so on. I have created
> > the tables. pls review them and suggest me your ideas to improve the DB, as
> > mine maynot be best.
> >
> > tblPatients
> > RegID - PK
> > PatientID,FirstName,LastName,DOB,Gender,Hospital,MA,CaseStatus,Diagnosis,Address,City,Zip,county,MFirstName(mom),MLastName,MAddress,MCity,MState,MZip,MHomePhone,MCellPhone,MWorkPhone,FFirstName(Father),FLastName,FAddress,FCity,FState,FZip,FHomePhone,FcellPhone,CurrentPed,FormerPed,GenCenter,Geneticist,CaseManager,CaseManagement
> >
> > tblPediatrician_1 and also tblPediatrician_2
> > PedID-PK,LastName,FirstName,MI, Practicie,Address,City,State,Zip,Phone,Fax
> >
> > tblGenCenter
> > center_Id-PK,Name,Address,City,state,zip,phone,Fax
> >
> > tblGencounselor
> > CounselorID-PK,FName,LName,Phone,Pager,Fax
> >
> > tblCaseMgr
> > MgrID-Pk,FName,LName,Phone,Pager,Fax
> >
> > tblCaseMgmt
> > MgmtId-Pk,Name, address,........
> >
> > tblInsurance
> > InsuranceID-Pk,Type,AgencyName,ContactFName,ContactLName,phone,fax,insuranceName,policyno,CS_Phone
> >
> > tblInsuranceType
> > InsuranceType-ID-Pk,RegID,InsuranceID
> >
> > tblGuardian
> > guardianId-pk,Refdate,type,fName,LName,address,state,city,zip,hPhone,Cphone,wphone
> >
> > tblpatientguardian
> > patguardianID-Pk
> > Regid
> > GuardianID