Hi,
Mucho gracias if you pros can help me! I've only completed 1 year of a degree...

I have a many to many table relationship that I have resolved with another table- therefore I now have two one to many relationships, fairly standard stuff. Basically I need to list items under a schedule. So currently the tables may be like this:

1 Schedule record can have many item records.

This is no problem but the schedule MAY be split up into various sections. E.g.

1 Schedule record can have many sections.
1 Section can have many items.

Okay but what if I want to store items either under a section or straight under the schedule?

1 Schedule record can have many sections.
1 Schedule can have many items.
1 Section can have many items. (Easy way out have a section that's PK'd to represent the Schedule)

Now the tricky bit.. where I need help really from a pro dude/dudess.

Can I have a set of relationships where the user can create sections and sub sections of those sections all within a schedule, and can then store items under the sechule itself, under a section, under a subsection of a section, under a subection of a subsection of a section? I.e. infinite nests? Sounds crazy- maybe it is. But this is a real life situ! :o

Many thanks,

Simon

You can have basically whatever you want.

You had many to many relationship that you resolved quite correctly.

What you need to do is to firstly complete a conceptual diagram where you purely show the relationships be they M:N or 1:M. The important point here is to show all relationships between the entities you have identified.

When you have done this then you resolve M:N relationships

So the answer to your question is

Define what is required
Then decompose M:N relationships to 1:M relationships

HTH

Len

Well, thankyou.. I've tried but I think this one is sending me doolaly. Can anyone glean my problem from this?

(Please see attached erd pic)

Clear the mind and go to beginning

You cannot have a data structure that grows at the whim of a user. You can have a data structure that allows for sub sets but you need to define how many subsets. Think of it in the way of the Structure of an item

A Car

Car is top item
consists of Engine
Body
Gearbox
Etc

Each of these consists of bits and those bits consist of bits etc which is your problem.

Well the only way to resolve is to say
You can have as many layers as you like providing it is not more than X. You need to decide X

I have done this with structures but good reasons for going to lower levels because with each layer the complexity doubles at least.

But its fun


So dynamic structure.......No

Len B

edit add

Okay so you could build in some very fancy chunk of code that added a table of known definition and referred to a data table to decide its name and what referential links should be but you would be building yourself a bit of a nightmare in my opinion.

L

Thankyou for your help. Instead I think I may take this route:

To just have the simple saving items under a schedule format, however...

Create a new table realted to the schedule called sections:

1 schedule can have many sections:

Sections table:

ID... number
Schedule ID .... Num
Heading.... Text

Then in the table that stores the items to the schedules, i.e. the resolved schedule-items table each item has a section ID that is normally 0. (0 = attached to schedule) and will be listed under the schedule, (You can also assign the item to the ID of the heading.... (1 = first section etc) catch my drift.

You can then assign sections as children of each other by adding a bit of fancy code that assigns the section as a child of the one above it if there are no items stored under the above section. Apply a bit of conditional formatting... And bob's your uncle.

Thankyou for bouncing ideas off- very helpful.

Many thanks,

Simon

If I'm thinking right - late in the day now - you want a table that has a one to many with itself to manage sections in one table being a subsection of a section in the same table.

Bouncing ideas is the way to develop the solution cos only you have a full grasp of your requirements but sometimes we all get a bit tunnel visioned

Len B

It looks like a hierarchial relationship -
Schedule-->Section-->Item
with the Section part being multi-leveled.

You can add a field called ParentSectionID to the section table. This will allow you to make the Section table n levels. Each section has a ParentSectionID except the top level in the hierarchy. You create a single relationship between two instances of the Section table in order to enforce RI between ParentSectionID and SectionID. Take a look at geneology databases or Bill of Material (BOM) databases to see how this type of relationship works.

If you know the maximum number of levels of Section, you can create queries that traverse the hierarchy. If not, you would need to write a recursive VBA sub to traverse the hierarchy.

What quality replies! Sorry I haven't been in touch 'till now but I have multi Access projects looming. So do you pros make a living out of DB design- Pat and Mile?

I'm looking at your sample databases to see if I can get my head around this relationship. I almost wish I stayed on my degree......

Cheers

So do you pros make a living out of DB design- Pat and Mile?

Pat does, I know that.

As for me, I have no computer related qualification. I taught myself about 2-3 years ago.

With respect to the programming side - it's been a hobby since I was four so it was no great hardship making a jump for linear BASIC on an AMSTRAD CPC 464


10 Rem MyFirst Program
20 Rem by Mile-O-Phile
100 PRINT "What is your name?"
110 INPUT a$
120 PRINT "HELLO " & a$
130 END

to event driven code in VB.

:)

