Querying two values from the same field (1 Viewer)

Hello. I'm quite new to Access and have inherited a database from a colleague. It is a database of references from which we have extracted pesticide active ingredient, pest species and crop species using forms. I am trying to create a query that will allow us to search and return unique active ingredient x pest x crop species combinations (but which will return all instances of this combination).

The complication is that the pest and crop species names are within the same field ('taxa'). They are distinguished by a label ('pest' or 'crop') in an associated field ('PestorPredator'), but a query using 'OR' will only return a crop OR pest, whilst a query using 'AND' will return no records (because something can't be a pest AND and crop).

I want to be able to create a table that shows the active ingredient, associated pest species AND the associated crop species each in separate columns. We have tried a couple of methods, including UNION and UNION ALL, and I can post the SQL script if someone thinks they may be able to help. Also, please ask for any more detail you need; I'm not sure the best way to explain the issue.

It'd be very much appreciated. Thanks.

Could you post a sample row with field names and example contents?

You might be able to use the Like keyword to help do what you need.
Select aField from aTable where aField Like "*Pest*"

will return all results where Pest appears anywhere in the field.

Hi thanks very much for the speedy reply.
It's a bit difficult to post an example row as all of the relevant information is contained in different tables. We have been inputting data via a form. I have attached a doc that explains where the various pieces of info are in the form and the respective tables they come from. I don't know if this is helpful?

A 'wildcard' type search as you suggest might work...

Thanks.