Many to Many to Many Query Trouble (1 Viewer)

[Holly_Associated]

Good Morning Experts,

I have a DB with some many to many relationships for which I have used "join" tables. I believe I have done this correctly although I am quite new to normalisation and many to many relationships!

I've attached a simplified version with test data of the area I'm having trouble with.

I have been trying for many days unsuccessfully to create a query or series of queries which will result in what I have manually typed into TblEndResult.

I want this query to feed a subform so that when the user views the corresponding job record, they will be able to see what (Trade) and who (Sup/Sub) is on that job and which is doing which.
(My next problem will be creating the data entry form to link these!)

One of the difficulties is that there are blanks to be seen; we know what needs to be done but not who is doing... Or we know someone is on or supplied to a job but not what.

I have created the Union queries for my Supplier/Sub-Contractors and know they need to link to TblJoinJobToTrade but I'm completely out of ideas.

Any help or ideas much appreciated and if anyone cracks this mofo please explain how and why for me. I want to learn!

 

[CazB]

Just an initial thought... how are you linking each supplier or subcontractor to the trade they are supplying on a particular job?
Wondering if they should be linked to the JobTradeID in the tblJoinJobToTrade rather than linking directly to the job... ?

 

[stopher]

Hi Holly

I took a look at your database and it quite a bit ill-formed. In order to help, can you please first descibe your real world scenario. Here's my attempt but please correct it:

A supplier "provides" many jobs. A job is supplied by many supliers i.e. You have indicated this is many-many but is that really true? Surely one supplier supplies many jobs (not many-many) - how can a single job be supplied by more than one supplier?

A subcontrator can "work-on" many jobs. A job can be worked on by many subcrontractors. (many:many).

A job can "involve" many trades. A trade can "involve" many jobs i.e. many-many. Are you sure about this? Maybe you mean a job is associated with one trade? i.e. many jobs are associated with one trade?

Chris

 

[Holly_Associated]

Hi Chris,

I have a table called TblClient and this is indeed "One Client:Many Jobs".
The TblSupplier is material suppliers; So a job may need many suppliers just as a supplier delivers materials to many jobs. I understand this is similar to subbies which is why I did the union query on them.

For trade, we organise many for each job, just as each trade can be associated with many jobs.

For real life purposes, the DB will (hopefully) help us organise a little better. Currently everything is in files and searching for information is a nightmare. So if I want to know which Suppliers and Subbies can help with the labour & materials for bricklaying... hey presto. I have a question from a client about a job we worked on a year ago, I can tell them exactly who did their plastering etc. etc.

As I said, I've not worked with m2m before, and Sod's law, the first one I attempt is a bit more complicated than I would like!

 

[CazB]

Just to clarify things in my head, and looking at it from a Jobs point of view:

On a particular job, you may have one or more trades needed. eg: Job A, you only need plasterers. Job B, you need plumbers and plasterers.

On any one job, each trade can be supplied by a different subcontractor (the workers doing the job) (Job A's plasterers could be from Subcontractor X and Job B's plasterers from Subcontractor Y) - or could there be more than one Subcontractor supplying workers to the same trade on the same job?

Likewise the materials to do the jobs can be supplied by different suppliers, and you can have many suppliers on the same job - does this need to be tracked 'per trade' within a job, or just at job level?

From what I understand, You also need to track which Subcontractors 'can supply' which trades, and which Suppliers 'can supply' which materials... and to be able to backtrack and see who was used on which jobs / trades previously.

Is that what you're after? Have I got your definitions of what suppliers and subcontractors do right?

 

[Holly_Associated]

Hi Caz,
Yes to trades, one or more needed per job.
Yes, on any one job any trade can be supplied by any different SubCon and for instance
on job X, trade = electrics, we could have different SubCons doing the work; especially if it is busy or we have a deadline, quite common for us to have 3 from one company, 2 from another and an agency bod (which would be a supplier) on the job doing electrics. I don't need that much detail in the DB, but if I have the company names and what trade they were doing I at least have an idea what to look for in the file paperwork-wise!
The material suppliers are many to many and it would be really helpful to have them tracked by trade in the job, but at this point I'm tearing my hair out so much I'll accept a compromise!
The 'can supply' definitions are spot on, and yes, backtracking is key.

I'm wondering if a three way junction table is needed? (If that concept even exists?) At the moment, I'm manually opening the TblJoin's and entering the links, but I will eventually need a user friendly form version. That's another bridge for another day, but I'm fast learning with Access you need to plan all your blooming bridges before you've even started walking!
I like the challenge though, after I've worked something out of course

 

[CazB]

How about something along these lines?

I've changed your structure a bit... if you can't see what I've done, let me know and I'll explain!


... if that structure works, then it's time to look at how to build the forms

 

[Holly_Associated]

Thank you very much Caz for taking the time
Ah yes I see, use the JobTrade in the Join... I will play with this and report back!