Circular Query

[Raul Sousa]

Hi all,
I need to build a query and I don't know how to, so any
ideas are most welcome.

I have a table for operations with clients. Every
operation can be one of two, a new operation or an change
to an existing operation. Avery operation can have only
one change, but not necessarily.
On this table I have two fields; operation number and
operation changed. If a record is a new operation it has
only operation number, if it is a change to an existing
operation it has a new operation number plus the number
of the operation it changed.
I need a query to give me all related operations. All I
can do is a query that prompts the user for an operation
number and displays that operation.
SELECT Clients.Operation, Clients.*
FROM Clients
WHERE (((Clients.Operation)=[Choose operation number]));

Beside this I need the query to check if the operation
the user chose has any value in the operation changed
field and if so display all that operation.

I give an example:
Operation number operation changed
1
2 1
3
4 2
5

If the user choose operation 4 I want the query to
display the records for operations 4 2 and 1.

I think it should look something like:
SELECT Clients.Operation, Clients.*
FROM Clients
WHERE (((Clients.Operation)=[Choose operation number]))OR
(((Clientes.Operation)="Clientes.OperationChanged"));

It is too complicated for me. Should I have another
query? Another table?
Does anyone have any idea how to achieve this?

 

[Steve Schapel]

Raul,

Try it like this...

SELECT * FROM Clients
WHERE (OperationChanged Is Not Null) Or (Operation In(SELECT
OperationChanged FROM Clients ))

- Steve Schapel, Microsoft Access MVP