If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DB design questions
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 |
#2
|
|||
|
|||
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 Thanks much, rick |
#3
|
|||
|
|||
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 if needed. "rick m" wrote: 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 |
#4
|
|||
|
|||
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. -- Duane Hookom MS Access MVP "Triton" wrote in message ... 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 if needed. "rick m" wrote: 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 |
#5
|
|||
|
|||
Duane Hookom wrote:
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. |
#6
|
|||
|
|||
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). -- Duane Hookom MS Access MVP "rick m" wrote in message ... Duane Hookom wrote: 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. " |
#7
|
|||
|
|||
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. "rick m" wrote: Duane Hookom wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
FAQ - frequently asked questions - please read before posting - June posting unofficial | Charles Kenyon | New Users | 4 | June 28th, 2004 02:58 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - May | Charles Kenyon | Tables | 6 | May 24th, 2004 05:55 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - May | Charles Kenyon | Page Layout | 6 | May 24th, 2004 05:55 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - April | Charles Kenyon | Tables | 0 | April 26th, 2004 03:34 PM |
FAQ - Frequently Asked Questions - unofficial - please read before posting - April | Charles Kenyon | Page Layout | 0 | April 26th, 2004 03:34 PM |