Trying to create a complex query

[psycoperl]

I am working on trying to write a query that will give me available sessions that have seats remaining, and cannot figure out how to write one that will give me everything I need. Any help and guidance would be most helpful

Need to obtain:
ts.tsTestSessionID, ts.tsTestSessionDateTime, number of seats available for session (tsMaxRegistration – number of students already scheduled for the session)

Where
1. the most recent tnTestCode for the studentID displayed on the form tblStudentTestNeeds (it is logged in by tnDateEntered) is in the list of tsTestTypes (a comma separated list) in tblTestSessions.
2. the number of students already scheduled is less than the tsMaxRegistration, ap.aptSession contains the tsTestSessionID and are not canceled (ap.aptCancel)


Tables:
tblTestSessions [ts] fields:
tsTestSessionID, tsTestTypes, tsTestSessionDateTime, tsMaxRegistration, tsMaxRegistrationOverride, tsRoom

tblStudentAppointments [ap] fields:
aptRecordID, aptStudentID, aptTestCode, aptSession, aptPresent, aptDateCompleted,
aptIncomplete, aptEmail, aptPrint, aptCancel, aptDateAdded, aptDateSentEmail,
aptDateSentPrinted, aptModified, ResendETicket, ResendPTicket

tblStudentTestNeeds [tn] fields:
tnRecordID, tnStudentID, tnTestCode, tnDateEntered

Form Used:
frmAppointmentScheduling

 

[psycoperl]

Re: Trying to create a complex query (Parital Solution)

I have been working on this problem and have so far gotten gotten a query where I am trying to just get

The Sessions table with the count of appointments from the appointments table.

When I try to view it, I get a prompt asking for tblTestSessions.tsTestSessionsID

Why? What did I do wrong?

SELECT tblTestSessions.tsTestSessionID, tblTestSessions.tsTestTypes,
tblTestSessions.tsTestSessionDateTime, tblTestSessions.tsMaxRegistration,
tblTestSessions.tsMaxRegistrationOverride, tblTestSessions.tsRoom, 
apt.NumOfAppointments
FROM tblTestSessions 
   LEFT JOIN 
      (SELECT tblStudentAppointments.aptSession, 
    Count(tblStudentAppointments.aptRecordID) AS NumOfAppointments 
     FROM tblStudentAppointments 
     GROUP BY tblStudentAppointments.aptSession) APT 
 ON tblTestSessions.tsTestSessionsID = apt.aptSession;

 

[Joe4]

When I try to view it, I get a prompt asking for tblTestSessions.tsTestSessionsID

That usually means that it is not recognizing the field name in that table name. Make sure it exists, and you have spelled the table/field names correctly.

 

[psycoperl]

That usually means that it is not recognizing the field name in that table name. Make sure it exists, and you have spelled the table/field names correctly.

Got it... I added an S in tblTestSessions.tsTestSessionsID where it should have been tblTestSessions.tsTestSessionID

thanks