Advanced filter (of sorts) (2000)

Topic

New Reply

Ok, I have a table with MANY records in it, with no primary key and multiple occurrances of many records. A new record is added for every individual once they complete a class. I realize that columns with the class name formatted as yes/no would be more effective, however I acquired this beast, and now I need to manipulate the data for a one-time presentation. Basically, I would like to create a filter/query that would give me the names of only those who have completed three of the courses, for example, I would have something as simplistic as:

Jones – Math 101
Jones – Math 102
Jones – Math 103
Jones – English 101
Edwards – Math 101
Edwards – Math 102
Edwards – Math 103
Edwards – English 101
Smith – Math 101

I would like to query for all those who have completed Math 101, 102, and 103, and want my query to return only this:

Jones
Edwards

I hope thats a good enough description without having posted an example.

Reply | Quote

Replies

Say that your table is named tblData and that the fields are named LastName and Class. Create a query with the following SQL:

SELECT LastName
FROM tblData
WHERE Class In (“Math 101″,”Math 102″,”Math 103”)
GROUP BY LastName
HAVING Count(*)=3

Reply | Quote

Like a charm.

Reply | Quote