Need help with query!

[GaryJ]

Hi

This is probably really easy, however I cannot find a way to do it.

OK, I have a table that contains student records.

The first column is their name(changed to student 1, 2 etc..) the second column is the course thay have completed, and the 3rd column is whether they have passed an exam.

What I want is to create a list of learners that have not passed an exam. I can make a query that just returns "N"s however I just want it to return students names that have completed some courses, but not yet passed an exam

I have attached the table as a txt file, and from the data I would want my query to return any learner that has NOT got a y next to ANY of their courses.

So the result I would expect after the query would be:

Student 1
Student 3
Student 4



Im sure this is simple to an Access expert, but I cannot find an easy solution - any help would be appreciated!

 

[Brianwarnock]

In SQL view

Select name
From yourtablename
Group By name
Having Sum(IIF([text]="y",1,0))=0

In the event that you don't understand the logic post back.

Brian

 

[Kafrin]

One way would be to create a query that lists just students that HAVE got a Y next to them (for any course), eg:
SELECT StudentName FROM StudentTable WHERE Passed = "Y" GROUP BY StudentName;

Then you can use this query to find the remaining students, ie those without a single Y, either by using Not In:
Select StudentName FROM StudentTable WHERE StudentName NOT IN (SELECT StudentName FROM NewQuery) GROUP BY StudentName;

Or by linking the table to the new query:
Link on StudentName so that you show all records from the table.
Show StudentName from table where StudentName from query Is Null.


PS: Brian's method is simpler!

 

[GaryJ]

Thanks for the replys, and Brians does look simple, however I have never used SQL view, I always use design view. I know this is cheeky, but as the code seems quite small, could you create me the code that I could copy and paste into SQL view?

 

[Brianwarnock]

My code can be pasted into SQL and then change the field name of name to the field name containg the student name which I thought was name, and the table name yourtablename to erm, your table's name.

Brian

 

[Kafrin]

But don't forget to put a semi-colon ( on the end!

 

[Brianwarnock]

Not compulsory, just like all of the bracketting that access inserts when you use design view, but I suppose it doesn't hurt.

Brian

 

[Kafrin]

OK, fair enough.

I know if you're using SQL strings in VBA then you need the semi-colon or it won't run, so I've always assumed you need it in the queries themselves too (even thougth you don't need the masses of brackets), but you clearly know more about this than I do.

 

[GaryJ]

Brian, sorry to sound so dumb, but I hadnt used SQL view before, I have used your code, and the query has run, Im just gonna check now to see if it has pulled the results I wanted, but so far it is looking good.

Thank you so much - you are a star!!

 

[Brianwarnock]

I didn't know how to reply as I did not know the table name, I see the field name is Username, The important thing is that you understand the logic, the use of Summing of IIf constructs is often used in Counts.

Brian