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.

 

[scriverb]

Please copy the SQL and post it so I may see exactly what you have created. Bob Scriver

 

[ps40life]

Here is the SQL:
>>
SELECT tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & ", " & [studentfirstname] AS Name, Max(tblDataTest.ARDDate) AS MaxOfARDDate, tblDataTest.TestID, tblDataTest.TestCode AS [Test Name], tblDataStudent.Active, tblDataAccomodation.Accomodation
FROM ((tblDataCampus INNER JOIN tblDataStudent ON tblDataCampus.CampusIDNumber = tblDataStudent.AttendingCampus) INNER JOIN tblDataTest ON tblDataStudent.StudentSSN = tblDataTest.StudentSSN) LEFT JOIN tblDataAccomodation ON tblDataTest.TestID = tblDataAccomodation.TestID
GROUP BY tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & ", " & [studentfirstname], tblDataTest.TestID, tblDataTest.TestCode, tblDataStudent.Active, tblDataTest.TestType, tblDataAccomodation.Accomodation
HAVING (((tblDataStudent.Active)=Yes) AND ((tblDataTest.TestType)="taks&quot)
ORDER BY tblDataCampus.NameofCampus, [studentlastname] & ", " & [studentfirstname], Max(tblDataTest.ARDDate);
<<
I tried creating one query with just the test data and then a second query to add the accom. info , but it still pulled all the test dates instead of the latest.

 

[scriverb]

How about sending me some test data without violating any student confidentiality. It would be easier to make the modifications and test if I had that data. See my email address in Profile. Bob Scriver

 

[scriverb]

Thanks for sending the data. Is the accommodations column in the spreadsheet how the data is organized in the accommodations table. What I am saying is this spreadsheet representative of how the tables are organized or is this just a representation of the output that you are looking for. Because, it seems that the Max of the date should just pick off for Sara the 12/12/2002 records and give her three of them because the test id and test names are all different thus the grouping would give you three records. Now the accommodation column has multiple accomodations for these three tests. How are they organized in the table. Individually with a test id. Like three records highlighter, calculator, and colored pencils all with the test ID corresponding to the tests.

Finally, and maybe this is the answer you are grouping on tblDataTest.TestType but I don't see that field being selected. What is it? Any possibility that this is different for each testing date? That could cause you to select all records. Add the field to the Select and let's see what that shows.

Let me know what you find.

Bob Scriver

 

[ps40life]

The spreadsheet is representative. I could send you a screen shot of the query window.
The accomodations are in a separate table with 3 fields (accom id, which at this point I am thinking is not really necessary anyway; the test id that, like you thought, ties the accom to the test; and the actual accom).
I need the test type because I need to single out only &quot;TAKS&quot; tests. However, I did delete the TestID field and that dropped off the 2001 writing test. That made the 2001 & 2002 record the same for Sara, except for the date, so it grouped them and pulled the max.
By the way, I was looking up something else on a union query and it turned out it was your reply! HA! What are the odds! Thanks for helping me without even trying.

 

[scriverb]

Okay, let me understand. You now have eliminated the TestID field which was a unique number by test for a date. That was causing you to not be able to pull the max date for the test. This is good.

So, you now have the correct records being pulled for the date. Yes??? If so we have to address the accommodations. You see the link between the tables and the Accommodations table will create multiple records due to multiple Accommodations for a particular test. I am working on that.

Please answer my questions above and repost the SQL. Bob Scriver

 

[ps40life]

Here is the SQL:
>>
SELECT tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & &quot;, &quot; & [studentfirstname] AS Name, Max(tblDataTest.ARDDate) AS MaxOfARDDate, tblDataTest.TestCode AS [Test Name], tblDataStudent.Active, tblDataAccomodation.Accomodation
FROM (tblDataCampus INNER JOIN tblDataStudent ON tblDataCampus.CampusIDNumber = tblDataStudent.AttendingCampus) INNER JOIN (tblDataTest LEFT JOIN tblDataAccomodation ON tblDataTest.TestID = tblDataAccomodation.TestID) ON tblDataStudent.StudentSSN = tblDataTest.StudentSSN
GROUP BY tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & &quot;, &quot; & [studentfirstname], tblDataTest.TestCode, tblDataStudent.Active, tblDataAccomodation.Accomodation, tblDataTest.TestType
HAVING (((tblDataStudent.Active)=Yes) AND ((tblDataTest.TestType)=&quot;taks&quot)
ORDER BY tblDataCampus.NameofCampus, [studentlastname] & &quot;, &quot; & [studentfirstname], Max(tblDataTest.ARDDate);
<<
It is exactly as you say. I have a left join on the Test to Accom because there is not always an accom. That gives me every test, but the difference in the accom makes the record unique. It is the grouping that is causing the trouble. Is there any way to pull the max, but not use the grouping? I had previously looked at Thread 701-484548 (Problem Customising a Query), and thought it might help, but I couldn't write it in SQL so it would work. SQL is not one of my strengths.

 

[scriverb]

If a test for a student(i.e. A School, 73456, Sara, 12/12/2002, 1615, Writing ) is a single row due to grouping and Max function. The Left Join to the Accommodations table will ultimately give you two records because there are two records for this test number (i.e. highlighter and colored Pencils) Am I wrong in thinking there are two records here. Is Highlighter/coloredpencils a single accomodation record?

Awaiting your answer. Bob Scriver

 

[scriverb]

After the last change how many records are you getting for Sara? Three, one for each test or more. Bob Scriver

 

[ps40life]

The highlighter/colored pencils is one choice, one record. There are other accom options, like Can use Calculator, In Spanish, etc., so some tests do have multiple accom records related to them.

When I drop the test ID, I end up with 5 total records for Sarah (two 2001 and three 2002). I think the 2001 writing test drops off because the accom is the same as the 2002 test, therefore identical records except for date. The query then takes only the Max. The math and reading stay because the accom are different. The 2001s have none; the 2002s have accoms.

 

[scriverb]

I think we need to remove the ACCOOMODATIONS table from the initial query that is performing the Group By.(See Below my attempt) Remove the reference in the Select portion, the join , and the GroupBy. Then create another query that will take this first query in as input and perform a left join to the Accommodations table on the TestID field. I believe that will solve this problem.
SELECT tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & &quot;, &quot; & [studentfirstname] AS Name, Max(tblDataTest.ARDDate) AS MaxOfARDDate, tblDataTest.TestCode AS [Test Name], tblDataStudent.Active
FROM (tblDataCampus INNER JOIN tblDataStudent ON tblDataCampus.CampusIDNumber = tblDataStudent.AttendingCampus) INNER JOIN tblDataTest ON tblDataStudent.StudentSSN = tblDataTest.StudentSSN
GROUP BY tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & &quot;, &quot; & [studentfirstname], tblDataTest.TestCode, tblDataStudent.Active, tblDataTest.TestType
HAVING (((tblDataStudent.Active)=Yes) AND ((tblDataTest.TestType)=&quot;taks&quot)
ORDER BY tblDataCampus.NameofCampus, [studentlastname] & &quot;, &quot; & [studentfirstname], Max(tblDataTest.ARDDate);


Now create a new query taking this query in as input and perform a left join to the table accommodations on TestID. You will receive two or more records only if there are more than one accommodations for that particular test. That can be handled in your report by Hide Duplicates on the other fields.

Sorry about the thickheadedness this morning. Without having the actual tables in front of you to use sometimes it is difficult to understand the relationships. Bob Scriver

 

[ps40life]

Ya' know I was going to mention in my last post that I tried to do a second query. Let me make sure I am understanding you though. You're saying run the query the same, just take out the accom table all together. Then run a 2nd query on the first adding the accom. table with a left join. I thought that would work too, but I have to include the Test ID because that is the tie to the Accom table. Maybe that is what is causing it to give the same results. : (
I am sure it is difficult to try to imagine EXACTLY what I am looking at, and I am doubting, with 34 years experience, you are thickheaded about this! No problem.

 

[scriverb]

Yes, give that a try and I think you will be very close to what you want. Bob Scriver

 

[ps40life]

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!

How about this--what if I added another table, like the Test table to tie the other two together, then I could leave the Test ID out and tie them by SSN. I tried it and it seems like I get a bunch of ambiguous records (like repeats of the same records), but maybe I could get around that by just showing Unique Values?? Is this making any sense! I have to leave in a few minutes, so I am trying to do this quick.

 

[ps40life]

I have to leave for an appt., so I am going to lay this to rest...for now! I will test it some more later and get back to you. Thanks! : )

 

[scriverb]

SELECT tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & &quot;, &quot; & [studentfirstname] AS Name, Max(tblDataTest.ARDDate) AS MaxOfARDDate, tblDataTest.TestCode AS [Test Name], tblDataStudent.Active, False as SelectRecord
FROM (tblDataCampus INNER JOIN tblDataStudent ON tblDataCampus.CampusIDNumber = tblDataStudent.AttendingCampus) INNER JOIN tblDataTest ON tblDataStudent.StudentSSN = tblDataTest.StudentSSN
GROUP BY tblDataCampus.NameofCampus, tblDataStudent.StudentSSN, tblDataStudent.StudentID, [studentlastname] & &quot;, &quot; & [studentfirstname], tblDataTest.TestCode, tblDataStudent.Active, tblDataTest.TestType
HAVING (((tblDataStudent.Active)=Yes) AND ((tblDataTest.TestType)=&quot;taks&quot)
ORDER BY tblDataStudent.StudentSSN, [studentlastname] & &quot;, &quot; & [studentfirstname], tblDataTest.TestCode , Max(tblDataTest.ARDDate) Desc;

Try the above SQL as a first step in a process that I want to try. Make a spread sheet out of the results and send it to me with just the first names again. What I am doing here is creating an interim recordset with a SelectRecord boolean field and put it into a Temp Table that is sorted in an order than we can loop through the records and make selected those records that are the Max date for a particular student and Test name. I think we can then write another query to link this selected group of records to the accommodations table to pick up that data.

Waiting your reply
Bob Scriver

 

[ps40life]

Bob,
Did you get the email I sent with the data from the SQL you posted above? I was wondering what the next step would be. It seemed to get the data I need, now how to add the accom's on.
Thanks,
Susan

 

[scriverb]

Yes, I did and I will now take a look at it. Sorry about the delay in looking at it. Will post a little later today. Bob Scriver

 

[scriverb]

After looking at this data could you tell me if we have successfully selected the correct records and data to represent the latest of each test type for an individual. It looks like it to me. Please check all of the fields to make sure this is correct.

If it is, then we should be able to create a query to link to table tblDataTest using the Test Date and the Test Name. We then can pickup the test id# and update the records.

Then we have the data to match to the table Accommodations to match on TestID# and pick up the Accommodations for that test.

Please review these thought and see if you concur.

Bob Scriver