Junction Tables Help

[cruzinonline]

I saw a previous thread discussing junction tables and I think that is what I need to use, however, I'm having a hard time "seeing" how this works. After reading a couple of articles, I was hoping someone here would be able to use what I have and see first, if this would be the solution and second, kind of tweak what I have so I could better understand how this works. I will be in training all day so I will not be able to respond for a bit. Any help would be greatly appreciated.

In this case I have one table with a list of topics and questions. Another table logs in the basic information and I have another table that I probably don't need.

There is a sample form of what I am trying to get at. Basically an employee goes into the field and observes a working crew. He then logs in his observations. There are 12-13 different types of observation topics with 8-25 questions per topic. The employee should be able to choose which topic and then address each question pertaining to that topic by entering a number in 1 of 3 columns. The first column would be satisfactory, the second would be needed some guidance, and the third would be for "they don't get it".

What I would like the person be able to do is enter on the form there name, date, comments, what they observed, and then pick from a drop down the topic. From there it would be nice if the subform autopopulated with a list of questions where the user could enter a number in the safe, ok, or unsafe column. Even if they could drop down a list of questions related to the topic would be nice.

From there, I would run a report based on a date range, and grouped by Topic with a summary of each persons observations or another report by date range that would group by person and then sort by topics. The reporting part should be a breeze, it's just collecting the data that is stumping me. This is a little more detailed that what I am used to and I am thinking the junction table would be the solution.

The form you currently look at lets me choose a topic and on refresh, loads the questions but I am not able to enter any data in the subform.

Hopefully the attached file is available for your review. Thanks in advance.

Ray

 

[CJ_London]

I saw a previous thread discussing junction tables and I think that is what I need to use, however, I'm having a hard time "seeing" how this works.

Say you are an assembly company making things by putting different components together. For this example they make buckets. There are two components, a bucket and a handle and each bucket and handle are unique to each product

The data structure you would might have this structure

FinishedProducts
ProductPK
ProductName

data
ProductPK ProductName
1       Bucket A
2       Bucket B
3       Bucket C

Components
ComponentPK
ComponentName
ProductFK

data
ComponentPK ComponentName ProductFK
1       BucketA Base                1
2       BucketA Handle              1
3       BucketB Base                2
4       BucketB Handle              2
5       BucketC Base                3
6       BucketC Handle              3

Then someone has the idea to put the handle component from bucketB onto a bucketC Base to make a brand new bucket product BucketD

Now the above design won't work because one handle belongs to two buckets, as does the bucket component

ComponentPK ComponentName ProductFK
1       BucketA Base                1
2       BucketA Handle              1
3       BucketB Base                2
4       [COLOR=red]BucketB Handle[/COLOR]              2
5       [COLOR=red]BucketC Base[/COLOR]                3
6       BucketC Handle              3
7      [COLOR=red] BucketB Handle[/COLOR]              4
8       [COLOR=red]BucketC Base[/COLOR]                4

So the component is repeated - which may be acceptable in the short term but longer term, when the company wants to reorder the bucketB handle they now need to look in two places.

This is where the junction table comes is. The tables are redesigned as follows

FinishedProducts (no change)
ProductPK
ProductName

data
ProductPK ProductName
1       Bucket A
2       Bucket B
3       Bucket C

Components (remove the link to the product table)
ComponentPK
ComponentName

data
ComponentPK ComponentName 
1       BucketA Base  
2       BucketA Handl
3       BucketB Base                
4       BucketB Handle       
5       BucketC Base       
6       BucketC Handle

Assembly (new junction table)
ProductFK
ComponentFK

data
ProductFK ComponentFK
1     1
1     2
2     3
2     4
3     5
3     6

So now to add the new product, you just add the details to the assembly table (plus of course adding the new product to the product table)

data
ProductFK ComponentFK
1     1
1     2
2     3
2     4
3     5
3     6
[COLOR=red]4     4[/COLOR]
[COLOR=red]4     5[/COLOR]

The other benefit of this style of table is that you can also easily determine where components are used.

In the first example we are saying any component can only belong to one product (one to many relationship) and in the second we are saying any component can belong to any product (many to many relationship)

So - whether you need a junction table depends on how you need to structure your data. An invoice header table and invoice detail table would be one to many - you wouldn't have one invoice detail line appear on more than one invoice for example.

In your case, if the questions are the same regardless of topic, you need a junction table (many to many) and if the questions are unique to each topic you don't need a junction table (one to many). If they are a mixture, then go the many to many route.

Hope this helps

 

[cruzinonline]

Okay, I'll try to work with that response. What would have been nice if you could have used what I am actually doing as an example, LOL. I guess I'm more of a "visual" type person, I guess it's the Auditory Processing Disorder. Thanks for your time.

 

[CJ_London]

I would have done if you had provided more information

In your case, if the questions are the same regardless of topic, you need a junction table (many to many) and if the questions are unique to each topic you don't need a junction table (one to many). If they are a mixture, then go the many to many route.

Edit:Sorry, just seen your db is attached