Designing a Grade-Specific School Supply DB That Calculates Supplies in Total For Purchase, Distro

[Sanden]

Helping my mother with her ministry where we give away school supplies to underprivileged children in our community. Rather than trying to do an Excel spreadsheet again which has become very confusing and we ended up missing items last year, I thought we would try Access so we could modify the tables annually and have it hopefully spit out a nice shopping list for my senior mom and other senior ladies in our ministry with a total amount expected. However, being new, I need some (read: a lot of) help.



The tables thus far are simple. Parent table is a 1:M to Student table. This is where it gets dicey in my head. We have grades K-12, and each student of course can only be in one grade, but each grade can have many students. Each grade should have a separate table for us to maintain as the supply list is extensive for each grade and gosh darn it, they specify on separate lines, 6 pencils, then further down the list if they are in a specific course, then another 12 pencils, the same for pens, highlighters, and even boxes of Kleenex (12 boxes, mind you!) you get the picture... Ideally, we need for this whole DB to calculate the total number of pencils, pens, packs of papers, etc. each time a student is added to that grade, so that in July when we do our final budget? We can see how much money we have versus what we can purchase. When we verify, we may need to check to see what courses a student is in and this may require some grades to have sub-tables, but for now, I just need to understand how to do the Student <--> Grade

I do hope I am making sense. Help to do this project will be repaid in cookies (not the HTML variety) as we're a poor ministry. But know that we can bake!~

Blessings,

Sanden & Mom
Half An Apple Ministry

[June7]

Do you really need to enter specific student info (names, grade, room assignment)? If all you care about is quantity of supplies then really just need to know number of students in each class. The teacher name doesn't even seem important.

Separate tables for each grade should not be needed and in fact would probably be problematic.

I envision at minimum:

tblSupplies
SupplyID
SupplyDesc
Unit

tblClasses
RoomNo
Grade
QtyStudents

tblSuppliesLists
Grade
SupplyID
Quantity

Now build a report that joins the tables and use report Grouping & Sorting features and aggregate calcs in group sections.

If you do have to itemize supplies per student, the db structure will get much more complicated (that 'special classes' requirement sounds like real trouble).

[Sanden]

Actually, yes. We need to know Parent/Guardian for the Student because award is made based on their income (the parents themselves are the qualifiers and this is cross-verified). So I have:

table PARENT
(PK)PARENT_ID
PARENT_LAST
PARENT_FIRST
PHONE

table STUDENT
(PK)STUDENT_ID
STUDENT_LAST
STUDENT_FIRST
STUDENT_GRADE
(FK)PARENT_ID (?)

Then for each grade I would need to build it as such:
table 3rd Grade
(PK)3rd_GRADE
QUANTITY
ITEM
COST_EACH
TOTAL

Then have it all tally up all items from all grades and all items by cost.

So how to bring them all into a single accrued table that calculates total number of pencils for all grades? Because next year, 10th grade may decide they don't want wood pencile, but 4 packs of mechanical pencils. This is what has been so confusing for my mom is trying to redo all the lists and see what they can finance for each grade equitably. By separating items as they are listed by grade list, they have a better idea how they can help each student by cost, then they can also look at a total overall by how much money they have at the end of June from contributions and how much we have raised and can afford to buy.

[June7]

Do not have separate table for each grade. Have one table with another field for Grade.

Do you want multiple years or will you discard the data each year and start with empty tables?

Since you want to have individual student records, will have to do aggregate query that does a count of students for each grade and join that query to tblSuppliesLists for calculating totals.

[DubCap01]

Senden, have sent you a private message on this subject, please read

[DubCap01]

I would modify the Grades table to allow for inventory control. For instance, Tissues come in a box ( a one-off), whereas pencils can come in a box of twelve (Box12) but can be distributed perhaps in quantities of only four or five or six per student...

You might need a Stock table to allow for this... (six boxes of pencils, 72 pencils, four, or five, or six pencils per student = enough for 8 or 10 or 12 students)
Just a thought from a person who has worked in Supply/Inventory/Logistics for over forty years