Show [a]=x, if [b]=y

[SueBK]

This is a query that has baffled me for a while, but I find myself constantly wanting to use it for checking.

I have two fields [a] and (amongst others).
What I want to see is:
records for [a] = 'x' IF = 'y', where x and y are not going to occur in the same record and also see = 'y'.

For example:
I have a table with:
[c] = project name
[a] = issue name
= response to issue

Project Zulu, has Issues 1 through 10; Project Alpha also has Issues 1 through 10. Each issue has a response . I want to see Issue #5 where Issue #6 includes the word "koala".

My results should look like:
Zulu #5 blah
Zulu #6 blah koala blah
Alpha #5 blah
Alpha #6 blah koala blah

At the moment I can only manage
Zulu #5 blah
Zulu #6 blah koala blah
Omega #5 blah
Turkey #5 blah
Alpha #5 blah
Alpha #6 blah koala blah

 

[MSAccessRookie]

Interesting issue, but I am not sure there is enough information to make any realistic observations. Two items that could use additional information are listed below:

I have two fields [a] and (amongst others).

The statement reveals only part of the information about the Table. If there are any design issues, they cannot be taken into consideration. Showing the full Table design is helpful here.

At the moment I can only manage.

You provided sample output that I assume was based on SQL code. Show us the SQL code so that we might be able to help you find out what is wrong with it.

 

[jdraw]

Projects have 1 or more Issues
Issues have 0,1 or more Responses

Get your self a data model. Get your tables and relationships organized. Properly structure table will help you.

We need more info as MSAccessRookie says.

 

[SueBK]

I was trying to avoid giving too much irrelevant information, but seem to have erred to far on the side of breviety.

I have a table of projects (currently several hundred records). I have a static table of issues (~50 records). For each project we need to consider each issue - yes/no and why.

This is stored in a third table. I am trying to query this third table. It creates one record for each project for each issue; that is, every project appears ~50 times and every issue appears against each project. The table has a "yes/no" field (this issue applies to this project) and a "why" field (with information about why it does/does not apply).

An example of the query I want to run is (briefly):
- one of my issues is "are there any State protected critters in the area", if the answer is "yes", then a list of the critters is entered in the "why" field.
- another issue is "are there any Commonwealth protected critters in the area", with a similar "why" requirement.

The koala is a State protected critter. Just recently it has been listed by the Commonwealth. I wanted to see:
- all projects that had "koala" in the "why" field against "state protected critters" in the issue field
AND
- for all of those projects (and only those projects), the "Commonwealth protected critters" records (regardless of whether they were yes or no or what was in the why field).

This would allow me to easily add 'koala' to the Commonwealth records.

What I am able to easily query is:
- all projects that had "koala" in the "why" field against "state protected critters" in the issue field
AND
- the "Commonwealth protected critters" records for ALL projects.

I'm not running this through code; just through a query.