Help With a Querry!

I have a column named PASS of 'y' & 'n'values. I need to run a querry that will pull a list of students who have 8 'y' only. In design view, I'm o.k. pulling the names & some other info but when setting the criteria under PASS, I can't seem to get the desired result.

Shoul I be using the COUNT or COUNTIF statement? Or any suggestions would be much appreciated. Thank you.

:confused:

What is the criteria under the column 'pass'...and if it's 1 column and 1 answer how will you get 8 'y's' for 1 name?

Just a lil more info is need. I'm willing to help though. :)

Here is the querry that I created:

SELECT MAIN_DATABASE.LNAME, MAIN_DATABASE.FNAME, [STUDENT DATABASE].PASS
FROM MAIN_DATABASE INNER JOIN [STUDENT DATABASE] ON MAIN_DATABASE.MEM_NUMBER = [STUDENT DATABASE].MEM_NUMBER
WHERE ((([STUDENT DATABASE].PASS)="y"));

Sample of the result after running the above:

LNAME FNAME PASS
PAT BEENA Y
VAC JOSEE Y
TAR JAMES Y
KON KRYS Y
DUR NAV Y
DUR NAV Y
DUR NAV Y
DUR NAV Y
SHE GLA Y
TOU MIC Y
TAY LIN Y
TAY LIN Y
GUA JANA Y
GUA JANA Y
GUA JANA Y

The output that I would like to see:


LNAME FNAME COUNT
PAT BEENA Y = 1
VAC JOSEE Y = 1
TAR JAMES Y = 1
KON KRYS Y = 1
DUR NAV Y = 4

And so on, and so forth.

Even better, how would I also include a COUNT for 'N'for every student.

Thanks for helping.

Why don't U user "group by"?

SELECT MAIN_DATABASE.LNAME, MAIN_DATABASE.FNAME, [STUDENT DATABASE].PASS, count(main_database.lname)
FROM MAIN_DATABASE INNER JOIN [STUDENT DATABASE] ON MAIN_DATABASE.MEM_NUMBER = [STUDENT DATABASE].MEM_NUMBER
WHERE [STUDENT DATABASE].PASS="y" group by MAIN_DATABASE.LNAME, MAIN_DATABASE.FNAME

The query syntax might be incorrect but the main concept is this.

I tried the recommendation but now I'm getting an error message: 'you tried to execute a query that does not include the specified expression 'PASS' as part of an agregate function.

Please offer some input.

Thank you.

I do apologize.

The server is absolutely right.
You have two options. Either leave [STUDENT DATABASE].PASS from your selection - since you count records only where this column is "Y" - or put [STUDENT DATABASE].PASS into the group by clause and it will work.