Many-to-Many-to-Many Help - trying to build an database to detail a portfolio of apps

[Jaccbox]

I need help, please.



I am trying to build a portfolio of applications for my business, and I know it will be better to build it in Access rather than Excel, but as much as I try I can’t fathom how to structure it. I can only liken it to trying to take a 2d image and turn it into 3d! I am beginning to question my own sanity.

There are possible many-to-many relationships throughout the data, and if I structured it in excel it would look like this.

Business	Business Area	Layer	Application
Sector 1	Apple	UI	Black Software
Sector 1	Orange	UI	Red Software
Sector 2	Apple	Security	Yellow Software
Sector 3	Apple	eComm	Pink Software
Sector 3	Orange	Documents	Purple Software
Sector 3	Pear	Documents	Black Software
Sector 3	Banana	UI	Red Software
Sector 4	Pear	Security	Yellow Software
Sector 4	Banana	MI	Black Software

Black Software in this example is what is killing me, it sits within multiple businesses, can be found in numerous business areas and across numerous layers.

On top of this, applications can sit within applications – for example Red Software might be part of Black Software... much like a plug-in sits within an application.

I am sure someone who’s head is clearer than mine will look at this and find it a simple problem to solve.
If I have posted this in the wrong place I apologise – i look forward to your replies with great interest!

[ranman256]

I think your list is fine. For your other example , could have

Sector 4,

Black Software,

MI,

Red Software

[Jaccbox]

Thanks Ranman... so are you suggesting create just the single list in Access?

My table view represents what I have in Excel, and I was hoping that access would mean that I would have only one instance of each thing... ie; 1 Black Software, 1 Apple, 1 MI etc and then the relationships would create the multiple outcomes.

Doe that make sense? I am having one of those days where it might be a danger for me to cook tea tonight!

[ranman256]

You could make a self related list. A single, self-relating table...with a parent ID on the record that shows who owns it.

Like all children have the same parent ID.
That parent ID , when you go to that record, could also have a parent.
The record at the top has no parent. It would 'own' everything under it.

[Jaccbox]

Ah, so in a hierarchical sense? I still feel that would leave duplicate fields - but will give it a go and report back with my attempt.

Really do appreciate the help. Thank you.

[Jaccbox]

So, I finally got round to looking at this again this afternoon. Do I have the right idea for these many-to-many relationships?

Division	jDivisionProducts	Products	jProdDom	Domain	jDomainFunction	Function Layer
Legacy Division	LegDivLeg	Additional Benefits	AddBenDoc	Documents	eCommBusServ	Business Services
International	IntInt	Legacy	AddBeneComm	eCommerce	eCommFramework	Framework
UK Division	UKDivAddBen	International	AddBenProp	Finance/Inv. Admin	eCommIntergation	Integration
	UKDivGuard	Guardian	CorpFinInv	Proposition	eCommSec	Security
	UKDivClose	Closure	LegDoc		eCommUI	UI
			LegeComm
			LegFinInv
			LegProp
			IntDoc
			InteComm
			IntFinInv
			IntProp
			GuardDoc
			GuardeComm
			GuardFinInv
			GuardProp
			CloseDoc
			CloseeComm
			CloseFinInv
			CloseProp

The columns such as jProdDom are the junction boxes. I would then create the relevant primary/foreign key relationship throughout the data to join it all back together. I think I heard the "penny drop" when I was doing it, or it could have been the last brain cell closing the door on the way out....