[mso] Re: Sorry Very Long Setting up DB ... Naming and Relations Access 2K :VSMail mx3
- From: James LaBorde <jlaborde@xxxxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Tue, 31 Dec 2002 14:08:24 -0800
April,
That second commandment is a little misleading. From what I usderstand,
they are referring not necessarily to a lookup table but lookups. The idea
is to do it at the form level and have it use a table for its source rather
than listing the items internally. Then provide a way for this table to be
maintained by someone at the end users site. This way if they decide they
want to add a marriage status or something like that, they can do that. The
lookups that are evil that are being referred to are the ones you set up at
the table level that are merely lists rather than using a table for a
source. I know, I was very worries about that when I first read it because
I use them fairly regularly.
As for the second part of your question. The types of service will
definitely repeat and would probably be best as a lookup table. Also
consider how you will handle the possibility that a client will require more
than one type of service. All of the fields that will only be entered once
and don't duplicate should be in the table. All of your
medicare/medcare/ins pol/group# info could go in the main table on this
basis. The Doctor field could be a lookup based on the Doctor table which
would have all of the Doctor's info in it.
James
-----Original Message-----
From: 4office [mailto:4office@xxxxxxxxxxxxx]
Sent: Tuesday, December 31, 2002 1:29 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
James, Thanks for all your help... Hopefully you wont mind me picking your
brain as I do this... Ok you clear some things up for me... but you did also
muddy the water for me... cuz you reminded me that while yes, I added the
ClientID (txtCltID) to each table, I forgot to create an ID for say the
Doctor or Insurance Carrier... and I did not add those fields to my original
Client table... Also, since I just read (from I think a link you just sent
me to mvp.org/access... that my love for lookup tables is BAD BAD BAD....)
What is the easiest and correct way for the end user to either choose an
existing say doctor or add a new one when looking at the client table/form??
Is it better to just link it on the form side and not on the table side like
I do with lookup tables??
Ok so that also means that the Table with the Types of service needed, and
the Hours/Days Requested, and the summary/assessment stuff and the
medicare/medcare/ins policy/Group# Really could all be on the original
Client Table with only the name of the Ins Carrier or Doctors'/Phone/Address
on a separate table? I'm I getting this right?? The Responsible Party is
it's own table, but only used when they are not checked (on the Client's
Primary Table) as being the same, Right?? Same for the and the Contact party
which I should separate from the Responsible Party, with a check box about
whether they are the contact also...
Remember:
>>The Responsible Party could be the Client but may not be the
Contact
Person
Contact Person's will never be the Client but could be the
Responsible
party
How do I eliminate redundancy, or can I?? (Those yes/no Questions??
If
Responsible Party is NOT the Client these fields are required, if
Contact
Name is NOT Responsible Party these fields are required... Ok then
how do I
make sure they don't accidentally check both?? Saying the Contact is
the
Responsible Party who is the Client (the Contact can not be the
Client)<<
On the Hospitalization table:
tblHospitalization
txtCltID (this would be the Primary Key)
dteEnter (This would be the foreign Key)
dteDischarged
cboDisposition (This will be a lookup of choices ie "Recover no more care
needed" "When to Nursing Home" "Expired")
and this should remain a different table since a client (Patient) can go in
and out of the hospital, I'm Learning!
Ok so I have another field that is specific to each client it is their
Ambulation Status (There is only 1 of 5 possible answers) Instead of
creating a lookup table I should just make it a text field and then on the
form create a combo-box with those 5 possible answers, Right? Ok, by doing
this are they limited to only those 5 possible answer or can they add a
sixth if ever there becomes one (Will this now make the list 6 options for
the future?)
Am I asking to many questions for one posting... you know the saying, one
question, begets another....
THANKS FOR ALL YOUR HELP, I very much want to get this (right) the first
time... Sorry if I am making you repeat your self... I just want to be sure
that I am digesting what your telling me and not misconstruing what your
saying with my obvious bad habits!
April
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of James LaBorde
Sent: Tuesday, December 31, 2002 3:37 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
April,
You will want to separate the data that will duplicate. Doctors will likely
be their own table since they may have more than one patient. Any data that
is particular to the doctor will go in their table and a fk will be in the
client table listing which doctor they have. Remember, the particular data
has to relate directly to the primary key. Will the patient see more than
one doctor? You may want a visits table that would be separate from all of
the others. You still want your separate tables if the data will not refer
uniquely to the primary key. The additional tables you have with billing
and mailing addresses could go either way. Insurance carriers again will
duplicate across clients and will likely have data of their own. It would
work to have a table for them and a single reference to it in your client
table.
I was merely trying to point out the fact that you can have a large number
of fields in a table that refer specifically to the primary key. We have a
number of tables in our operational database that have over 100 fields per
record.
In your question you list separate entities in each question. That data is
important but if you have data specific to those separate entities you will
want to separate them. That way you only have one listing for each
insurance carrier and have minimal problems of misspellings or typos in the
address. Think of all the fields you need to include and ask yourself what
the field refers to. If it refers to the primary key, then it belongs in
the table, if it refers to another field in your table, it should probably
be its own table with a foreign key designated for it. In your Insurance
carriers example, insurance carrier refers to the primary key of patient,
but insurance carrier address refers to insurance carrier. So you would
include an Insurance Carriers table and have a field in your patients table
that referred to the specific insurance carrier.
I hope I am helping with clearing this up, rather than making the waters
muddier.
James
-----Original Message-----
From: 4office [mailto:4office@xxxxxxxxxxxxx]
Sent: Tuesday, December 31, 2002 11:41 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
James,
Ok then are you saying that it is ok to put ALL the information that I have
broken up on the 7 tables that I listed before (and the approx 5 that I did
not spell out) into ONE table, since they are all information related to the
"Client" : Who are they, who will pay, who is their doctor, who is their
insurance carrier....
April
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of James LaBorde
Sent: Tuesday, December 31, 2002 2:07 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
40ffice (sorry, you didn't use your name),
The easy answer to your question is that the limit to the number of columns
in a single table is only limited by size. The best way to determine if you
should break up your tables is to ask yourself if everything in the table
refers to the Primary Key. In the case of your first table, that would be
the client. One thing to remember about your look-ups, don't embed the
data. Make a small table that houses the data and make sure that somewhere
in your application you give a user (an Admin) the ability to add to that
list if necessary. You are correct about the age being able to be
calculated. There are some issues with calculating age though so be
careful. You can make it display in any format you choose, although it may
require that you do a little coding to get the exact format you wish.
As far as your redundancy issues. You can leave certain fields inactive and
unaccessible until the appropriate box is checked. You are venturing into
territory where you will need the vba coding more and more though.
I hope this helps. If you have any more specific questions feel free to
ask.
One resource that should be of great help will be
http://www.mvps.org/access/
James
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
