[Solved] Comination Filters on a Query

[RSpringuel]

I've got two fields that I'd like to query out of a data table: Student & Measure. Each of these fields is a forgien key for the table (to seperate tables) and thus there are many occurances of each value for each field in the table. By querying both columns and turning on the "Distinct Values" filter, I'm able to cut the list down to just the unique combinations of these two fields.

However, I need to further cut down the number of results so that I only see Student values which have more than one Measure value associated with them. How do I formulate the query conditions to generate such a condition?

E.g.

I've got the following distinct pairs of values:

[Villeroy]

SELECT "Student", "Measure" FROM "Table" GROUP BY "Student", "Measure" HAVING (COUNT( "Measure" ) > 1)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[RSpringuel]

When I run that query on the table I the same results as if I had queried those two columns for distinct values.

If I try to run it on a query which has already grabbed the distinct pairs, I get a null result.

Any possible modifications that might make it work?

[DrewJensen]

One thing, I am not completely sure what you want - on this point - I take it you want all the records for the students that have multiple measure, correct? I will proceed as if that is the case

In SQL terms you need to use a sub-select, in Base terms this will be a query-in-query.

I created a table named TABLE1 with ( ID, STUDENT, MEASURE, VAL1 ) and populated this with the data you show in your message.

OK - it should go something like this.

Open a new query designer and add your table. In this query we will want to return the student field for those students with multiple measure entries:

The SQL would be this

[RSpringuel]

That worked, though I had to prefilter the original table with a query designed to grab just the distinct (Student, Measure) pairs.

Thanks.