Add Columns to Grouped Max(date) Query

[csoseman]

I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.

tblStudents
studentID
studentName
studentEmail

tblResults
resultID
resultStudent
resultDate
resultTest
resultScore

tblTests
testID
testName
testPassScore

The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records. Any ideas? Please let me know if there is anything I should clarify.

 

[plog]

What kind of comparisons? Can you post sample data from your table along with what you expect back from your queries based on that sample data?

 

[csoseman]

plog,

I have attached a sanitized version of what I am working with. I put a screen shot in the report of what I would like to see as the end result and have 2 queries with the information I need. I just can't figure out how to join them in a manner that makes this work. Your help is really appreciated!

 

[plog]

I need sample data and then results based on that sample data. Your report screenshot is not derivable from the data in your tables. I need the target as well as a valid starting point from which to aim.

 

[csoseman]

Are the tables and queries not showing up in the accdb or are you saying the query cannot produce those results?

 

[plog]

They are showing up, but the report you have supplied cannot be produced by the data in your tables. In effect you've said you want your report to add up to 5, but the data in your tables only adds up to 3. Give me what the report should look like based on the data on the tables you have provided.

 

[csoseman]

I apologize for that. I went and took a look at what I had pasted into the report and that was not at all was I was looking for. I have attached a spreadsheet that should make more sense. Again, just to clarify what I'm looking for:

• Query should contain only one row for each student.
• Each row should be data for the most recent test that student has taken
• If a student has not yet taken a test, their row should be blank (reason for left join)

What I'd really like to know is how I would add the primary key for "tblResults" to "qryWhatIAlreadyHave." Again, I really appreciate your help and your patience.

 

[plog]

Nope, that spreadsheet confuses the issue--is that the starting sample data or what you want as a result?

Let's start over. No more explanation, just data. Give me starting sample data. Then provide me what you want your query to produce based on that sample data.

 

[Brianwarnock]

I cannot read an accdb but what concerns me is resultstudent would appear to be a student name when I think it should be the studentid, in which case the query you have would need to run only against that table. Then I think that you drag into the design grid students table, results table, your query left joining as you go and select the fields.

Brian