OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Comination Filters on a Query

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
RSpringuel
General User
General User


Joined: 31 Mar 2007
Posts: 22

PostPosted: Mon Apr 02, 2007 9:31 am    Post subject: [Solved] Comination Filters on a Query Reply with quote

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:
Code:

AmBr 06RAA
AmNi 05RAM
AmRe 04RAM
AmRe 03RAM
AnJe 04RAM
AnKr 04RAM


I'd like the query to return only (AmRe, 04RAM) and (AmRe, 03RAM).


Last edited by RSpringuel on Tue Apr 03, 2007 11:15 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 02, 2007 1:59 pm    Post subject: Reply with quote

SELECT "Student", "Measure" FROM "Table" GROUP BY "Student", "Measure" HAVING (COUNT( "Measure" ) > 1)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
RSpringuel
General User
General User


Joined: 31 Mar 2007
Posts: 22

PostPosted: Mon Apr 02, 2007 3:22 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Apr 02, 2007 6:28 pm    Post subject: Reply with quote

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

Code:
SELECT "STUDENT", COUNT( "MEASURE" ) FROM "TABLE1" GROUP BY "STUDENT" HAVING ( ( COUNT( "MEASURE" ) > 1 ) )


Now save this query and name it qMultiMeasure.

Open a second query designer window, add the table TABLE1 and add the query qMultiMeasure. Join the table to the query on STUDENT:


That's it.

This is the same as if you had created an SQL statement of
Code:

SELECT * FROM TABLE1
WHERE STUDENT IN ( SELECT "STUDENT",
                                                 COUNT( "MEASURE" )
                                   FROM "TABLE1"
                                   GROUP BY "STUDENT"
                                   HAVING ( COUNT( "MEASURE" ) > 1
                                   )


Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
RSpringuel
General User
General User


Joined: 31 Mar 2007
Posts: 22

PostPosted: Tue Apr 03, 2007 6:53 am    Post subject: Reply with quote

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

Thanks.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group