Filtering by fields

[lukestkd]

Hello all,
I am making a database for my ICT department. Attached as a ZIP file, 03/10 versions attached, but working from 10.

It needs to contain:
Student, Class, Project, Level, Feedback

So far I have:
tblstudents, tblClasses, tblProjects, tblLevels. tblFeedback. Which all feed into tblAssessment. I have tblFeedProjLevel which links the primary keys from tblfeedback,tblprojects and tbllevels – but I am unsure how to use it to get what I need.

In reality, feedback depends on what project the students are on and what level they achieve.

When entering the data for a student, I need a way that I can:
- Limit the feedback fields showing in a combo box by selected a project
- Further limit the feedback fields showing by selecting a level

e.g. if you select Scratch, level 3a, then only three fields from tblFeedback will show.

I then need to be able to create a report containing the student name and feedback by class.

 

[jzwp22]

First, although Access has the capability of having table-level lookup fields, using them is generally not a good idea. This site provides details as to the problems, table-level lookup fields can cause. I have removed the lookups in the attached database (the 2010 version only).

I also noticed that in the student table, you used the field name of "name". "Name" is a reserved word in Access, so it is best not to use it as a field or table name. This site has a list of reserved symbols and names in Access.

In reality, feedback depends on what project the students are on and what level they achieve.

Can a student progress from one level to another throughout the course of a project? If so, I believe that your table structure may not be correct.
Could you provide more details on the process you are trying to model?

 

[lukestkd]

Hi,
Many thanks for your reply and advice thus far.

A student will progress through the grades over a year, but will only receive one grade per project. So if the projects were named Project 1,2,3,4,5 instead of Scratch, Computer Control etc, then a student is likely to:
Start on a 3c with project 1, then progress to a 3b or 3a with project 2, then onwards.

Any advice on restructuring would be greatly appreciated!

Luke

 

[jzwp22]

To be able to help with the structure, we have to get a better understanding of what you are trying to model.

From what you have said thus far, many students can work on the same project and a student may work on several projects.

Is the level dependent on both the project and the student or just the project or just the student?

Can the student/project combination have many levels?

How does the feedback fit into the project/student/levels?

 

[lukestkd]

I have six classes, containing 30 students, so about 180 in total. I need to group the students by class.

Every student will do the six projects during the year, in the same order. So yes, a project has many students, and a student will have six projects.

A student could have any level on any of the six projects. Every level will have three statements (feedback) per project. I woulds eventually like a way in a form that a teacher can select (example) :

(1) Project, (2) class, (3) student, (4) level (5) one of the three statements for the chosen project/level

The idea is to make entering feedback into the database easier by reducing the number of statements to choose from based on the project/level. I then want to be able to print the feedback with the student name via a report, onto a label sheet (a class per sheet - so students grouped by class), so students can stick the feedback onto their folders.

Is that any help? MAny thanks again! Luke

 

[jzwp22]

From what I understand, a student/project combination will have only 1 level and 1 feedback, but there are 3 possible feedbacks specific to a project/level combination. If my understanding is correct, then that part of your database is not structured properly.

I think you have to relate the projects & levels first and then you can associate the applicable feedbacks to the project/level combination. I think you should also adjust the assessment table to include the combined project/level key rather than two separate keys. See the relationship diagram in the attached database.

The one other thing that I am not sure about is the class field in tblStudents. Is it necessary since you also associate each student with a class in tblstudentclass? You will have to explain this item a little further.

 

[lukestkd]

Thats right, one level for one project, but six different levels to be entered for each student overall (one per project).

What should I do to adjust the structure for that?

If keeping the class data seperate and linking that would work fine for me.

Very novice Q (last access use was back in college!) how do I set up the forms so that the actual words/names/levels/feedback statements show rather than the autonumber assigned where tables are linked?

 

[jzwp22]

Very novice Q (last access use was back in college!) how do I set up the forms so that the actual words/names/levels/feedback statements show rather than the autonumber assigned where tables are linked?

Typically, you would use a combo box based on the related table. The autonumber field will be hidden. For example you would create a form based on the student table and you would use a combo box based on the class table to populate the classID foreign key in the student table.

Now for your project/levels etc. it will be a little more complicated. You will have to use the cascading combo box technique to do the filtering you mentioned earlier. This site has some sample databases illustrating the cascading combo box technique.

 

[lukestkd]

From what I understand, a student/project combination will have only 1 level and 1 feedback, but there are 3 possible feedbacks specific to a project/level combination. If my understanding is correct, then that part of your database is not structured properly.

As the feedback is fixed to the level and the project (and is not repeated) would it be easier to get rid of tblFeedback and put these statements in tblProjectLevelFeedback?

 

[jzwp22]

Yes, that would be appropriate by normalization rules.

 

[lukestkd]

I've addressed the structure, and created some queries which I think now show the structure is working correctly.

What do you think of what I have changed thus far?

The next step would be creating the forms?

 

[jzwp22]

I'm a little confused. It looks like you have added additional tables regarding levels for feedback and targets. Why not have all level in 1 table and include a field to distinguish those applicable to feedback versus those applicable to targets? You can then have one table for project/levels. Since an assesment will then have project/levels for feedback as well as target that describes a one assessment to many project/levels relationship.

tblAssessment
-AssessmentID primary key, autonumber
-fkClasStuID foreign key to tblStudentClass (I would rename the current StudentID to fkClasStuID to avoid confusion with which table it should be joined)

tblAssessmentProjectLevels (you will have 2 records in this table for each assessment-one for the target and one for the feedback)
-pkAssessProjLevelID primary key, autonumber
-fkAssessmentID foreign key to tblAssessment
-fkProjectLevelID
-comment