Database setup for school objects (1 Viewer)

[JStoops1]

Hello everyone

First of all, I hope I am in the right section. I have the following problem:
Each year, we ask all our teachers which school objects (highlighters, binders, pencils...) they use in their courses. Each school object has a price tag. Some objects can only be used once, i.e. our pupils should buy a binder for each course separately. Other objects, such as highlighters and pencils, can obviously be used in different courses. Later on, I want to sum all the prices. The objects puils can use in multiple courses, shoud only be counted once.

I get a table from my colleagues with three fields: Teacher, ClassGroup, Course. I want to join the Teacher field to the data above when ClassGroup and Course match.

A couple of years ago, when I had limited experience with access, I started with this project. I created a table with the following fields: ClassGroup, Course, SchoolObject, Price, IsReusable (Yes/No field). An object that is reusable, is stored once with the correct price and once with the price being 0, in order to calculate correctly. I join this table with the table mentioned above. It works fine, but it's not an effective way to work.

I hope I descibed my problem well enough. Is there anyone who can give me a general database setup? The goal is to add, delete or change data easily in the future and give me a correct calculation of the prices. I am more than happy with a possible work flow, I don't mind solving problems I encounter on my own. Hopefully some of you can help me out. Thanks!

Kind regards

JS

 

[arnelgp]

there is demo database here:
SCHOOL DATABASE | Access World Forums (access-programmers.co.uk)

 

[GaP42]

Not sure of the purpose or expected output, however the described content seems to lack the expected data structures.
At a most basic level Teachers (attributes such as name) teach one or more Classes. A Class operates for a Period (Year/Term/Semester) and has StudentEnrolment. A Class belongs to a Course (assuming here a class cannot be common to one or more courses), and a Course is run by a Subject Area (or Faculty/Department). The SchoolObjects you describe seem to be a standard item list. These would appear to me to be associated to a Course.
You gather information from the teachers of the classes - hence it is likely teachers of classes in the same course would submit identical or very similar lists. In collating the list of items across courses you would need class enrolments (at least total numbers) and be able to count reusable items only once across different courses operating in the same period.
Consider which entities above you need to have data recorded (Teachers/Class/Period/StudentEnrolment/Course/SubjectArea/SchoolObjectItems) and the data items belonging to each to meet your needs, and the needed relationships between those entities.

 

[gemma-the-husky]

Seriously, is this an educational exercise or a real need? If it's real, I just can't believe a school needs to waste time and effort tracking a few consumables.

 

[arnelgp]

Not sure of the purpose or expected output, however the described content seems to lack the expected data structures.

you can always put some demo of your own that will satisfy all requirement.

 

[JStoops1]

I think I need to add some imporant information about our school system (secondary school in Belgium).

Our school has about 50 different class groups. Each class group starts with a number (referring to the grade) and two letters (referring to what this group focuses on). Each class group has a set amount of courses (i.e. maths, English, science...). Each course is taught only by one teacher. I'm sorry for the misunderstanding, the school system is entirely different. So: class = group of pupils, course = i.e. Englisch, maths, science... There's no need to divide these courses into a trimesters, semesters...

Seriously, is this an educational exercise or a real need? If it's real, I just can't believe a school needs to waste time and effort tracking a few consumables.

This is a real need. It's important for parents to know what their children need in the beginning of a school year. We don't want parents to run to a store multiple times, pupils need to be fully prepared. This cost is added to another list, by the way, which informs parents how much the school year will cost in total (books, school objects, trips...). It's valuable information for parents, considering Belgium does not have "private schools", every school has a mix of pupils: rich, poor... transparancy is important.

 

[Pat Hartman]

Start with a table of objects, include a current price. Then a table of classes. I'm not sure teacher matters. If multiple teachers teach the same subject at the same grade level, they can agree on the tools needed. Create a class called "other" and assign all the generic tools to this one such as pens and pencils, etc.

Then you need a junction table. This will have a quantity. A writing class might need 4 special writing books for the school year.

You need a report that produces a list of tools required per student by grade year that can be given to each student and sent to local shops for reference.

You should produce a spreadsheet that you can send to local shops asking for pricing each year. Import all the pricing quotes. Your list can be an average of local prices.

 

[gemma-the-husky]

It's a long while since I went to school. Does the school provide text books, or is there a list of required books. Does the school specify uniforms etc, on the list.

If all you are talking about is stationery requirements such as pens, pencils, notebooks, drawing instruments, erasers, folders, and so on, then I am still astounded that a school needs to cost out a list of requirements. Surely all the kids have parents who went to school. Most supermarkets I've been in in France have a school section. I can't believe it's a matter of parents having to seek out specialist shops. eBay and Amazon will probably fulfil most requirements next day. Sorry, but I would not waste time with a database for something that could be put on a form letter. A database is actually likely to be quite complicated.

Even Hogwarts just specified a cauldron and a wand. I'm sure they didn't show the expected cost on a spreadsheet.

 

[561414]

If I understood correctly, you want your teachers to create a list of the objects their pupils will need. Like
teacher 1 wants erasers and pencils for course a
teacher 2 wants notebooks for course b
teacher 3 wants rulers for course c
If a student happens to have courses b and c, you want to be able to make a list with notebooks and rulers, but if it matches a and b, then erasers, pencils and rulers. Is that what you need?

You need these tables:
objects: object_pk, object_name, measurement_unit, is_reusable, price_tag
teachers: teacher_pk, first_name, last_name
courses: course_pk, course_name, teacher_fk
objects_required: object_required_pk, object_id, course_fk
students: studend_pk, first_name, last_name
class: class_pk, student_fk, course_fk

You needs these forms:
frmMain: Is a list of teachers, where you can add/delete/modify teachers. When you click on a teacher, it takes you to frmTeacherDetails;
frmTeacherDetails: Is data about the teacher and a list of courses, where you can add/delete/modify courses for that teacher. When you click on a course, it takes you to frmCourseDetails
frmCourseDetails: Is data about the course and a list of objects, where you can add/delete/modify objects for that course

In frmMain: there is also a list of students, where you can add/delete/modify students. When you click on a student, it takes you to frmStudentDetails.
frmStudentDetails: Is data about the student and a list of classes the student is involved in, where you can add/delete/modify classes. When you click on a class, it takes you to frmClassDetails
frmClassDetails: Is data about the class and a list of objects required for it.

As you can see, frmMain could have a tab control with a page for Teachers and another page for students. In the students page, next to each student name, there can be a button where you can open a report of the objects the student will need. And this is the query for the report:

 

[CarlettoFed]

Maybe if you attach the file you created it might be easier to understand and help you.

 

[Pat Hartman]

then I am still astounded that a school needs to cost out a list of requirements.

I guess you've never been to Staples or Walmart in August

The costing part may be due to charitable funding for disadvantaged students.