Find records that match criteria of multiple fields

[jvbeats]

using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b


please point me in the right direction!

 

[jvbeats]

solved.

I needed 3 queries. I first generated queries with unique records for field2 criteria: "a" and another for "b", then joined them on an intersect distinct in the third query.

this was very helpful
http://www.gplivna.eu/papers/sql_set_operators.htm