report mutiple fields from one table

[Marc76]

I know this should be simple stupid. I looked on line and looked here and I am way to0 much of a noob to even know when I am looking at the right thing.



To the point:

I have one table. This tables Fields, "Comp ID - Loc ID - Proc ID - SU ID - SU ID 2 - SU ID 3 - SU ID-4 - SU ID 5" <---- simplified

All I need is a query, which in turn Ill make a report from, that finds all instances of one SU ID in the fields and matches the Comp ID & Loc ID, and lists it.

IE

SU ID xxx Comp ID at Loc ID
Comp ID at Loc ID
Comp ID at Loc ID

SU ID yyy Comp ID at Loc ID
etc. etc.


I would have thought a simple query would pull all instances, however it only pulls "SU ID", ignoring the instances in the remaining fields.

Now let it be said I may have blown my relationships, and or design. This is so not my forte' it is merely an attempt at showing the superiors that a db is essential in tracking what they have, then hopefully turning it over to an IT person who can do it up right.

When I built the table for Comp ID, which IS the parent table of the whole DB, the SU ID's made relation tables, that are not tables. They refer to the master SU table.

I found that online. I think this is why the query will not pull the remaining fields?

The basics is that the Db needs to list SU's, and Loc's, and Comp's, and proc's, so that we can pull a Comp at a loc, and see all the SU's that are used for it. I have all 4 tables, and the Comp ID table is built entirely from the other tables. I add the comp number, then the loc, su, and proc, are pulled from the other tables.

I have been able thus far to do all I want it to do, except make this query.

Im sure it has to do with my design and build but like I said this isn't my expertise.

Thank you for your help.

I attached my mess so you can see some of what I am talking about

[June7]

This is not a normalized data structure and will give you lots of issues.

Try applying the same filter parameter under each SU field and use the OR operator - this means each parameter on a separate Criteria row, will look like stair steps. The WHERE clause needs to look like:

(SU1 = [something] OR SU2 = [something] OR ...) AND [Comp ID] = [some Comp ID] AND [Loc ID]= [some Loc ID]