wanna twist your brain with this SQL statement?

[tziviak]

I need a sql statement that shows-the child's session information(ex. date, time started, time ended), therapists info(ex. name, id), and child's info (ex. id,name, dob)
But to make it tougher-I need all the sessions per child-with the specified child, and therapist-but the query should display -all the child's sessions-for the day that the specified therapist saw the child (but should include all the sessions-including other therapist's session-for that day, that child)
ex. if therapist A saw child Z on 1/1/03 (and therapist B,D,& F saw child Z on 1/1/03 too) then the query should display all 4 session for therapist a,b,d,& f)

This is the code to get the specific therapist and child-but not the other therapists info:


sql = &quot;SELECT Sessions.UserName, Sessions.OSIS, Sessions.WereServiceRendered, Sessions.ServiceType, Sessions.ErrorCodes, Sessions.DateOfService, Sessions.TimeSessionStarted,Sessions.TimeSessionEnded, Sessions.TotalSessionDuration, Students.[Last Name], Students.[First Name], Students.[Date of Birth], Employees.[LAST NAME], Employees.[FIRST NAME], Employees.[Employee ID] FROM (Sessions INNER JOIN Students ON Sessions.OSIS = Students.[Student ID]) INNER JOIN Employees ON Sessions.EmployeeID = Employees.[Employee ID] WHERE (((Sessions.DateOfService)>= #&quot; & BeginDate & &quot;#) AND ((Sessions.DateOfService) <= #&quot; & EndDate & &quot;#)) &quot;

sql = sql & &quot; GROUP BY Sessions.UserName, Sessions.OSIS, Sessions.WereServiceRendered, Sessions.ServiceType, Sessions.DateOfService, Sessions.TimeSessionStarted,Sessions.TimeSessionEnded, Sessions.TotalSessionDuration,Students.[Last Name], Students.[First Name], Students.[Date of Birth], Employees.[LAST NAME], Employees.[FIRST NAME], Employees.[Employee ID], Sessions.ErrorCodes HAVING ( ((Sessions.WereServiceRendered)='y') AND (Sessions.ErrorCodes Is Null Or Sessions.ErrorCodes <>'del') ) &quot;
if StudentId <> &quot;&quot; then sql = sql & &quot; and Sessions.OSIS = '&quot; & StudentId & &quot;'&quot;
sql = sql & &quot; and Employees.[Employee ID] =&quot; & Session(&quot;EmpID&quot & &quot; order by Sessions.DateOfService, Sessions.TimeSessionStarted &quot;%>

Any Help would be appreciated.

 

[PauloRico]

Try this -

SELECT whateverdata WHERE ChildID IN (SELECT ChildID FROM yourotherdata)

Lets start with the second SELECT statement. This will pull out the record that you have so far (the session with the certain child and certain therapist). The first SELECT statement will pull out all records where the ChildID in the second SELECT statement match (you will need the date criteria in both).

 

[tziviak]

The second select statment is the one -that I wrote? and how do I select from the previous select statement (can we have a select in a select? if yes-do you mind giving me a simple example-so I can get the idea?
thanks

 

[PauloRico]

Usually you would write a query like this -

SELECT MyID FROM MyTable WHERE MyID=1

But what you're looking for is something like this

SELECT MyID FROM MyTable WHERE MyID IN (SELECT MyOtherID FROM AnotherTable)

It like multiple dynamic WHERE clauses.

If you are still unsure (I am probably the worst communicator on the planet), let me know and I will create an example in a database and e-mail this to you.

 

[tziviak]

Is there a way I can do it on access? I tried and wasn't sure how to go about it (in design view, that is) if yes, how do I specify a select in another?
thanks

 

[PauloRico]

I don't think you can create the SQL the same within Access QBE grid. But you should be able to to create the second SELECT statement and save it as a query, and use this query in another query, linking on the appropriate fields.

 

[Transcend]

Could you just type in into access in the SQL view?

Transcend