Query question - setting field criteria

A

Alana

My database includes companies, products, and agreements. I need a query to
give me a list of all agreements whose type is "exclusive", so I set the
field criteria on my query so that "Type" = Exclusive. This works fine, and
I get a list of all my products that are under exclusive agreements. What I
need now is a list of all the agreements these products are under, exclusive
or otherwise. Here's a super-simplified example:
Products: soda, candy, chips
Soda is under an exclusive agreement, and is also under 3 non-exclusive
agreements (so all 4 of its agreements show up on my list)
Candy is only under non-exclusive agreements, so it won't show up on my list
at all
Chips is under an exclusive agreement, so this agreement shows up on my list

Any tips?
Thanks!
-Alana
 
J

Jackie L

Alana,
First create a query to test for the items that have the exclusive
agreements. If you make it a Totals query with a group by on the item and a
Where for the agreement type and then put exclusive in the criteria. That
will give you a list of all items with at least one exclusive agreement.
Then create a new query with the original item and agreement tables and then
an equals join from the items to the Totals query so that the only items
showing are those in the first query. This is assuming that the item number
is unique.

Hope this helps.
 
A

Alana

Worked nicely, thanks for your help!

Jackie L said:
Alana,
First create a query to test for the items that have the exclusive
agreements. If you make it a Totals query with a group by on the item and a
Where for the agreement type and then put exclusive in the criteria. That
will give you a list of all items with at least one exclusive agreement.
Then create a new query with the original item and agreement tables and then
an equals join from the items to the Totals query so that the only items
showing are those in the first query. This is assuming that the item number
is unique.

Hope this helps.
Click to expand...
 
Top