Student Participation Points DB (1 Viewer)

I need some advice about a project for my hubby. I have mucked around with Access for probably around 10 years, and I can usually find the answers to what I need in forums like this and other web places, or if I'm lucky someone better with it works in another department. This time though I think I'm in over my head!

So Hubby is a teacher and advisor to a club at his school. His students have to reach a certain number or percentage of points to be able to participate in District, State, and National conference. He also just needs a database with their info, such as contact, grade, did they pay their dues, etc.

Tables I have so far:
tblFBLA_Membership-all the students, contact information, etc.

tblEvent_Points this contains the different categories that events can fall into and how many points that category is worth, such as Regular Meeting 50 points.

tblEvents this contains the actual events, its category and how many points it is, for example January Meeting is Regular Meeting and is worth 50 points.

tblBonus_Points is used for when kids get certain ranks at district and state conference, since these points aren't available to every student these points are not to used to calculate total
possible points available when I need to calculate percentage.

tblCertification_Points contains "BAA" levels, the points worth and the grade level the student must achieve that level by, for example by the end of grade 10 a student have completed
BAA Future and BAA Business. What complicates things with this is that since these are required certifications, if a student hasn't met their grade requirement those points have to still be used to calculate the total possible points available.

tblEvent_Participation has the event name, the student who attended and the points. This is the one I will use to calculate how many points a student has accumulated at "regular" events.

I have a query based on tbleEvent_Participation that sums how many points each student has received, and one based on tblEvents that sums how many points are possible based on these "regular" events.

My snag is with a few different things. The certifications requirements, and the percentages.

I have tried a few different ways of querying and I can't get that percentage to calculate.

And I'm not even sure how to begin to go about getting the certification requirements to work the way he wants.

Can you upload your database along with some sample data in it?

Also, could you mock up a report you hope to end up with? Something to let me know what the target is.

If you can walk me through how to upload I can try.

As far as what the report needs to look like, something like below I think

Student: John Smith
Student ID:12345
Class: 10
{other general info straight from data entry, telephone dues paid etc}
Bonus Points: Excellent at District <-----only print the bonus categories corresponding to the check boxes on the membership form
Certifications: BAA Future, BAA Business <------Based on check boxes on membership form
Participation points: 365 <----Sum total of all student's Event points, bonus points and Certification points.
Participation %: 75% <---------Student's total participation points divided by total possible for that student.

I would like the Participation points and % to be able to be displayed on the membership form on screen as well.

At quick glance, I see something that is perhaps a bit redundant. If you do not understand database normalization, do a Web search for "database normalization" or a search within this forum simply for "normalization."

OK, you have tblEvent_Points and tblEvents and then you have tblEvent_Participation - and ALL of these have points in them. This is a nit-pick, perhaps (or perhaps not), but you probably have a redundancy in where the points are defined.

If you have all possible event types in tblEvent_Points then this only needs a primary key for an event type-code and a number of points. (It could have a generic name for that type of event if such is appropriate.)

Then your tblEvents would only contain the event date, venue, name, and the event type code. Using a simple JOIN query, the events could have the number of points because the query would list them.

Then you have tblEvent_Participation. Again, a query gives you the list that you need to show that student "Tim" participated in event "Regular Meeting (1/14/2017)." The event, being in tblEvents, already has a code that tells you it is a regular meeting. The code leads to the event types table which tells you that a regular meeting is worth 50 points.

Once you have these JOIN queries (which you can build using the query grid pretty easily), you can use THEM as record sources for reports.

The part about certifications will depend on what CAN be certified. Address that topic a bit and you can get more advice.

Zip your database, click the Go Advanced option under the thread posting area and then use the page that opens up to upload that zip file

The_Doc_Man said:

At quick glance, I see something that is perhaps a bit redundant. If you do not understand database normalization, do a Web search for "database normalization" or a search within this forum simply for "normalization."

OK, you have tblEvent_Points and tblEvents and then you have tblEvent_Participation - and ALL of these have points in them. This is a nit-pick, perhaps (or perhaps not), but you probably have a redundancy in where the points are defined.

If you have all possible event types in tblEvent_Points then this only needs a primary key for an event type-code and a number of points. (It could have a generic name for that type of event if such is appropriate.)

Then your tblEvents would only contain the event date, venue, name, and the event type code. Using a simple JOIN query, the events could have the number of points because the query would list them.

Then you have tblEvent_Participation. Again, a query gives you the list that you need to show that student "Tim" participated in event "Regular Meeting (1/14/2017)." The event, being in tblEvents, already has a code that tells you it is a regular meeting. The code leads to the event types table which tells you that a regular meeting is worth 50 points.

Once you have these JOIN queries (which you can build using the query grid pretty easily), you can use THEM as record sources for reports.

