designing report, create tree diagram

Hello all,

I'm not sure whether this is a query, form or report problem but I try my best to explain...

I'm trying to design a database that stores info on a power plant grid. Basically, the grid is made up of levels. Every level connects to a higher level (except of course the very top!). The lower levels cascade down in a tree diagram pattern, that is, any level can have multiple lower connections, but only one upper connection.

In my table, I have a column called "Upstream Feed" which gives the name of the level that is connected directly above.

My logic is, that as long as I have the name of the upstream feed of each level, then I will know the exact layout of the 'tree diagram' grid (provided I also know the very top level).

My main problem is that I wish to sum the total power at each node of the network, and add it up until the very top level. I wish to create a report that shows the power at each level, as well as the above feed, and possibly the path to the top.

I have absolutely no idea how to set this out so that it can work. I been working on this for weeks but no luck.

I guess I'm just looking for some advice as to how to design this kind of database.

Thankyou to anyone who responds,

Cheers,

Bakerboy

The problem you have defined is some what similar to Bill of Material explosion where in the same table will have parent child relation and its associated quantity per assembly in any ERP or MRP package. The table structure will look some what like this.

ParentGrid -primarykey
ChildGrid - primarykey
relatedcost

if you have a temporary table with the same structure and primary key and start the initial data in temporary table as parentgrid, parentgrid, 1 and run the following query until no more record is added to the temporary table (this can be achieved by record count before and after append) you will get all the related tree data into temporary table.
pseudo code->
Insert into temptable parentgrid, childgrid, related cost
from select parent - sorry I am not able to recollect the SQL
---- just put the temptable and yourmaintable in design window and join temptable childgrid field to maintable parentgrid field and select all fields from maintable and append it to temptable. Run this query in a loop until nomore record is added and then from temp table you can sum up the data you want.

If you need further assistance I will come with moredetailed explanation tomorrow. In the mean time pl provide a sample data if possible.

Thanks heaps for your reply supersubra.

I don't completely understand everything you wrote, but I really appreciate the response.

I'm not too sure what you mean with regards to the temporary table, but I hope I will soon! :-)

You asked for me to supply sample data, so I've attached the database file I'm working on. Also attached is a diagram showing the actual layout (just an example) that I would like the database to represent. In the diagram, I've incuded some sample power values for each level, and I've summed them to the top. In the table in the database, I've written the total power levels for each level, as well as the summed values to the top. The ultimate outcome is to have these sum automatically to the top, however due to the fact that this database is in such early stages, then I've just added them manually for now.

One more thing I've just decided to attach is a notepad text file just showing the very basic layout of the report I am hoping to create. I thought it might be useful.

Thanks again for your reply, I hope I've attached the right things you've asked for!

Bakerboy

I have included the solution and its explanation in the attached zip file. Just try it and reply. Bye

Thanks again,

That seems to be what I was talking about, though I'm still looking through it all to completely understand it!

Just one question. Is there any way that all the reports (i.e. the MSB1, MSB2 and SB1 reports) can be compiled into a single report? That is, rather than selecting the upstream feed, have all the information that is contained in the three individual reports, appear on just one?

Another question is, how hard would this design be to extend to more levels? If for instance, I want to connect something to the bottom of load 1, can this be done just by entering new data with an upstream feed of Load 1? I tried adding a load with upstream feed Load 1, but there was no option in the form to select Load 1.

I have split the table into two as load master and load info.In loadmaster any feed having child is classified as upstream and feed having direct power info as downstream. I have added a sample substation2 to show the extensibility of the data without any change in program. You can extend the loadinfo table as you please and check the value of consumption of tree at anylevel.
I will come tomorrow if u need any classification.

Thanks again supersubra :-)

The database is becoming almost exactly what I require!!

One question I have is about query 3. In this there is the expression Expr 1. The actual expression is 'MSB1'. Is there any way rather than having the name always be MSB1, be able to call it from say the upstream feed list or something? That way if I change the names in the table down the track, it won't keep writing MSB1.

Also another question (I hope I'm not sounding picky, I really do appreciate all the help you've given me, but I'm just trying to modify it to do what I require). The report shows the level, upstream feed number, upstream feed description, tag number, description and power. Is there any way that the report can show only the directly attached levels and loads?

for example,

for MSB1, have this:
(I'm using underscores because the spaces don't work once I post)

MSB1

L1____________Load 1_________________2
MSB2__________Main Switchboard 2_______9
SB1___________Substation 1____________10

__________________________Total ______21

Rather than show all the individual loads connected to MSB2, SB1 as well as the loads connected to MSB1. This is just because eventually I will add many loads to this layout, and I only wish to write the details of these once, not for every path to the top (if that makes sense).

Let me just reinforce, I am extremely grateful for all this help. I hope I'm not complaining too much, I'm just trying to explain myself thats all.

Thanks again!!

Bakerboy

I am not using query3 and query2 within the program. Those are queries I used to get the SQL portion which is used inside the module. Both can be deleted. Yes It is possible to get the single level or fulldrilldown or bottom most only in the report for any top level feed. I will come with a better explanation and a modified realtreeviewreport pattern later. Possibly on sunday. Till then meddle as you please. Bye.

Another thing I'm noticing is that if I select all on the form, then the report shows the right info. But if I select MSB1, or SB1 or whatever, then the same info comes up each time (it seems to be showing the MSB1 info but with a SB1 or SB2 etc heading).

So basically there is no difference between selecting MSB1, MSB2, SB1,....etc. I don't understand why this is happening...