very dificult 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?

 

[Michel Walsh]

Hi,


If you have a small and fixed number of levels in the hierarchy, here
just two, bring the table that many times.


SELECT a.f1 & ( "; " + b.f1 + ("; " + b.f2))
FROM myTable As a LEFT JOIN myTable as b
ON a.f2=b.f1

That should return
1
2; 1
3
4; 2; 1
5


Note that + propagates the NULL, while & keep the not-null, so, with
the right combination of + and &, the ; disappear all by itself, but that
assume that all the fields are text (not integer). If your fields are
integer (or numerical), an error would occur with "; " + fieldName. You
will have to relay on another technique to make the ; disappear, since you
will have to use & instead of +.



Hoping it may help,
Vanderghast, Access MVP