Creating DB to track same group of people

[Ray Peterson]

I need to track a few different categories for the same 30-40 people. For example, say I'm a teacher and I want to keep track of test results, quiz scores, extra credit, special projects, research papers, etc. for the same 30-40 students as an ongoing project. Right now my field names are those I already listed, with first name and last name being 2 more. So there are 20 records with 1 name, 25 with another, etc. I'd like to design a form where I can search one name and have multi-tabs with the different categories and have the various records for each person listed. I've always worked with db's where each record is unique, and I'm not sure how best to go about it this way - or if that's sort of the opposite of what Access is intended for?



Thanks in advance.

[ItsMe]

What I usually do is start by identifying the various entities. Then I create a diagram that illustrates how the various entities relate to each other. For instance, ExamType could be an entity so I would draw a rectangle on a piece of paper and title the rectangle tblExamType. I would try to imagine what the different exam types could be and maybe list a couple of them on a separate piece of paper. Looking at a short list of exam types, I will try to imagine different attributes of my entity, exam types. Maybe some attributes would be ExamName, ExamDescription, ExamVersion. I would then go back to the rectangle on the first piece of paper and list out the attributes under the title, 'tblExamType'.

As I add additional entities to my Entity Relationship Diagram, I will draw relationships, connecting the entities on their Keys.

[Ray Peterson]

Thank you, that's really helpful. The db's I've worked with before have all been very flat, I'm a bit new to db's heavy on relationships so I'll have to do some research on that.

[ItsMe]

Here is a 15 min. video that might help.
https://www.youtube.com/watch?v=-fQ-bRllhXc

[Homegrownandy]

You said you want tabs with different categorys, It sounds to me like you already understand the relationships if this is the case. Your tabs would represent tables on the database. Each one relating to the teacher. If you could give an example of the data that may relate to a teacher we can help further.

the teacher information should be in my opinion just information that is specific to them. Other info such as the test results will be stored in a new table. But is that all you need there? just a test result?

each teacher will have a unique ID, then in your test result table... you would have a field called teacher ID, then a field called test result.

EG:
1-75%
1-50%

that shows the first teacher in your table has two test results. Obviously these records need a unique ID too but hopefully you can visualise how to structure it now. assuming thats how you want it.

[Ray Peterson]

Thanks, Homegrownandy. So in tblTeachers I have TeacherID, TeacherLastName, TeacherFirstName as fields.

I have 4 other tables: tblTests, tblQuizzes, tblProjects, tblExtraCredit with fields: TeacherID, TeacherLastName, TeacherFirstName, StudentID, StudentLastName, StudentFirstName, Test(/Quiz/etc)Date, TestCategory, Grade, etc. I do anticipate needing to have all that info together at some point, but I can just do a union table for that. I have a one-to-many relationship between TeacherID in tblTeachers and StudentID in the 4 different category tables.

I've started with tblTests and created a subform - can a subform be multi-tabbed?

[HiTechCoach]

I have 4 other tables: tblTests, tblQuizzes, tblProjects, tblExtraCredit with fields:

I would recommend you to make only one table with a field for type. Something like tblActivity.

Avoid naing tables what yuo be data in a field.

with fields: TeacherID, TeacherLastName, TeacherFirstName, StudentID, StudentLastName, StudentFirstName, Test(/Quiz/etc)Date, TestCategory, Grade, etc. I do anticipate needing to have all that info together at some point, but I can just do a union table for that. I have a one-to-many relationship between TeacherID in tblTeachers and StudentID in the 4 different category tables.

TeacherLastName and TeacherFirstName should only be store in the teach table. Only the TeacherID is stored in related tables. When you need the name you look it up.

same is true for StudentLastName and StudentFirstName.

To get each category on a separate tab you would create multiple sud forms that filter the data for the Activity to the desired category

I would also make a single table for all people.