Query that shows a 1 or a 0 based on existence of record in another table

[jthoni]

I have a db that is tracking a student fund raiser. I have a table of students, and a table to pledges collected. I want to create a query that has all the data from the students table, and then I want a 1/0 or a true/false based on whether or not there is a record in the pledgesCollected table. I am thinking something like:



SELECT tblStudents.FullName, IIf(EXISTS(SELECT StudentProceeds.Student FROM StudentProceeds WHERE StudentProceeds.Student = tblStudents.FullName),1,0) AS PledgesCollected
FROM tblStudents;

I get 'Data type mismatch in criteria expression.'

Any ideas of what I am doing wrong, or a better way to do this?

Thanks!
~john

[Rixxe]

The query itself looks fine to me..

The 'Data type mismatch in criteria expression.' might be due to:
tblStudents.FullName AND StudentProceeds.Student are not the same type?

Also if you have some null values, that *might* also be a problem.

[jthoni]

Well,
tblStudents.FullName is calculated with: [LastName] & ", " & [FirstName]

StudentProceeds.Student is a lookup type which looks up tblStudents.FullName

So one is a calculated field, and the other a lookup. If this is the problem, is there anyway to do the check on a .ToString() type so that they both resolve to the same data type?

Thanks!

[weekend00]

I think an outer join is more efficient than exists:

SELECT tblStudents.FullName, IIf(StudentProceeds.Student is null, 0,1) AS PledgesCollected
FROM tblStudents left join StudentProceeds on StudentProceeds.Student = tblStudents.FullName

[slave138]

I couldn't find a reference for the Exists method in Access 2010, but in Word it requires a String-type parameter. If the same is true then the code should probably be something like:

IIf(EXISTS("SELECT StudentProceeds.Student FROM StudentProceeds WHERE StudentProceeds.Student = tblStudents.FullName"),1,0)

[jthoni]

Thanks all.

I figured out what was going on. I was thinking that the field was text, but the lookup is actually the ID (it just renders text in the query). I did needed to join on the ID, not the text full name:

SELECT tblStudents.FullName, IIf(StudentProceeds.Student is null, 0,1) AS PledgesCollected
FROM tblStudents left join StudentProceeds ON tblStudents.[StudentID] = StudentProceeds.[Student];