Displaying one to many to many

[haze]

Hello!

I have been struggling with this problem for a while and I'm so deep into it I can't look at it clearly anymore. I hope someone can give me some advice.

I'm trying to set up an orders database, but instead of one "product" I have a service with many specifications. One service part can have many kinds of inspections, which in turn have many inspection criteria.

That part seems okay to me, I guess. There's a one to many relationship between services and inspections, and a one to many between inspections and criteria. The problem is displaying that in a form in a way that's not confusing to the users - I can't put all that info in one line, and I can't put subforms in continuous forms, the best I can come up with is to have separate subforms that display the inspection types when the part number is selected and the inspection criteria when the types are selected. The users I tested it on are totally confused about it. What's the best way to go about displaying it?

I secretly just want it to look like this!

[B]PartNumber     Service          Criteria               Price[/B]
1001              Gauging   (for) Length              $100000!
                             (for) Diameter
                 Sorting    (for) Rust
2222              Sorting    (for) Rust                  $10

Maybe that's just as confusing. A fresh perspective would help.

This forum has been such a wonderful resource to me and I've learned a lot. I feel kind of embarrassed posting because I know my questions are access-ignorant, but I'm really having trouble figuring this one out on my own. Thanks for any ideas.

 

[The_Doc_Man]

I'm not sure about your description, but it sounds to me like you have a simple case of 1:many :: invoicesarts and a 1:many :: parts:services

This is covered, for forms, under this forum's search function, title "Cascading Combo Boxes" - in which each line item in the invoice "enables" the selection of services that correspond to the selected parts only.

By the time you have selected what there is to select and are ready to report on it, the foreign keys should all be in your records. At worst, you have a two-layer join, invoices to parts as one query and parts to services as the second query. BUT the second query is off the first query (which can contain all the information you need from the parts table so you can support the join to the services table.

Instead of

Invoice >> parts >> services

you do

Query( Query Invoice >> parts ) >> services )

This works because queries just want recordsets as inputs, and a query is itself a recordset. A limitation of Access prevents you from doing the direct (triple-way) join in a single query.

Does that help?

 

[haze]

Thanks for your help Doc Man! I'm not sure I understand, but comparison to parts and invoices is a useful way to think about it that I'll have to chew over for a bit.

If I use cascading combo boxes, does that mean I can only have one service and one criteria per line? What I wanted to represent was one "invoice" line with one part but many services selected, which each have many criteria.

The idea was that a service on a part has one part number and one price but lists all the inspection types the part will undergo, which is usually 3 or 4, as well as the criteria for each inspection type which can be about 5 for each type of inspection.

Maybe I am overcomplicating!

Thanks for your help