[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 11:06:43 -0800
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
-----Original Message-----
From: 4office [mailto:4office@xxxxxxxxxxxxx]
Sent: Tuesday, December 31, 2002 10:23 AM
To: Office (E-mail)
Subject: [mso] Sorry Very Long Setting up DB ... Naming and Relations
Access 2K :VSMail mx3
Hope everyone had a great holiday! I am still in the planning stage of a
new db and want to check some things with you guys first (when I complete
this I will be able to go buy all the DB books I need!)
tblOne (I haven't figure out logical table name yet)
txtCltFName (Text Field) Client First Name
txtCltMName
txtCltLName
nbrCltID (Number Field) Primary Key
txtCltSpsName (Spouse's Full Name)
txtCltSS (Social Security Number)
dteCltDOB
txtCltPhone
cboMarital (Choose Marital Status from Lookup)
txtCltPhyAdd (Client Physical Address)
txtCltPhyCity
txtCltPhySt
txtCltPhyZip
txtCltPhyCounty
cbxCltMailingAdd (Yes/No - If no, I figure in the Form it would pop-up
another form to enter Different Mailing add - How do I do it? and do I make
another table for that Info, or put it here?)
cbxCltBillingAdd (Same as above)
txtRace
txtSex
txtReferred
txtDirections (to Home)
They want an AGE field, but I figure that I could add a calculating field on
the form or query a calculating field that would be something like txtAge:
=Now()-[dteCltDOB] (Can you tell I think in Excel Formulas? Would the
formula in Access be different?(Can I get the answer in the form of __Yrs
___ Mths)
This Seems like way to many fields in one table!
tblResponContact
txtCltID
cbxResponClient (Is the Responsible Party the Client Yes/No?) If no then
they will complete this table, right? See comment below
cbxConRespon (Is the Client the Responsible Party Yes/No?) See Comments
below
txtResponPriName (Client's Responsible Party Primary Name)
txtResponPriAdd
txtResponPriCity
txtResponPriSt
txtResponPriZip
txtResponPriPhone
txtResponRelation
txtResponSecName (Client's Responsible Party Secondary Name)
txtResponSecPhone
txtConPriName (Client's Contact Person Name)
txtConPriPhone
txtConPriRelation
txtConSecName
txtConSecPhone
txtConSecRelation
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)
tblThree
txtCltID
cboServiceNN (Type of Service Needed - chosen from Lookup)
cboDress (Dress Code Uniform or Street Clothes?)
cbxCltMonD (the question is Hours/Days Requested? so I am doing a Monday,
Tuesday.....Sat check box so the can check off which days the client wants a
nurse)
nbrCltMonH (Number of Hours on Monday?)
cbxCltTueD
nbrCltTueH
CbxCltWedD
nbrCltWedH
cbxCltThuD
nbrCltThuH
cbxCltFriD
nbrCltFriH
cbxCltSatD
nbrCltSatH
cbxCltSunD
nbrCltSunH
How do I put a validation rule in that checks that the days they check off
has number of hours for that day? (I know what until I make the form to ask
this question!)
tblSummaryAssessment
txtCltID
cbxSummary (Yes/No)
cbxAssessment
CbxReassess
dteReassess (Date of Reassessment)
tblInsurance
txtCltID
nbrMedicare
nbrMedicaid
txtInsName
txtInsAdd
txtInsCity
txtInsSt
txtInsPhone
txtInsCoPay
nbrInsGroup
nbrInsPolicy
tblrDoctor
txtCltID
txtHosp
txtDrPriName (Primary)
txtDrPriAdd
txtDrPriCity
txtDrPriSt
txtDrPriPhone
nbrDrPriPCP (Dr Id Number)
txtDrSecName (Secondary)
txtDrSecAdd
txtDrSecCity
txtDrSecSt
txtDrSecPhone
tblHospitalization
txtCltID
dteEnter
dteDischarged
cboDisposition (This will be a lookup of choices ie "Recover no more care
needed" "When to Nursing Home" "Expired")
Ok there are about 5 other tables that I wont bore you with. Is there some
magic answer to the question (is there a good ball park number of columns
that one table should not go over to be able to maintain normalization??)
I'm always feel like I make to many tables, but somewhere I got the
impression that 10 table with 3 fields or even 5 tables with 6 fields is
ALWAYS better then fewer tables with 10, 20 or 30 fields....
I really appreciate you guys checking my thinking on these tables.... The
next step in all of this (which probably should be in a different posting,
they want all the fields on One Form (no sub forms)... Should I do this with
a VB Form instead of an Access Form?? Can I even do this with Access
Forms?? Is there one book that I should buy that would walk me through doing
that?? (Again if this was Excel, I would know how to create the VB form and
populate the table cells and create event-driven macros that would check if
this box is checked and this box is NULL to do XYZ, but in Access I am
clueless.... )
*************************************************************
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:
