Setting up a database (1 Viewer)

[andy_49]

The school where I work is trying to set up a system where students swap credits for prizes. The students earn credits from their behaviour etc and these would need to be imported on a regular basis. (easy) The way the school have started seems very slow and not very efficient so I thought I'd investigate an access approach.

I have 4 tables
table1 - pupil info, names
table 2 - Credits and losses from the student account
table 3 orders - date of order, what the students has requested from the available prizes
table 4 prizes - a list of all the prizes and the credits the students need to be able to get the prize.

So my "wall" is how best to set up the following:

teacher enters which students have "bought" a prize and which prize

Doesn't need to be that complex. I have quite a bit of Access knowledge and VB too

Can anyone help please

 

[theDBguy]

Hi. Welcome to AWF!

One way is to use a form/subform setup. The main form is based on the pupils table, one subform is based on their points, and another subform based on their orders.

 

[oleronesoftwares]

The school where I work is trying to set up a system where students swap credits for prizes. The students earn credits from their behaviour etc and these would need to be imported on a regular basis. (easy) The way the school have started seems very slow and not very efficient so I thought I'd investigate an access approach.

I have 4 tables
table1 - pupil info, names
table 2 - Credits and losses from the student account
table 3 orders - date of order, what the students has requested from the available prizes
table 4 prizes - a list of all the prizes and the credits the students need to be able to get the prize.

A few questions i want to ask

1. When are the prizes given, is it every term or any time

2. Is there a conversion formular(e.g a certain number of credits= a certain number of prizes)

3. Why keep losses count, if losses foes not convert to any thing.


You can achieve it below
1. A behavioural table with credits allocated to each(this is a base/set up table)
2. Once a student behaves well, you pick the student id,type of behaviour(combo box),number of credits is automatically calculated(credits
allocated). There will be a credits awarded field, this is different from credit allocated field
Step 2 above will happen in another table for credits.
3. Another base table for credits to prize, this will list how many credits is equal to a prize

Finally you can now create the orders table where the student can order for a prize.
Once the prize has been given , the following will happen

4. The system knows how many credits is equal to the prize.
5. An insert statement is created to insert into the base table in step 2, the student name, credits awarded(this will be inserted into the credits awarded table,date awarded)

You can now run select statement to see available credits for each student, you will query table in step 2 above.

 

[The_Doc_Man]

Your overall idea doesn't look too bad at all. At the overview level, I might have done something similar though not exactly identical.

Your "balance" table as it is shown in your relationships image is not exactly what I might have done. Yes, you need a table to track a student's credit-related events. When you say "losses" - can that ALSO be due to behavior? I.e. a helpful student gets rewarded, a disruptive student gets punished? If so, your student credit transaction table has at least THREE transaction types: merits, demerits, and prize orders. I would have a table with student, date, and positive or negative credits plus either a code field or a literal text field to explain why that credit amount was involved. Your initial design would give multiple columns to something that in the end will only be a single number - how many credits does the student have? My suggested style of a credit/loss table lets you use a simple SUM/GROUP-BY style of query to give you any student's instantaneous credits balance. A DSum on this table with a student's ID as the criterion would also give you this balance easily.

The idea of an orders table gives you a good source of history, and having a separate prize/cost table is also quite useful. Your starting design is very good in that it is clear that you are at least somewhat familiar with normalization. Usually, someone attacking this kind of problem wouldn't have made the data separations you have, which is how I know you have at least some experience.

Of course, with any multi-table system, the devil will be in the details. When you import the credits, just be sure to not "double dip" anything. The most complex part of the prize system will be a form that allows you to do lookups on prizes/costs AND lookups (computations) on students and their credit balances, plus you need to create a "prize order" transaction.

 

[Pat Hartman]

eacher enters which students have "bought" a prize and which prize

Isn't that the data in table 3?

 

[Uncle Gizmo]

Your overall idea doesn't look too bad at all.

That post offers brilliant advice from Richard. As usual!!!

 

[andy_49]

Your overall idea doesn't look too bad at all. At the overview level, I might have done something similar though not exactly identical.

Your "balance" table as it is shown in your relationships image is not exactly what I might have done. Yes, you need a table to track a student's credit-related events. When you say "losses" - can that ALSO be due to behavior? I.e. a helpful student gets rewarded, a disruptive student gets punished? If so, your student credit transaction table has at least THREE transaction types: merits, demerits, and prize orders. I would have a table with student, date, and positive or negative credits plus either a code field or a literal text field to explain why that credit amount was involved. Your initial design would give multiple columns to something that in the end will only be a single number - how many credits does the student have? My suggested style of a credit/loss table lets you use a simple SUM/GROUP-BY style of query to give you any student's instantaneous credits balance. A DSum on this table with a student's ID as the criterion would also give you this balance easily.

The idea of an orders table gives you a good source of history, and having a separate prize/cost table is also quite useful. Your starting design is very good in that it is clear that you are at least somewhat familiar with normalization. Usually, someone attacking this kind of problem wouldn't have made the data separations you have, which is how I know you have at least some experience.

Of course, with any multi-table system, the devil will be in the details. When you import the credits, just be sure to not "double dip" anything. The most complex part of the prize system will be a form that allows you to do lookups on prizes/costs AND lookups (computations) on students and their credit balances, plus you need to create a "prize order" transaction.

The losses are more like what the pupils have swapped. Eg if they have 200 credits they can spend 150 of them on a football. The loss would then be 150 leaving a balance of 50. Each month their credits would be updated with a new value from external software. This would not ta,e into account the fact that some had been swapped as the external system cannot deal with this (ive no control over this either)

 

[andy_49]

Thank you so much for all your feedback. Looks like the form/subform is my next route after some fine tuning to my tables.

 

[Pat Hartman]

Is the external system sending you only credits added the previous month or is it sending a running sum of all credits ever added?