DB design questions

[rick m]

My daughter's school asked me to design a database to track immunization
records. This freebie will have the following fields:
ID (autonumber)
FirstName
LastName
DOB

It will also have to have fields for each vaccine (7 of them) along with
fields for the times kids are supposed to have gotten their shots, so
Hep will need to have a the following fields: HEPBbirth, HEPB4month,
HEPB6month.

Would "best practice" be to have a table for each vaccine, so HEPB is
it's own table with the ID number linked from the "master table" along
with the vaccine times? Or should I just lump them all into one table?
There are 40 kids, each year 6 leave and 6 or so come on board.

My idea would be to have the following tables:
MasterTable (ID, FirstName, LastName, MedWaiver, RelWaiver)
HEPB (ID, birth, 4mo, 6mo)
DTAP (ID, 2mo, 4mo, 6mo, 1yr, 4yr)
NIB (ID, 2mo, 4mo, 6mo, 1yr)
POLIO (ID, 4mo, 1yr, 4yr)
MMR (ID, 1yr, 4yr)
VARICELLA (ID, 1yr)
PREUNAR (ID, 1yr)

The "ID" is what will link them together. All of the fields except for
ID are short dates. MedWaiver and RelWaiver are checkboxes for medical
and religious waivers.

Lastly, when I do a report, can I have it print the first name and
initial of the last name? They kids are "sorted" by first name and last
initial.

Thanks much,
rick

 

[Marc]

Hi

My daughter's school asked me to design a database to track immunization
records. This freebie will have the following fields: lol
ID (autonumber)
FirstName
LastName
DOB

It will also have to have fields for each vaccine (7 of them) along with
fields for the times kids are supposed to have gotten their shots, so Hep
will need to have a the following fields: HEPBbirth, HEPB4month,
HEPB6month.

Would "best practice" be to have a table for each vaccine, so HEPB is it's
own table with the ID number linked from the "master table" along with the
vaccine times? Or should I just lump them all into one table? There are 40
kids, each year 6 leave and 6 or so come on board.

My idea would be to have the following tables:
MasterTable (ID, FirstName, LastName, MedWaiver, RelWaiver) and DOB
HEPB (ID, birth, 4mo, 6mo)

