query to lookup

[Leo Bonnaci]

I have a data base that has a tblstudent table, with a 1 to many
relationship with table tblPrograms. TblPrograms has a field that has
program codes (field prProgramCode) in it. I have another table
tblExceleratedClasses which lists all the program codes (field
ecProgramCode) of just the program codes of the excellerated clases.

I would like to do a query which list every student once and if they have
one or more program codes that are in the ecProgramCode field of table
tblExceleratedClasses to print "yes" in a coloum and if not leave it blank.

I assume I am using an IIF statement together with a dLookup(?). If so how
would this formula look like?

Thanks,
Leo

 

[Michel Walsh]

SELECT DISTINCT tblStudent.*
FROM tblStudent INNER JOIN tblPrograms
ON tblStudent.studentID = tblPrograms.studentID


should list only these students that are also in tblPrograms.


SELECT a.*, EXIST(SELECT * FROM tblPrograms WHERE tblPrograms.StudentID=
a.StudentID)
FROM tblStudent AS a


should add an extra computed column giving a yes (-1) or no (0) if the
student has, or not, a record in table tblPrograms.



Hoping it may help,
Vanderghast, Access MVP

 

[Leo Bonnaci]

Thanks,

Leo

SELECT DISTINCT tblStudent.*
FROM tblStudent INNER JOIN tblPrograms
ON tblStudent.studentID = tblPrograms.studentID


should list only these students that are also in tblPrograms.


SELECT a.*, EXIST(SELECT * FROM tblPrograms WHERE tblPrograms.StudentID=
a.StudentID)
FROM tblStudent AS a


should add an extra computed column giving a yes (-1) or no (0) if the
student has, or not, a record in table tblPrograms.



Hoping it may help,
Vanderghast, Access MVP