combine multiple rows in same field

[Guest]

Hello all,
I am using Access 2002 and have established an ODBC connection to an oracle
database in my company.
I have data in one of their tables, in the GUI, it shows the path to my data
as
New/Project1/January.
In their table it is not so easy.

Here are the fields and value in my_table
caseid Case
101 Title of my case data


Here are the fields and values of their_table
caseid FatherID Name
101 3 January
3 2 Project1
2 1 New
1 0 (Blank)

The FatherID tells you where to get the next level up. At the highest
level, the FatherID value is always 0.

I want to get the path New/Project1/January from the caseid 101 in my_table.

Is there a way to loop thru this bouncing between caseid and fatherid until
the father id is zero and capture the Name values and add it to a string then
return the string value as the path?

Any assistance you can provide is greatly appreciated.

This is so far over my head. I'm not sure why they don't just store the
PATH for each caseid, but perhaps there is a reason.

 

[Gary Walter]

I don't mean to waste your time, but...

if this was SQL Server (not Oracle)
I would first look for a stored procedure