Rather (and for remaining tables) InjectID, InjectType (HEP, DTAP,
etc,ChildId, DateGiven (then you can calulcate the age) This way you don't
have to have separate tables for each type, and it will make reporting
easier.

DTAP (ID, 2mo, 4mo, 6mo, 1yr, 4yr)
NIB (ID, 2mo, 4mo, 6mo, 1yr)
POLIO (ID, 4mo, 1yr, 4yr)
MMR (ID, 1yr, 4yr)
VARICELLA (ID, 1yr)
PREUNAR (ID, 1yr)

The "ID" is what will link them together. All of the fields except for ID
are short dates. MedWaiver and RelWaiver are checkboxes for medical and
religious waivers.

Lastly, when I do a report, can I have it print the first name and initial
of the last name? They kids are "sorted" by first name and last initial.

After going through the wizard to format the report etc
Select the report record source in the report properties box - click on the
ellipses to the righ, the query builder should come up. Where FirstName is
in the field change this to PrintName: FirstName & " " & Left(Surname,1)
Click ok, save. Then go to the sort order in the report properties box, and
enter PrintName.

HTH
Marc

 

[Triton]

So from you message at first let me get the problem straight.
you have one student and that student could have 7 kind of vaccines ..

the way you could do is
Create a table called Student.
Student table will have these fields

StudentID ( autonumber as datatype)primarykey
Student_Name (TEXT)
Student_Lastname(TEXT)
dob(TEXT or Date/time)

Create another table called vaccines.
VaccineID(primarykey)
StudentID(Number) You can join autonumber with Number.
Vaccine_Type1
Vaccine_Type2
vaccine_Type3
Vaccine_Type4
Vaccine_Type5
Vaccine_Type6
Vaccine_Type7

Now you will create a relation between student table and Vaccine table. The
type of relation you would need is (ONE-To-MANY). Because One student could
have 7-types of vaccines or its data. or information.

so lets say student MARY smith could have 7 vaccines data information.

Now you might want to have a field called Active (text) in your student
table which would take value YES/NO ....the reason is since you said student
leave and come..you need a way to keep track of which are the current
astudents in the school and who has left...so that when you print a report
access would only print those students who are currently enrolled and discard
the one who are no more in the school...

to print the report of students you need to create a query
Select student.studentID, student.student_firstname, student_lastname,
student.DOB, vaccine.vaccine_type1,vaccine.vaccine_type2 and so forth...where
student.active = yes
orderby dob ...or student lastname
this above query has syntax error so do not try to paste it...if you need
the right one you need to send me the table and info..and i can write you a
query for your report...
email me (e-mail address removed) if needed.

 

[Duane Hookom]

Oops. I think most of the seasoned codgers here would cringe at the thought
of a table with fields like XXX_1, XXX_2, XXX_3, XXX_4,...
Students should be in one table with every student creating a unique record.
There should be another table of Vaccine Types with each type of vaccine
creating its own record. This table could possibly store the relative date
of a vaccine or frequency etc. A third table could list Students with
Vaccine Types. You could actually add a record for each student for each
vaccine type. A status field could indicate "Required", "Un-necessary",...
There could also be a date field for the date the vaccine was administered.
This field would be blank for students not yet vacinated.

 

[rick m]

Oops. I think most of the seasoned codgers here would cringe at the thought
of a table with fields like XXX_1, XXX_2, XXX_3, XXX_4,...
Students should be in one table with every student creating a unique record.
There should be another table of Vaccine Types with each type of vaccine
creating its own record. This table could possibly store the relative date
of a vaccine or frequency etc. A third table could list Students with
Vaccine Types. You could actually add a record for each student for each
vaccine type. A status field could indicate "Required", "Un-necessary",...
There could also be a date field for the date the vaccine was administered.
This field would be blank for students not yet vacinated.

All of the fields will need to have something, all of these vaccines are
required. Should I put them all into one table? Like have a table for
HepB, a table for Polio, etc? Or should I just create tables for the
recommended vaccine date, it 2mo, 4mo, 6mo, etc.

I could always make a huge flat file.

 

[Duane Hookom]

I think I described three tables in my previous post. Access is a
"relational" database and should be used that way. Do not create fields that
have either the names of vacines or of months (time periods).

 

[Bruce]

You should be able to describe each table in a single sentence without using
the word "and". (PK = primary key; FK = foreign key). In your case you
would prpbably have tblStudent (StudentID (PK), FirstName, LastName, DOB),
tblVaccine (VaccineID (PK), VaccineType, Frequency). Each student could have
many vaccinations, and each type of vaccine would be administered to many
students. That is many-to-many, so you need a junction table (aka resolver
table). The junction table (tblVaccinationRecord) could contain its own PK
and the FKs from the other two tables, along with any other necessary fields
such as vaccination date. The FKs would be of data type Number (assuming
that the PKs are autonumber). If you base a form (frmStudent) based on
tblStudent and base a subform (fsubVaccinationRecord) on
tblVaccinationRecord, you could look at a student's name and see all of the
related vaccinations. Set the default view to Continuous Form (or datasheet)
for fsubVaccinationRecord.
Quick outline view of how this could work: In order to get VaccineID into
tblVaccinationRecord you could place a combo box onto fsubVaccinationRecord.
The combo box row source would be a query based on tblVaccine. You would
select the vaccine from the drop-down list (you could use the combo box
wizard), and add the date. Use the subform to create a new record for the
next vaccination. Move to the next student and repeat the process.
There are all sorts of refinements you could add, but something like this
would get you started. Form you could prepare a report of upcoming
vaccinations, which could derive its information by comparing the last date
for a particular vaccination with the frequency as specified in tblVaccine.
You could view a student's vaccination history, or how many vaccinations
remaih, or whatever.
I think you asked about having the name appear as Last, First MI. You could
do that by creating a query based on tblStudent. In design view add
something like this at the top of a new field: FullName: [LastName] & ", "
& [FirstName] & " " & [MI]. This assumes you have fields in tblStudent
called [LastName], [FirstName] and [MI]. Whenever you need Last, First just
select FullName form the field list in qryStudent.
I tend to name queries, forms, etc. similarly to the table name. It is not
required, but I think it makes things a lot easier. I recommend naming combo
boxes, text boxes etc. according to a naming convention. Google 'Access
"naming convention" ' (use the double quotes, but not the single ones - that
should work, but I'm not certain) for more on this.
Remember, tables are for storing data in the form of records. Queries are
for arranging and working with data (for instance, performing calculations or
as described above for FullName. Forms are for creating, viewing, and
editing records. Reports are for viewing and printing. Reports offer all
sorts of options for grouping and sorting, once you get your tables set up.
Start with the tables, then create the relationships (Tools >
Relationships). Experiment with adding data directly to the tables before
you create forms. After you create forms, go back to the tables and look at
the relationships again. Get used to how all of this works.
A caveat: this is pretty important stuff you're keeping track of, so test
extensively before deploying the database. Maintain parallel paper records
(or whatever is happening now) for a while after deploying the database.
Don't be in a hurry. You can do this, but you are jumping into a project
somewhat more complex than might have been recommended for a first database.