-
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.
-
More info.
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.
-
Querry
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|