Problem with grouping/max in query

[ps40life]

I have a query with 4 tables-campuses, students, student tests, and accomodations (these are items students can use for testing, e.g., highlighters, calculator, etc).
I am trying to show the last date of tests taken with the accomodations. So it would look like Student name took Math, Reading, etc. on a date and list the accomodations for each test. I just want the latest testing. It works until I add the accomodations. Because there are multiple accomodations for each test, I end up not getting the last test date. I get all of them. I can copy the SQL if necessary. Is there a way to use MAX without using "Totals"?

Also, if I could show the name of the campus rather than just the number, which is what is entered in my student table, I could leave out the campus table. It is a lookup field in the student table. How can I show the second column of the lookup rather than the first (the code) like you do in a form when you put 0" for the first column?

Thanks for any advice.

 

[ps40life]

Are you saying to run another query on the SQL you gave me including the Test Data table and picking up the test id? When I do that, I get strange duplicates. Eg., it gives me 3 Reading, 3 Writing, 3 Mathematics with the test id repeated. Originally, let's say Reading was 234, Writing was 235, and Math was 236. Now it assigns that number to each test. So they all have a Test ID of 234, 235, 236. It is repeating the Test ID, so I get nine records instead of just three. Why is this happening? Am I doing something wrong?

 

[scriverb]

No I left the Test Table data out of this SQL on purpose because it was causing the query to return all the records.
See your post of 3/5 that said:
It does not work because of having to include the Test ID. That is the foreign key in the accom table. If I could leave that out of the original query, I think it would work. Putting it in gives each record a unique Test ID, therefore, I get all the records. This is a tough one!


I just want you to look at the records selected and tell me if these are the correct records except for the fact that we have multiples by date. I plan on giving you code to loop through them and select out the most recent by date.
But, before I do that I want you to verify that the latest SQL is complete except for the multiples and TestID info and accommodations. Bob Scriver

 

[ps40life]

In the SQL you gave me, the test table is included. I show the test name from the test data table. It works fine. There are no duplicate records and I get the last ARD date as I want. All of the "select record" fields are 0. What is the purpose of that statement?
I had the dups when I went further and tried to add the test id on a second query. Your SQL works fine.

 

[scriverb]

Okay, great. The Boolean field [Select Record] was there as I wasn't sure if we would get multiple records on the test date. If we did then we could loop through them in code and select the latest one for process. No matter. Now we want to match this recordset to the tblAccommodations. The field that should be linked are the TextID fields. This should allow you to create a final subset of the records along with the unique accomodations data.

Does this make sense now. We couldn't have it linked before because of the Group By and Max functions. Now that we have one record we can pull the final piece of data in. Bob Scriver

 

[ps40life]

The test id is not included in the SQL you wrote-the test code is, but that is not on the accomodation table. So, I can't join the Accom table. Am I missing something?

 

[scriverb]

Okay you are right. Bare with me now as you see these linkeages are not the easiest. I think that there is something missing from your ACCOMODATIONS table that could fix this up for us. At the time that you create a new record in ACCOMMODATIONS you should have the following data available: Test ID, Test Code, Date of Test and the Accomodations. Yes??
If that is so then is it possibile to update this process to add two new fields to the ACCOMMODATIONS table? They would be Test Code and Date Of Test. If that can be done and we can update the records already there with this information then we can link the prevous SQL results with the ACCOMODATIONS table by Test Code and Date Of Test.

Let me know. Bob Scriver

 

[ps40life]

The Accom table has 3 fields. They are Accom ID (just an autonumber field for the key), the test id field that ties the accom to the test in the Test data table, and the actual accomodation.
How about setting up a function in the report to call the most recent date? Is there anywhere you can use the MAX function outside of the query and the Group By function? Why do you have to have the Group By function on all the fields? Is there no way to get rid of it when you are using aggregate functions??

 

[scriverb]

When you aggregate a recordset you must either GroupBy on a field to consolidate the values or you must perform a Function such as Max, First, Last, Sun, Count, etc. It is the table structure and what you are wanting to create that is causing this problem. You see the Test ID field is not unique when aggregated with the other fields in the query. So, we have to come up with a way to leave that out and at the same time accomoplish the pulling in of the ACCOMMODATIONS.

I have written this without the ability to test so give this a try:

Select A.NameofCampus, A.StudentSSN, A.Name, A.StudentID, A.MaxOfARDDate as ARDDate, B.TestID, A.[Test Name], A.Active, C.Accomodation
FROM (qryInitial as A INNER JOIN tblDataTest as B ON (A.StudentSSN = B.StudentSSN) and (A.TestName = B.[Test Code]) and (A.MaxOfARDDate = B.ARDDate)) INNER JOIN tblDataAccomodation as C ON (B.TestID = tblDataAccomodation.TestID)

Let's keep our fingers crossed.





Bob Scriver

 

[scriverb]

Just a update to the above post. Save the latest version of the large SQL code in a query called qryInitial. Then create this new one which references that query and the other tables needed to pick up the othe data elements for your final row of data fields. Bob Scriver

 

[ps40life]

I put the SQL in to a new query and I got a Syntax error on the join-it looks like it is pointing to the Inner join to the Accom table. I tried a few things, but my SQL knowledge is limited!

My fingers are crossed!

 

[ps40life]

A note on this that you may not be aware of-some tests do not have any accomodations, so the join between test and accom should include ALL test records from Test Data and any matching records from Accom.

 

[scriverb]

Change the the INNER JOIN to a LEFT JOIN and re-run the query.

Waiting for a positive result. Bob Scriver

 

[ps40life]

Bad news--I am now getting a "Reserved error (-8083)". No message with it. It won't run.

 

[scriverb]

I only wanted to change the INNER JOIN on the tblDataAccomodation as I should have indicated that. The INNER JOIN between qryInitial and tblDataTest is okay. Try this SQL code:

Select A.NameofCampus, A.StudentSSN, A.Name, A.StudentID, A.MaxOfARDDate as ARDDate, B.TestID, A.[Test Name], A.Active, C.Accomodation
FROM (qryInitial as A INNER JOIN tblDataTest as B ON (A.StudentSSN = B.StudentSSN) and (A.TestName = B.[Test Code]) and (A.MaxOfARDDate = B.ARDDate)) LEFT JOIN tblDataAccomodation as C ON (B.TestID = tblDataAccomodation.TestID);

What we are doing here is matching our recordset from the main query to the table tblDataTest on StudentSSN and TestName. This gets us the correct record to pick up the TestID. So now we link the tblDataTest record to the tblDataAccomodation on the TestID fields. Thus, we can now pickup the Accomodation data. If this errors out we can do this in two passes. One to just pickup the tblDataTest.TestID and a second to link with a left join to the tblDataAccomodations. But, i think this should do it with the above SQL.


Bob Scriver

 

[ps40life]

I am getting a syntax error on the join. It looks like it is going to the tblDataAccomodation part.

 

[scriverb]

Can you create a small sample database for me with the tables involved. I need the tables to sort out this problem. This should link up properly but I need the tables to get it to work okay. See my email address in my profile. Bob Scriver