Using MS Access/Access Query


QUESTION:  This is my second Access DB assignment, and I struggle with this.  I have Windows 7, Access 2007, I have three Access tables Year1Class Assignment, Year1Students, and Year1StudentTestScores.  I need to create a query whose output shows Year 1 test scores for all second grade students.  The columns in the query are:  StudentNumber(Year1Students), LackOfInvolvement? (Year1Students), ClassNumber (Year1ClassAssignment), Teacher (Year1ClassAssignment), FirstGradeScore (Year1StudentTestScore), and SecondGradeScore (Year1StudentTestScore).  Note: Lackofinvolvement is stating if parents are involved with students to get a good grades.)  There are 548 records in Students and TestScore tables, and 69 records in Assignment table.  The book states that the query should generate 69 records for the three teachers (Smith, Casey, Jones).  What criteria would be used to generate the query?  I have tried different relationships and I am not sure what criteria to insert to get this.  I hope this is sufficient information.  Thank you.

Frankly, if this assignment is in your text book, I would throw out that book. There are so many things wrong with the structure of the tables I hardly know where to begin. If this is the table structure you came up with to deal with the assignment then you need to start over.

But the only criteria I can tell from what you have is to filter where the SecondGradeScore is not Null.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

I read your comment. Sorry I was so blunt, but I was working under the assumption that the structure was dictated by the assignment in the book. That sort of raised my ire because someone writing a textbook for teaching Access should have known better. If I had realized that you came up with the structure based on the assignment I would have been less blunt and tried to explain the proper structure. Hope I can help more in the future.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.