Table Recursion

[Andrea]

i need to create a recursive function that allows me to create a different
table (with the same fields) depending on an answer entered into the current
table. how do I do that?

 

[Jeff Boyce]

Table store data, forms display data.

Don't do this in a table.

By the way, if your design allows for multiple tables of identical design
("with the same fields"), you probably have ... a spreadsheet! A
well-normalized relational database (Access) neither needs nor uses well
such "duplicate" table structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Andrea]

Well, they will pretty much have the same fields, but different records, and
the field prec_pipe depends would the depend on the primary key of the Main
Table. I am working on a piping system, and I need to be able to track down
the whole history of the pipe just by looking at the penetration (where it
ends and goes up to the next floor). I believe i could do this by just using
a report. The problem is setting up the relationships. Any ideas?

 

[Jeff Boyce]

Andrea

If you want to get the best use of Access' features and functions, you'll
have to have a well-normalized design. Nothing you've described yet leads
me to believe that you have one.

If you can provide an example of the kind of data you're talking about,
perhaps other newsgroup readers will be able to see another approach...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Andrea]

I have a Table tblSecPipes containing the fields (Name {PK}, Bay, Node,
Size, Pen, PenID, End)Then from there, Pen and End are Yes/No fields, so if
the user enters No in both
the table tblPipes would need to be populated. It has the fields PrecPipe,
SplitLoc, Name{PK}, size, Pen, PenID,Ends

But again if the user enters No for both fields, I would need to create a
new table "3rd pipes" for instance, and so on until either the pipe ends or
there is a penetration.

Then I would need to print a report containing all the pipes that are
related to each individual penetration

 

[mnature]

Just have one Pipes table, use an autonumber as primary key, called PipeID.
Use a field in that table that is called something like ConnectingPipeID,
which will be a long integer number. That field will reference a PipeID that
indicates the pipe that this one is connected to. You can then have any
number of penetrations, and all pipes would be in the same table. You will
set up the relationships by using the Pipes table twice, and drawing a
connector from the PipeID to the ConnectingPipeID.

 

[mnature]

Thanks Jamie. I see that I oversimplified the problem. I notice that Celko
has all manner of books out, must save my pennies and get some of them.

Just have one Pipes table <snip>

I think you are describing an adjacency list model approach for
modelling a tree (except that more constraints are required than you
have outlined):

'Graphs, Trees, and Hierarchies' by Joe Celko, from the [new] book,
Trees and Hierarchies in SQL for Smarties (Morgan-Kaufmann), 2003:

http://www.dbazine.com/ofinterest/oi-articles/celko24

I've just read the book and highly recommend it. It shows how the
adjacency list approach is essentially procedural but still provides
SQL solutions to many common problems. It does the same for the
author's preferred approach for SQL, the nested sets model.

Jamie.

 

[Andrea]

Thank you guys. You've been a lot of help

Thanks Jamie. I see that I oversimplified the problem. I notice that Celko
has all manner of books out, must save my pennies and get some of them.

Just have one Pipes table <snip>

I think you are describing an adjacency list model approach for
modelling a tree (except that more constraints are required than you
have outlined):

'Graphs, Trees, and Hierarchies' by Joe Celko, from the [new] book,
Trees and Hierarchies in SQL for Smarties (Morgan-Kaufmann), 2003:

http://www.dbazine.com/ofinterest/oi-articles/celko24

I've just read the book and highly recommend it. It shows how the
adjacency list approach is essentially procedural but still provides
SQL solutions to many common problems. It does the same for the
author's preferred approach for SQL, the nested sets model.

Jamie.

 

[John Vinson]

Thanks Jamie. I see that I oversimplified the problem. I notice that Celko
has all manner of books out, must save my pennies and get some of them.

Stock up on coffee and headache remedies... <g>

Celko is brilliant, and his books are gold mines of valuable ideas.
But they are VERY heavy slogging!

John W. Vinson[MVP]

 

[mnature]

Is it OK if I prefix table names with tbl_ just for this forum, though? j/k