The part about certifications will depend on what CAN be certified. Address that topic a bit and you can get more advice.

Click to expand...

Thanks for explaining that. I saw normalization referred to in a few other threads and tried to read up on it, but (probably because it was near the end of my work day) I couldn't follow the explanations.

Let me rework those things and see what I can do from there.

I'm sure I will be back!

The GOOD news is that if you search the web for database normalization, there are literally thousands of such references. My advice is to look at the source of hit, and only (at first) read the ones from teaching sources. Colleges tend to publish this stuff on their official web sites because the professors can just give the students a link.

If the first article you read doesn't make it clear, try to find another one from that huge list. Some articles will go all "set theory" at once, while others will take a more practical approach. Somewhere in out there you will find something to your liking.

The articles here in this forum are probably not bad either, and are more likely to be written from a practical viewpoint. Which may be what you need to get started. Let me offer this opinion: Database normalization is important enough as a topic that if you have to work with databases in the future, it is WORTH the time it takes to learn about it enough to be comfortable with it.

Ok I think I have managed to normalize my database. I ended up getting rid of all the forms I had already created since they referred to fields and queries that no longer existed (I got rid of all my old queries and started them over).

I think I have managed to zip and upload the database as well.

Any suggestions would be appreciated.

I have managed to build a query that will look up how many points a student should get for attending an event. I will use that as the control source for my form when I get there.

What I'm not sure how to do is the bonus points and certification points. Is that a query or will I need to build code or a macro?

This is where my "Old Programmer's Rules" come into play.

Rule #1 - If you can't do it on paper, you can't do it in Access. That's a succinct way of saying that you must (sometimes literally) map out a data flow or decision flow or SOME type of paper document that will be your "project bible." You have to know what you need to do based on an analysis of the problem. This is because the problem rules the program, not the other way around. If you have ever heard the phrase "the tail wagging the dog" - this is something that you CANNOT let happen. You have to program to the problem, not change the problem to fit what your program can do. And the way to get there is to formally define the problem first. If you can't do that, you are not ready to implement stuff in Access.

Rule #2 - Access won't tell you anything you didn't tell it first (or at least tell it HOW to tell you.) This means that you have to know what you want to get out because otherwise there is no way to be sure that you are taking in the things you need in order to ASSURE that there is a data flow line between your actual inputs and your desired outputs. Sometimes this means working backwards so that you ask yourself: If I want X, Y, and Z in this report, how will Access be able to put them there? What data do I need to gather to obtain X, Y, and Z directly or through computation?

As to using a query to be a control source for a form, there is a pitfall. Queries for reports are excellent. A query as a form's .RecordSource lays a trap. That form can change any bound field. You might need to remember that changing any field bound that way potentially updates multiple tables. Not trying to over-complicate your life here, but you might do better to have a form that drives the contents of tblEvent_Participation (which seems to be the thing driving the individual students' point count) and use combo-box lookup fields to select what event a given student actual attended.

I don't know if you know the term, but look up "junction table" - because your event participation table looks like one to me.

To answer one question directly for you:

What I'm not sure how to do is the bonus points and certification points.

Click to expand...

See Rule #1 above. Your question makes it clear that you are not seeing how this fits into your app quite yet. But I'll offer a couple of hints. From the sound of it, you can have "bonus" events in the same table as regular events, but maybe have an extra Y/N field that is Y for bonus and N for regular points. Then your queries that total up points can skip the bonuses for determining the base against which you are computing a percentage.

Certifications sound like something that would go in another junction table to define the requirements. Now this is ME talking about YOUR problem and I could be wrong from the get-go, but... Define certifications that one can have. Then have a Certification Requirements table listing the certifications. Then build a junction table between your certification requirements list and your students so you can list each student's accomplishments that meet a requirement. This is how you would ORGANIZE them. How you then determine that a student has met the requirements? THAT would probably be a query or some simple VBA to count requirements and count accomplishments and then see if they match.

JLM... nobody ever said database design is always easy. But I will make ONE blanket statement: Database design is as easy as the problem it is intended to solve - and as hard. They are two peas in the same pod.

Ok I'm a bit, actually a lot, confused. I know I shouldn't store a calculated value in a table. And now you're saying using a query as a control source on a form is not a good idea. But I need to be able to pull up a kid and tell how many points they have accumulated so far and that percentage, without having to run a report.

But I need to be able to pull up a kid and tell how many points they have accumulated so far and that percentage, without having to run a report.

Click to expand...

If you have the query, you can do a DLookup on it from the appropriate field and have the student's name in the Criteria argument of DLookup.

We usually do not recommend DLookup in reports (because you do better having the data already present with the query) but a form just "sits there and does nothing" until you do something active with it. In forms, a DLookup for a single item available through a query is almost negligible unless something is terribly wrong with the data design.

