Query to find multiple criteria within same field - not working

[nobuddy]

I have a query based on 2 tables. The first table (Profile) has a one-to-many relationship with the second table (Responsibilities) based on the field ID_New. There are UP TO seven "responsibilities" that each record in the Profile table can have. I thought the way I set the tables up was the most efficient in the beginning. Now, I'm wondering if I should've just added seven additional fields to the Profile table.

Here's the problem: I need to query the records in the Profile table to find out which ones have BOTH "Operations" AND "Menu Development" in the Responsibilities table. I only want the query to return the unique records (i.e., no duplicates on ID_New). When I enter Like "Operations" And "Menu Development" it returns no records. When I enter the criteria as an OR statement, I only get the records that match either/or, but not necessarily both. Am I stating this clearly?

Is there something I'm missing in establishing the criteria, or (say it ain't so ) have I set the tables up incorrectly?

 

[kathryn]

Sorry, but I think you have set the relationships up incorrectly.

It sounds like you actually have a many-to-many relationship between the tables. Are the following two statements both true?

Each Profile can have many Responsibilities.
Each Responsibility can be associated with many Profiles.

If these are both true, then your tables need to look like this:

tblProfile
----------
ProfileID (Primary Key, Autonumber)
field
field
etc.

tblResponsibility
-----------------
ResponsibilityID (PK, Auto)
ResponsibilityText

tblProfileResponsibility
------------------------
ProfileResponsibilityID (PK, Auto)
ProfileID (foreign key, 1-M to tblProfile)
ResponsibilityID (foreign key, 1-M to tblResponsiblity)

Let me know if this makes sense.

Kathryn

 

[MichaelRed]

Like "Operations" And "Menu Development"

Appears to be incorrect.

This would essientailly return records which have both "operations" and "Menu Development" in the same record.

I think you probably need to look at a subquery, where you get all of the Id's which have one of the values and use it to find the records which have the same ID, and the other value.

You can also get the set by having layered queries. First layer would be what you have now - but two of them (one for each "Responsibility". Second layer would use both of these joined on the Id field. Results should be those Id.s which have both responsibillities.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Quehay]

Simple solution (sometimes the best): If the number of responsibilities is stable (and if it isn't then Kathryn's solution is best as it allows flexibility), that is, you won't be amending the number of responsibilites or the name of them, then you can consider them as attributes of the profile (Theoretically you could normalize things like hair color, eye color, & zip codes from a Contacts list into separate tables since they are one to many, but nobody does.) So each profile would have a yes / no value for each of the seven Responsibilities. Then you can query by dragging profile name into the query and each of the responsibilities as well. Put a "yes" or "true" criterion below those fields which must have a True value (both entries on the "AND" line). This way of doing it would also allow easy crosstab report creation where you could view each Profile going down as a row header on the left and the Responsibilites along the top as column headers with checkboxes indicating possession of the Responsibility.

That's advice on fixing for the next go around. To get the data out of what you have now Michael's right. Keep asking if this doesn't work (these problems are fun when it's not your job on the line).