Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Feb 2018
    Posts
    19

    Creating a junction table


    I'm just starting with Access and have been working on a database to maintain records for different audits and their results. In the attached image, I have a main table (reference table tbl_QMS in this case) for the QMS functions that we have different audits for. Then I have a Questions table (tbl_Questions) that list out all the questions we have in the audit checklists for each and every QMS function, therefore the junction table (jn_tbl_QMS_Questions) which lists the which QMS function goes to which questions. I then have a table for actual audits (tbl_IA) which links each audit number over each year to the particular QMS function it is for. So different audit numbers could relate to the same QMS function. My ai is to create a final related table (if needed) which has as form-subform interface that lets me enter/select an audit number that was created in tbl_IA and then the subform should list all the questions that associated with that audit number through the QMS function it is associated with. How should I relate the audit number with the associated questions in this case?
    Click image for larger version. 

Name:	database relations.png 
Views:	37 
Size:	41.0 KB 
ID:	32689

    Thanks in advance.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I see no reason to have a table for your form. You should use a query for the subform, and if the design requires it, another one for the main form
    Also, advise the following

    qms function should be unique index so that function name can only be there 1x?
    consider unique composite index for qms-id & question id in junction table so that question can only be 1x per function
    no spaces, special characters in names (save for maybe underscore)
    Last edited by Micron; 02-21-2018 at 07:25 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Feb 2018
    Posts
    19
    Thanks. I was about to create a main form for the audit table and then create a subform based on the query for the remaining 3 tables as you pointed out. I still don't quite understand where I could create a field for the answers to each question in the audit? Should that be in the tbl_Questions?

    As for the index portion, if I do create a composite key using the qms-ID and Question_ID, how would I use that as a foreign key if that junction table relates to another table in a hypothetical scenario? And thanks for the suggestions. I'll keep that in mind.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can't think of a real good reason to put answers in a table separate from the answers other than one could say questions are one entity and answers are another. However, the idea is to not repeat fields unnecessarily between tables, and to separate them would require the duplication of every field that's required to relate the questions to the audit of the function. That would sway me towards an answers field next to the question field.

    if I do create a composite key using the qms-ID and Question_ID, how would I use that as a foreign key
    Look again. I said composite index, not composite key. They are not the same thing.

  5. #5
    Join Date
    Feb 2018
    Posts
    19
    Look again. I said composite index, not composite key. They are not the same thing.
    My bad. I see the difference.

    Regarding the answers issue, if I put an answers field next to the questions field in tbl_Questions, how will I create new instances for the answer for each IA-ID, i.e., the next audit for the same QMS function that occurs every year. Do you think the way I've set up tables is optimum for this? Maybe that needs to be fixed. Let me know if my questions isn't specific enough.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For the next audit for the same function, the audit id would be different, yes? So same question(s) for a function, same or different answers (won't matter) but different audit identifier. Thus the questions and answers may not be unique, but taken along with the audit id, they are a unique set.

    As for setup, I don't see why answers can't just be in the junction table. Not important, but I think your names are a bit too long, and I don't use the underscore (I find it to be a pain to type in because I have to think of where the key is, plus it's a 2 key operation). To me, tblQMS is easier. tblQA would be my choice for a junction table of Questons and Answers. Not that your way is wrong, but if each word has to be somehow represented in the table name, jn_tbl_QMS_Questions_Answers will prove to be a pain.

  7. #7
    Join Date
    Feb 2018
    Posts
    19
    For the next audit for the same function, the audit id would be different, yes? So same question(s) for a function, same or different answers (won't matter) but different audit identifier. Thus the questions and answers may not be unique, but taken along with the audit id, they are a unique set.
    Where do the different answers get stored as we fill them in though? I tried it once and what it does is that the answers field in tbl_Questions keep getting overwritten with what I type during the next audit ID for the particular QMS function. I'm pretty sure that I've designed something wrong.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If in the same table, then of course in the answers field. You probably didn't design so that the answer being input was going into the proper record. The answer belongs to the particular question for the particular audit (question id = q id AND qms id = qms id). For the next audit, the qms id would be different if I understand your design, so you shouldn't overwrite the last audit's answers. While you could do this with 2 subforms on a main form (1 for q's 1 for answers), I think you'd find it easier to use a continuous subform where the answer field lies under (or beside, if that's better) the question field. Then the empty answer control for a given question for a particular audit gets filled in and you end up saving the answer to the field just by leaving the record or the answer form control. If locking subform fields (such as the question) is required, I have seen this done where all fields are disabled but double click on the editable data control to show a popup modal form for altering the field contents. IIRC, click events don't work on disable controls so to use this method requires enabled = true and locked = true.
    Last edited by Micron; 02-23-2018 at 05:05 PM. Reason: clarification

  9. #9
    Join Date
    Feb 2018
    Posts
    19
    Thanks. Yeah I don't think my database is designed to store new answers for each audit-ID. The only reason why I have that table linking QMS function and Questions is so that that link between the two didn't have to be create every time-I would just link the new audit ID to a particular QMS function that already had a list of the questions.
    Looking at your suggestions I think the latter suggestions would be more applicable. What can I search for to find examples for what you have listed there?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure which is the "latter". I must have been thinking that your linking table was to link the questions to a specific audit but it seems that it's to link your questions to the functions. Sorry about that mistake; it's kind of obvious when I look at the image again. This seems to happen to me when I get involved in too many threads and there's sometimes days in between postings. Not a good excuse, I know. Basically then would this work for you,
    functions in 1 table, questions in another. Linking table for questions and functions (that's 3 tables)
    audit details in another (date, auditor, location, etc) and a table linked between questions and the specific audit (that's 5). This audit q&a would have the id of the question and the full text of the answer.
    Hope that helps.
    Last edited by Micron; 02-26-2018 at 11:45 AM. Reason: clarification

  11. #11
    Join Date
    Feb 2018
    Posts
    19
    Not a good excuse
    Don't sweat it. Regarding the solution you suggested, this is exactly how I had made it a week back except the table that links the questions and the qms function (table 3 in you example). That database was working fine but adding that junction table doesn't seem to add any value in this example does it? I would still have to create the link between the specific audit and the questions in table 5 as you pointed out which I was doing in the previous case. The end user wanted that to avoid creating those links for each audit and just create the questions-QMS function link before hand and that would stay permanent. I could just create an additional field in the audit details table where I link it to a QMS function .

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The end user wanted that to avoid creating those links for each audit and just create the questions-QMS function link before hand and that would stay permanent.
    I can't tell for sure, but it seems we're not on the same page. Try this for example (hopefully I don't mess up my own logic):
    tables functions and questions are just lookup tables (categories). These often just provide combo row sources.


    tblFunctions tblQuestions
    functionID Desc QuestionID Question
    1 music 1 favorite
    2 color 2 least favorite
    3 food 3 never tried


    this is the linking table that governs which questions can be asked regarding a given function.
    The data shows that for music and color functions, "never tried" (3) doesn't apply.

    tblFuncQuest
    FQ_ID Func_FK Quest_FK
    1 1 1
    2 1 2
    3 2 1
    4 2 2
    5 3 1
    6 3 2
    7 3 3

    Lets look at audit table for audit 1. The auditor, date, location, etc. might be there. The function audited was 3 (food). From tblFuncQuest, it follows that all 3 q's can be asked.

    tblAudits
    AuditID AudFunc Auditor
    1 3 5
    2 1 2
    3 2 6

    Last table is a junction of Audits and the questions that were asked. I elected to put the answers in the field beside the question field, which is the fk from the function/question linking table.

    tblAuditQs
    AuditQs_ID Audits_FK tblFuncQuest_FK Answer
    1 1 1 beer
    2 1 2 egg plant
    3 1 3 escargot

    Left to right in this table, follow the trail backwards as if linking tables in a query:
    - for audit 1, the function was 3. The linking table allows us to pick any or all of the 3 applicable questions
    - 3 were asked (because 1 is in 3 records). They were q's 1,2 & 3. To get the actual q wording, we link back to the q's table itself
    - the question wording for q1 is "favorite"
    - the answer to q 1 (from tblFuncQuest) is "beer"
    - tblAuditQs doesn't tell us what the function was. That comes by linking tblAudits.AudFunc to tblFunctions.FunctionID

    Hope there's nothing missing from my logic, but it's all from my head. I advocate doing this on paper or at least some sort of ERD app.
    I probably just confused things now...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Join Date
    Feb 2018
    Posts
    19
    Thanks. I've printed this out and will try to work on it today and tomorrow. Hopefully I won't run in to too many issues.

  14. #14
    Join Date
    Feb 2018
    Posts
    19
    Actually now that I actually sat down and went through your example, I do think I was thinking along the same lines. This is what I had developed last night (hope you can excuse the naming conventions for now): Click image for larger version. 

Name:	database relations.png 
Views:	25 
Size:	36.5 KB 
ID:	32793

    I think it matches what you were suggesting. I still don't see a way to avoid having to linking each Audit-ID with each question in that particular QMS function as you can see (tbl_Answers). Do you think I am going in the right direction with this? The query isn't particularly helpful except to populate the appropriate questions in the subform. I can't use it to enter the answers.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I still don't see a way to avoid having to linking each Audit-ID with each question in that particular QMS function
    What is your definition of this? Need to expound a bit on what this means to you. Of course you have to link each function with it's relevant questions, but it's a one time thing, so I'm not sure I see a problem with that. I don't see a valid link between your audit table to questions;-
    The function audited was 3 (food). From tblFuncQuest,
    which is probably why your query isn't working as needed. Either route you take from audit table, you have to go through at least one many to many link; through qms to get to qms questions to the left, and worse, through answers, then questions to get to qms questions going to the right.

    Why don't you post a copy of this db and I'll take a look at it?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to use a Junction table
    By gwboolean in forum Access
    Replies: 17
    Last Post: 09-15-2017, 03:06 PM
  2. Replies: 13
    Last Post: 05-10-2017, 12:43 AM
  3. Junction Table
    By troachjr in forum Database Design
    Replies: 1
    Last Post: 03-08-2013, 02:10 AM
  4. Replies: 3
    Last Post: 01-21-2013, 12:57 AM
  5. Junction Table
    By mae3n in forum Database Design
    Replies: 2
    Last Post: 01-15-2011, 10:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums