Report and Query Filtering

[ayonae]

Hey everyone,

Have a bit of a problem that I need help with! Here it goes.

I have an Equipment Table that contains, Equipment, Primary Connection and Secondary Connection. Primary and Secondary Connection lookup items from the SAME list of Motors. (so the user can select which motor is connected to that equipment, via the primary or secondary connection.. can be different motors!)

Now, in my Report or Query, I"m trying to find a way that it will display the MOTOR - then all the equipment connected to that motor, both from the primary or secondary connection. I don't want seperate headings. (ie connected via Primary connection, then list all equipment) I want to show all the equipment connected to that MOTOR in one list, regardless of primary or secondary.

What Query and code can I use do accomplish that? I can do Motor connected to Equipment via Primary or Secondary seperately, but not in one list.

 

[CT Witter]

You might want to rethink how you are storing your data. If you were to normalize along the lines of:

Equipment, Motor(ID), ConnectionType

where Connection Type References (1=Primary, 2=Secondary)

Example Data:
Equipment, Motor(ID), ConnectionType
1,1,1
1,2,2
2,5,1
3,6,1
3,7,2

Then you can query for what you have requested. This should also make other reporting a lot easier...ie do I have any motors listed as both a primary and secondary?

BUT, if you want to use your existing data...take a look at the UNION query.

HTH,
CT