Question Displaying Many-to-Many relationships

[Arg81]

What's the best way of displaying many-to-many relationships on a form?

I'm trying to create a simple app to show Printers and Consumables.

Printers can have many consumables, and these consumables can be used on many printers.

I've created the three tables:
prn_Printers
prn_Consumables
prn_PrinterConsumables (the "middleman")

I have created the right relations etc in my app, and dragged onto the form to create the grids.

However, when I click a printer in the printer grid, I only see 1 consumable. I then have to go through each PrinterConsumable in that grid (which will end up deleted or hidden) to see the other consumables.

Basically I want a list of printers, and when I highlight a printer, see a list of ALL the consumables....

Help appriciated (I haven't had to do a MM relationship yet!!!)

 

[cjard]

Er.. You dont ever have or display MM relationships.. You just said:

"Basically I want a list of printers, and when I highlight a printer, see a list of ALL the consumables"

That's 1:M Printers:Consumables.


Think about it. If you had MM, it would bounce back and forth and become useless:
Click 1 printer, all consumables are shown, now try and show all printers for those consumables, now try and show all consumables for those printers..

Eventually you'd just show everything

 

[Arg81]

consumables can be used on many printers.... so that scraps that one to many.

For example, OKI Type 9 cartridge and drum can be used on 3 different OKIs


EDIT:

Printers: PrinterID, PrinterMake, PrinterModel, Comments

Consumables: ConsumableID, Type, Comments, Qnty

PrinterConsumables: PrinterID, ConsumableID

Printers to PrinterConsumables: one-to-many
Consumables to PrinterConsumables: one-to-many

Obviously no relationship from Printers to Consumables directly.
I've attached a screenie to try and explain more. When I click on a printer in the grid, the printerConsumables shows only the related data for that printer. However, the consumables show EVERY consumable in the table as there's no direct relationship.
I want the current ConsumableID's in the PrinterConsumable grid to be shown in the Consumables grid, and for this to filter correctly as I change printers....

 

[cjard]

consumables can be used on many printers.... .

Great, but this screen isnt showing that fact, so this screen is showing a 1:M 1printers:Mconsumables
And we dont care that those M consumables can be used in N printers, or that those N printers can take O consumables.. see how it runs away to infinity(whole set) ?


I get the problem, really, and you have it set up the right way.. Basically, i'm telling you to arrange your dataset like:

printer 1 : M (middleman table) N : consumables

And when youre on the "Show all consumables for a printer" screen you will have:
GrandParentBS: Printers
ParentBS: Middleman
ChildBS: Consumables

And when youre on the "Show all printers for a consumable" screen:
GrandParentBS: Consumables
ParentBS: Middleman
ChildBS: Printers


Dont try and show them on one screen, you'll tie yourself in nots