Ah I C! I started in qbasic when I was 10 and followed a long winding road to becoming a CAD techician who just writes DBs for employers....

I'm wandering if there are any examples of this one to many within itself relationship? I've searched the posts and sample DBs. I'll give it a whirl anyway and see if I can work with it.

A recursive code would be needed to write reports etc. I think this will make for interesting form designs. I am actually trying to show this hierarchy in a control where you can add and delete children/parents etc (sounds violent!) .... something like that.... seems like a complicated control to implement. Anyone any ideas or shall I take this post to the forms forum!!!

If I get a nice little streamlined number I'll post this baby up in the samples section, if anyone's interested.

Cheers

What version of Access are you using and I'll se if I can demonstrate this with the TreeView Control.

Thankyou! I'm using Access 2000 and treeview control version 5.0 (Sp2) I've had a look at an example, but to be honest my understanding was limited. I wander if the ability to add a record (or node) from within the control is possible?

If you add the table which supplies the TreeView Control with its data then you can and remove - you just need to rerun the sub that creates its structure.

It's not an object I use but I'm sure you can add, delete, and rearrange with it.

Thanks to your help Mile I can begin to implement this. I do wander if this one to many relationship within a table can be set up in access or shown in the relationships diagram, i'm very curious as to that the realtionship would look like!

Thanks again- hey do you get paid for this??

You should!

To create the relationship, you just add the table to the grid twice. Access will suffix each subsequent instance of a table with an underscore followed by a number so the first "duplicate" instance is labeled tblYourTable_1. You then draw the appropriate join lines and enforce RI and Cascade Delete. Here's a db that uses a self referencing table. It only goes one level though so it is not complex at all.
Code Table Maintenance Sample (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=32100)

do you get paid for this?

No, I don't. Like everyone else, it's voluntary.

Just like to say to Pat and Mile. Well I certainly learnt from this as well. I use A97 and have had a couple of situations where I have had to use a recursive relationship. I have limited this recursion to 7 on both occassions. This has proved to certailnly be sufficient but it is very interseting to learn that a dynamic recursive situation is possible,

Always leaning something

Len B

To create the relationship, you just add the table to the grid twice... You then draw the appropriate join lines and enforce RI and Cascade Delete.

Hi Pat (or anyone who may answer!) :)

Thanks for the tip, although I do have a problem with the appropiate lines bit. I am having sever trouble with the ref. integrity part when I try to do this. To illustrate- this is the recursive table and it's realtionship with itself:

tblHeadings
Section_ID (one)-------|
NodeText
Parent_ID (many)<----|
Schedule_ID

I'm just trying to get the join lines correct in the Relationships Window

You should have:

tblHeadings and tblHeadings_1

You should have:

tblHeadings and tblHeadings_1

I do, that's no problem- it's just the link between the two I can't figure... I join the SectionID to the ParentID of the table and the Schedule ID to the Schedule ID (because the primary table is two part PK), enforce ref.integrity, and apparently I am missing records in the adult table---- But I'm not I swear!!!! SO am I linking the wrong fields I wander?

It's a crazy world in computers...

Hey, just to let anyone know and see if the pooled knowledge is far superior to mine? Can anyone actually create a 1 to many relationship within one table?! I know the theory, tblheadings_1 etc but the fields with which to link just escape me.

Not sure about this but

Is it possible to link tables when you have a multi field PK in the Parent Table. I Have doubts. Let me explain.

Yes I believe it can be done and is covered within Relational Database theory. I also believe that SQL within DDL covers the situation but doing it within the Relationship display of Access. Hmmmmmm I have my doubts


I think (but am not certain) that it may be necessary to create a single key PK to link with another field acting as the FK

JUst thoughts

Len B

I see where your coming from. I think it can be done within the Access window though. Does anybody know a final conclusive answer or maybe even have an example for us to oggle at? An Access Compatible recursive relationship... one man's dream...

another mans nightmare! :eek:

I posted this one yesterday for somebody...

Thankyou very much! This is an excellent simple illustraion of using the recursive functions

Just one more thing...

I think it is impossible to have a multipart key in a recursive table and have this same relationship??