I need a smart way to group records by field name and criteria

[giol]

I am a complete novice that using the help file and some kind advice from this forum, managed to build a (probably awful) database.
Before i explain the problem i am facing, i should explain what the database should do at the end. I am an instructor in fighter squadron and i am trying to create a db that will allow the instructors to:
1 . grade the flight of the student using option groups (selecting from 5 options for each subject graded in every flight) in a format similar to how the standard grade sheet looks
2. Print the grade sheet
3. Compare student performance vs class average
4. Evaluate class performance tendencies on each subject evaluated (getting better or worse)
5. Create a report for each student that depicts in a chart the performance of each student in 5 specific areas vs class average on this areas. The 5 areas are professionalism, knowledge, aggressiveness, situational awareness and instructional capability. Each of the 13 subjects evaluated in each flight belongs to at least one area e.g. subject discipline is part of the area professionalism.


6. Ideally i would like the chart in number 5 to look like the player attributes in pro evolution soccer!

I tested more than 10 different ways of doing this and i selected the one that has less VBA code because i can't program. So i created a separate field for every subject and i called it Sub1....Sub13. Then i have a separate field for each score of the 13 subjects Sc1...Sc13. I have a form where after filling the basic data (date,student name, instructor name etc) the instructor can click on the option group next to each subject and see the score at the bottom of the form.
What i want to do now is associate the ScX and SubX fields with the records in the areas. I probably need VBA code that will query the data of the fields that are associated to the areaID. So is there a way to associate records with field names based on e.g. a text lookup?
I am uploading my work so far and please tell me if you see serious problems with it. Thank you very much!
https://drive.google.com/file/d/0B3E...it?usp=sharing

[June7]

Still downloading db (it's taking quite a while) but can already tell there is a serious problem with the data structure. A field for each subject and each subject score is not a normalized structure and will probably cause a lot of frustration. The first being how to associate each subject field with the area. You said 'at least one area' - could a subject be associated with more than one?

By chart you mean a graph of some sort - bar, line, pie? I am not familiar with 'player attributes in pro evolution soccer'. What does this look like?

Finally, got the db downloaded. I then ran Compact & Repair and the size reduced from 12MB to 736KB. Always run C&R before uploading db and after design modification.

Is the db in Greek?

Advise not to build lookups in tables, review: http://access.mvps.org/access/lookupfields.htm

Also advise no spaces or special characters/punctuation (underscore is exception) in naming convention. You have mostly done that with a couple of exceptions. Better would be A_A or AA instead of A/A. And FB_Query would be better.

[giol]

This how it looks in PES. Basically it is pentagon where the edges are the 100% and the line is where the dots of the 5 attributes connect. If you add the line for class average it gives a very clean picture of where the student is better or worse.

The records are in Greek yes.
I said at least one area because i want to anticipate the possibility of a subject that would fit in 2 areas. You see the lines between professionalism and knowledge ,for example, are a bit gray and i may need to make some corrections in the future.
I tried to build a normalised one where i had a table with FlightID,GradeID,SubjectID,Score (thanks to rpeare) but i could not make it look like the form i built and that is important because last year i did something similar in excel and they hated entering data with the keyboard. Also i could not ensure that all 13 subjects where graded and that none was graded twice in the same flight for the same student.

[June7]

Never seen anything like that. Still not sure how to interpret it. I don't think Access graphing can produce. Can Excel even?

So you want a form that lists all 13 subjects for each student? Each student will always have to accomplish all 13 subjects?

Options with a normalized db to get the desired form layout are:

1. batch create 13 subject records for each student, open form to that student's records, fill in grade to each record

2. unbound form and lots of code to save and retrieve data

Yes, will require VBA code but I doubt that can be avoided no matter what you do.

If you want to allow subject to have area options - would that be one area for each subject for each student? Would you need 13 more fields to select the area for that subject for that student to associate with?

[giol]

In every flight the student is evaluated and graded on all 13 subjects. Lots of code is what threw me off the right way. Do you think there is anything i can do to, at least, ensure that by using the database in hand i will not risk losing data or corrupt old data? I mean i could backup often and since we will print all filled grade sheets, we will have the data in hard copy.
Is there any way to perform some kind of Vlookup on a table and retrieve/sum/count the data that match a specific criteria? This is what i would do in Excel but i am not sure this can happen in access.

[June7]

Yes, the equivalent in Access is domain aggregate functions (DLookup, DCount, DSum, etc.). Access Help has guidelines on using DA's.

[rpeare]

You're really going to have trouble with this database if you keep going this way because for each record the subject can appear in a random column.

Here's an example of a normalized structure (mostly using your existing data) grades5.zip

Look at frmMain, there are a couple of queries that will show you how to score for a student/squadron as well (Qry_SquadronScores, Qry_StudentScores). I don't normally use bound forms so there may be some loose ends in this, in fact it's highly likely) but you get the idea of your data entry.

[rpeare]

haha, I was working on this off and on today and one of june's suggestions is exactly what I did.

[giol]

That is amazing Rp!
I built some queries and i can extract needed data easily. Thank you very much.
Can i delete the other tables and queries that i created (expect subjects,area,student and instructor)?
I will work on it to understand how things happen.

[rpeare]

You can do whatever you like with it. I think I only used 2 or 3 of your tables (students, instructors and classes) and made two new ones, the rest of them I didn't even touch I don't believe.