Query with nested queries

[psycoperl]

Need some guidance/advice:
When I am running this query, I am getting "The multi-valued field 'Seats.tsTestTypes' is not valid in the specified JOIN Clause"

In my tblTestSessions.tsTestTypes I have the list of all the TestCodes that can be issued for that session.

I am trying to get a list of the sessions that have seats available and are in the future.

HELP!

SELECT 
 Seats.tsTestSessionID, 
 Seats.tsTestSessionDateTime, 
 Seats.SeatsAvailable
FROM 
 (SELECT 
    tblTestSessions.tsTestSessionID, 
    tblTestSessions.tsTestTypes, 
    tblTestSessions.tsTestSessionDateTime, 
    tblTestSessions.tsMaxRegistration, 
    tblTestSessions.tsMaxRegistrationOverride, 
    tblTestSessions.tsRoom, 
    IIf(IsNull([APT].[NumOfAppt]),0,[APT].[NumOfAppt]) AS NumOfAppt, 
    tblTestSessions.tsMaxRegistration-IIf(IsNull([APT].[NumOfAppt]),0,[APT].[NumOfAppt]) AS SeatsAvailable
  FROM tblTestSessions LEFT JOIN 
    (SELECT 
       tblStudentAppointments.aptSession, 
       Count(tblStudentAppointments.aptRecordID) AS NumOfAppt 
     FROM tblStudentAppointments 
     GROUP BY tblStudentAppointments.aptSession) AS APT 
  ON tblTestSessions.tsTestSessionID = APT.aptSession) AS SEATS INNER JOIN 
      (SELECT 
         STN.* 
         FROM tblStudentTestNeeds AS STN INNER JOIN 
            (SELECT 
                tblStudentTestNeeds.tnStudentID, 
                Max(tblStudentTestNeeds.tnDateEntered) AS MaxOftnDateEntered 
                FROM tblStudentTestNeeds 
                GROUP BY tblStudentTestNeeds.tnStudentID) AS MRTC 
  ON (STN.tnDateEntered = MRTC.MaxOftnDateEntered) 
     AND (STN.tnStudentID = MRTC.tnStudentID)) As NEEDS 
ON NEEDS.tnTestCode = Seats.tsTestTypes
WHERE
 Seats.tsTestSessionDateTime > now()
 AND
 Seats.SeatsAvailable > (-1*Seats.tsMaxRegistrationOverride)

 

[Joe4]

I have not had the pleasure of attempting to use Multi-Valued in Access yet (and I hope I don't ever have to). My understanding is that they can be problematic, and should not be used in join fields.

Here is a person who had a similar issue: http://www.accessmonster.com/Uwe/Fo...valued-field-join-clause-problems-Access-2007

 

[psycoperl]

Hi Joe4:
I see. Can you provide some guidance on how to get out of "nasty" situation?

I actually only have this one multivalue field in my database. it is storing tiTestID values from tblTestInfo in my tblTestSessions table which shows which test codes are offered for each session.

Since I am in still in full development, now would be a good time to remove "crap" that could hinder before I start loading thousands of records into my database.

 

[Joe4]

Like I said, I have never used them, so I am not sure what the best way of correcting it is. One approach they mentioned in that thread I mentioned is:

The "classic" approach (which is actually what Access does under the covers
with multi-value fields) is to introduce a third table to resolve the
many-to-many relationship.