You should also note that if you do this, anything that changes that eligibility score (e.g. adding a new event) will force you to re-evaluate the score. This again will not be that noticeable because a form just SITS there most of the time anyway.

Ok so let me see if I have gotten my brain around this.....

On my from for FBLA Membership I need to have a control to display the total points a student has earned. This control is a DLookup to a query. The query should have the fields: the student's name from join table event participation, event name from the same table, the category from the events table, and the points form the point table.

Then when I run the DLookup with the criteria that the student name matches the record displayed on the form it will pull all those points and I just need to sum them.

Would I sum them with a total row in the query or in the control on the form?

Thank you all with your patience with me! I got the calculated fields on the forms to work. There is one that sums up what each student has earned, including bonuses; one that sums up how many available points there are, excluding bonuses; and one that calculates the percentage the student has earned. WHEW!!

The certification points will be more complicated I think.

There are 4 cert levels, one for each of the grade levels. So by the end of the 9th grade year the student must achieve the cert required for that grade. If they achieve certs ahead, i.e. a tenth grade cert in ninth grade, those are not bonus points, those points are added to both points earned and points available. As soon as a student is in the grade the cert points for that level should be part of the available points for that student.

I'm not sure a query can do everything I need to do with this. It seems like there should be some If..Then statements. Advice?

Forget Dlookups, and forms entirely for now. You need to focus on your tables. I've opened your tables and there's a lot of little to medium things that you need to fix before moving on.


Overall

Tables should be piles of data--don't add totals to the bottom of them and don't do that thing where they show the text value from one table but really contain a number. Just makes it harder to debug.

Short Text field lengths--all of yours are 255. Change the lengths to more align with realistic lengtsh of the values that will be held. No one has a First name that takes up that many characters.

What happens next year? From what I am seeing you are treating this as something that must be reset at some point. If set up properly that doesn't have to happen. If you set this up correctly and with an eye toward the future you can avoid cleaning it out and reseting it. Think about the future.


tblFBLA_Membership

1. Full_Name shouldn't be a field. Calculated fields shouldn't be in tables, instead you should calculate them in queries. When you want the full name of a student you create it by putting their first and last names together.

2. BAA_ fields shouldn't be fields. These too are calculated--from other data you should be able to determine their values, correct? If that's true, you don't store the value in a table, you calculated it in a query.

3. The 2 T_Shirt fields should only be 1 field. I don't really understand why the size fields is Yes/No, seems like it should be a text field (Small, Medium, Large). If it is a text field you can use it to determine if a member doesn't get a T-Shirt (Null, or No Shirt).

4. Telephone_Number should be text. The largest value a Long Integer can accomodate is 2147483647. My phone number (area code 913) wouldn't be allowed into that field.

~~~
tblEvents

5. Use better Event_Name values. How are you going to distinguish this years christmas part from next years? Same with every event. You are limiting the scope of this databases use by not thinking about how it will handle the future.


6. Event_Name should not be the primary key. Use an autonumber as the primary key. It will improve performance because numbers are smaller than text and if you ever mistype a name and realize it later its a lot easier to change.

7. Event_Category should be a numeric field. Very similar to #6 above--you should have an autonumber primary key in tblEvent_Points and then use that number her to reference a Category instead of storing text.


~~~
tblEvent_Points

8. Event_Category should not be the primary key. See #6 and #7.

9. I recommend calling this tblCategories. It's a more apt description and will eliminate confusion. You mentioned 'categories' 3 or 4 times in the post, yet I was confused because you had no table for categories.

~~~~~~

tblEvent_Participation

10. Event_Name should be numeric. Same thing again as #6 & #7. Use numbers as primary and foreign keys.

Then when I run the DLookup with the criteria that the student name matches the record displayed on the form it will pull all those points and I just need to sum them.

Click to expand...

Actually... no. Easier than that. Queries are CHEAP. Make a dedicated query that IS a summation query. Then the DLookup just looks up one field from the named summation query with the name of the student (which would be referenced in the query as a "group by" field) to facilitate the lookup.

I'm not sure a query can do everything I need to do with this. It seems like there should be some If..Then statements. Advice?

Click to expand...

You have to nail down the answer to that yourself, but consider this: It is possible for a field to be repeated in a query or to have multiple fields contribute to a single computation that will be come a single result field in the query (even thought it might have multiple contributors.)

For a possible example, if a cert has a grade requirement, you want the student in that grade to have the points available for your percentage computation. Look at this:


where you have the available points but you multiply that by a number that will either be 0 (if the student is below grade requirements) or 1 (if the student is at or above grade requirements) ... so if an "available point" requirement is above the student's current grade, it doesn't count. Perhaps this kind of thing is what you want to examine? I'm not saying this is the right answer, but it is a different way of thinking about queries to make them jump through your hoops.