What type query and how?

[rdholdford]

I have an Access database with three tables, Family, Clients and Sessions. The Family table has an autonumber field, F_ID that identifies a particular family. The Clients table has an autonumber field C_ID that identifies all members of the family and includes sex, age, race etc. for each. The Sessions table includes the fields C_ID, S_ID (session id, an autonumber field), Date, Program (drop down box) and Type (drop down box). The Family table has a one to many relationship with the Clients table and the Clients table has a one to many relationship with the Sessions table. My director wants information about all children that had parents that attended a parenting class. Since the children did not attend, they have no entry in the Program field

Basically, I need a query designed to identify C-ID’s and their records in the Client’s table that have the same F_ID as those C_ID’s that have “parenting” as an entry in the Program field in the Sessions table.
Obviously, I’m new at this, I have no knowledge of code. Thank you very much for your help!!!

 

[lespaul]

Program (drop down box) and Type (drop down box).

If you mean that there is a look-up field in the table, you may want to reconsider, The Evils of Lookup Fields in Tables.

as far as your query goes, I think this will work. It finds the Family ID of those who attended the Parenting class and then it finds all the clients who have that FamilyID and removes the clients who actually attended the class:

SELECT C_ID FROM Clients WHERE F_ID in (SELECT F_ID FROM Session INNER JOIN Clients on Session.C_ID = Clients.C_ID INNER JOIN Family ON Clients.F_ID = Family.F_ID WHERE Program = 'Parenting') AND C_ID NOT IN (SELECT C_ID FROM Session WHERE Program = 'Parenting')

There may be a more elegant way, but this should get you the results you are looking for.

HTH

Leslie



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Perhaps something like this (typed, not tested) ?
SELECT Childs.*
FROM (Clients AS Childs
INNER JOIN Clients AS Parents ON Childs.F_ID = Parents.F_ID AND Childs.C_ID <> Parents.C_ID)
INNER JOIN Sessions ON Parents.C_ID = Sessions.C_ID
WHERE Sessions.Program = ValueOfParenting;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886