One to Many to Many Relationship

[hokiewalrus]

I have a new database that I'm trying to design an interface for.

It tracks a series of plumbing job, and each job has several tickets (1 for each day a plumber is on the job) and each ticket has several lines of materials.

What I want is a screen where you open a job and it lists all the tickets, then you double click on a ticket and get all the lines of that ticket.

The problem is I don't want the ticket lines form to be a popup, I'd prefer it open in the same subform container as the ticket subform, but there is no direct relation ship between the job (parent form) and ticket lines (new child subform). So my question is what to people typically do when you have relationships more than 2 levels deep? Denormalize?

I feel like I'm just not thinking of this right way.

 

[Access Hero]

Never denormalize. Nest your sub-forms 2 deep. There are samples on Microsoft's Access training site.

 

[hokiewalrus]

Never denormalize. Nest your sub-forms 2 deep. There are samples on Microsoft's Access training site.

Except the first subform is a continuous form so it can't contain another subform.

I'm really just curious how people represent these sorts of nested relationships on the screen, cause I can't seem to find a way that makes a ton of sense.

 

[Scooterbug]

It's not the difficult. I would use a combo box to display a list of Plumbing Jobs. From there, you can populate a list box with a list of the tickets associated with the Job. From there, you can set it up that when you double click on a ticket, it will populate a subform which will display all the materials.

I am assuming that your table structures are similiar to this:

tblPlumbingJob
PlumbingJobID Primary Key
PlumbingJobNumber
Other items you wish to include

tblPlumbingJobDetails
JobDetailsID Primary Key
PlumbingJobID Foreign Key
DetailDate Used to record the date each material was used
Qty Quantity of Material Used
MaterialID Foreign Key to reference the material information

Not sure of your level of knowledge with access...but basically use a combo box to display the Plumbing Jobs by the PlumbingJobNumber. From there, use the PlumbingJobID as a criteria for the list box row source. The list box should show the date for all details where the PlumbingJobID is equal to the PlumbingJobID selected in the combo box.
You can then use the Double_Click event on the list box to filter the subform that list the materials based on the date from the list box.

 

[Access Hero]

Except the first subform is a continuous form so it can't contain another subform.

Just make the "second" sub-form another sub-form on the main form but link it to the first sub-form. This is the method I usually use.

Or, make the first sub-form continuous (or even single) and embed the sub-sub-form in that.

Or, flatten out the first 1:M (using a view, not by denormalizing) and put the sub-sub-form as a sub-form on a main form made from the flat view.

Lot's of ways to skin this cat.

 

[hokiewalrus]

Just make the "second" sub-form another sub-form on the main form but link it to the first sub-form. This is the method I usually use.

Thanks for the replies.

This looks most like what I want to do, but how do you link a subform to another subform and not to the parent form?

 

[Banana]

Try this tip.

Note: Though they say to use textbox, I'm told that it need not be the case and you can link to the subform control directly if you have the right syntax.


Also, there's another 'cheap trick':

While you can't put a continuous subform in a form that's continuous itself, you certainly can put it in a header or footer section of the continuous subform (though the Access will warn you that it can't be done and reset it, it won't stop you from resetting the form back to continuous form.). This requires no code on your part.

 

[Scooterbug]

Here is an example of how I would skin this cat....

 

[hokiewalrus]

Try this tip.

Note: Though they say to use textbox, I'm told that it need not be the case and you can link to the subform control directly if you have the right syntax.


Also, there's another 'cheap trick':

While you can't put a continuous subform in a form that's continuous itself, you certainly can put it in a header or footer section of the continuous subform (though the Access will warn you that it can't be done and reset it, it won't stop you from resetting the form back to continuous form.). This requires no code on your part.

Success!

I never knew you could just type in a Control in the Master Link Field rather than a field (since if you try to do this after clicking on the "..." it gives you an error), that really opens things up a lot.

Thanks for the help everyone, i really appreciate it. As always, you guys save my bacon.

 

[Banana]

Yes, Expression Service (that's the component that let us do stuff like doing calculation in a textbox or get a value from another table for a form and the like) is quite powerful but it's also quirky in that it doesn't always tell us what it is actually capable of and what it can't do.

Just because it's not in the list doesn't mean it can't be done but there are some stuff you can't touch. An example would be being able to retrieve Dirty property of a form even though the Dirty property is not listed in the expression builder, but you can't deal with form's Recordset property at all.

Glad they were able to help you.