Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    Join Date
    Feb 2018
    Posts
    19
    What is your definition of this?
    This might be a newb questions but can I avoid having to link my Primary Key in tbl_Ans (i.e, Ans-ID) to the FK (i.e, IA-ID and Q-ID)? The questions have to be linked initally in this design but I was wondering if there was a way to do this for answers also. In my initial design I only had a Questions table and an IA table, no QMS functions. That way I could have my Questions and Answers in the same table and this table was linked with a Many-to-Many relationship with the IA table. Even in this case before each audit I would have to link each IA to all the questions in the that were applicable. To avoid this I tried to create the QMS functions table which took care of the IA and Questions link but now I still have to link Answers to both the IA and the Questions somehow which I will try to attach here as soon as I figure out how to.



    I've been trying to attach a zip file which is well within the allowed limits but it doesn't get uploaded. I'll reply with the attachment as soon as I can figure this out.

    Thanks for your help though! Really appreciate it.

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the only thing I have time for at the moment is to post this link to see if it helps with your attachment. gotta run
    https://www.accessforums.net/showthread.php?t=70301

  3. #18
    Join Date
    Feb 2018
    Posts
    19
    That is what I have been trying. Maybe I need to clear by cache. I'll attach it by tomorrow for you to see. Let me know if you think there are structural issues as I could use that feedback. I think I also had my Eureka moment and kind of have a sample of what my final data entry form is going to look like. I'll add that to the post too. Make sure you have a look at that one first as I think it's more polished.

  4. #19
    Join Date
    Feb 2018
    Posts
    19
    So I finally managed to upload this. Test.accb is the one we have been discussing. The option2.accb is the one I just made today. It seems to be working fine. The only thing I wanted to check was how to add the Composite PK from jntblQuestQMS (i.e., QMS-ID and Q-ID) as a foreign key in tblAns as a FK. I want to be able to enforce referential integrity in the sense that the user must only be allowed to enter a combination of Q-ID and QMS-ID (I haven't added this field yet) to tblAns that exist in the jn table. This way the Answers cannot be associated to any other questions from any other QMS function.

    Hope the database and my questions are clear enough?

    Also, I created the form using the form wizard which allowed me to create a subform that had fields from multiple tables included. I was never able to do this using form design. Do you know how that works? Not that I'm complaining but I'd like to know just out of curiosity.

    Thanks.
    Attached Files Attached Files

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    this will take some time for a detailed look. 10 minutes in and I see things I would do differently. For example, I thought your term "function" meant a reference to an ISO standard but it's what I'd call a clause. A such, you have no table for standards, forcing you to enter them into the questions table, sometimes as multi-value fields separated by special characters (/) - not so good. Also, you're duplicating data such as the standard number, rather than using the PK of the parent field. This is because your parent field has no PK, just a composite index and your junction table has Q-ID in the index and as a PK so it's not correct. I think I see what you're getting at re how to link now. Let me know if you can't wait and I'll discontinue, as this review will not be finished today.
    EDIT:
    in your questions table, what is 7.1; 7.2 etc? Is the 7 a function/clause number for a given standard, and .2 is a sub-section? Or are you just trying to enumerate different questions applicable to clause 7?
    Last edited by Micron; 03-01-2018 at 05:18 PM. Reason: added questions

  6. #21
    Join Date
    Feb 2018
    Posts
    19
    Please do go through the entire design. I'd rather spend more time to make it efficient.
    in your questions table, what is 7.1; 7.2 etc
    It is as you mentioned the QMS function (or clause as you called it) and the part after the "." just being the order of the question within that clause. Just to make it easier to link to later on.

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    An old db I had on the subject that was built by someone else long ago seemed lacking so I dropped that idea.
    Here's the relationships as I see it at the moment, based on my own design. This is mapped out on paper, which I find always helps to begin with. This means the autonums from one table can be put into the other to see if it looks OK before having to do so in real tables. I think it should work but I haven't tested it yet - figured I'd let you see it while I try querying data. Here's the layout and some tables with possible test data (I didn't worry if all field names were exactly the same as what's in the pic). Some fields have just letters in them to aid with rendering these html tables.

    Click image for larger version. 

Name:	Rel1.jpg 
Views:	24 
Size:	39.0 KB 
ID:	32899
    tblStandards is any standard that you want to include in the db (MDD, CMDCAS 13485, ISO 14001, etc). ID 1 is MDD

    tblClauses links the standard to every clause in it.
    ID StdIdFk Clause Desc
    1 1 1.0 aa
    2 1 2.0 bb
    3 1 3.0 ccc

    Clauses is linked to questions. This should show that 3 q's were asked for 1.0, 2 for 2.0 and 1 for 3.0
    Qid ClidFk Qnum Question Req Prompt
    1 1 1 q1 for clid 1 what std says what to look for
    2 1 2 q2 for clid 1 what std says what to look for
    3 1 3 q3 for clid 1 what std says what to look for
    4 2 1 q1 for clid 2 what std says what to look for
    5 2 2 q2 for clid 2 what std says what to look for
    6 3 1 q1 for clid 3 what std says what to look for

    tblAuditQuestions is linked to the tblQuestions for the q id plus the audit table (not shown) to associate the audit id.
    This should show which question id's were asked for audit id #1. This is where I think the answers should go.
    AQid AudIDfk QuestIDfk Answer
    1 1 2 aa
    2 1 3 bb
    3 1 4 cc
    4 1 5 dd
    5 1 6 ee

    junction tblAuditClauses is for which clauses were part of the audit. This should enable to do partial (surveillance audits rather than all the clauses as you have it.
    This should show that for audit id #1, only clauses related to clause id's 2 and 3 were audited.
    AclsID AuditFk Clid
    1 1 2
    2 1 3
    So for audit 1, two clauses were audited (id's 2 and 3) [a join might be needed between tblClauses id and AuditClauses Clid as a fk]
    Question asked where the id's of 2 to 6. These questions are related to clause ID's 1, 2 and 3 (which just happen to be 1.0, 2.0 & 3.0) which are all related to standard 1 (MDD)

    Clear as mud??
    My guess is that there's one or more flaws in the logic - just wanted to let you know I was working on it. Will have to test.
    Last edited by Micron; 03-05-2018 at 10:54 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #23
    Join Date
    Feb 2018
    Posts
    19
    Ah I see. It does look similar to what I have under the option2.accb. I think the difference being is that I have left the Standards as a free text field while you have those set up in tables. The rest of your logic seems to coincide with mine unless I'm reading this absolutely wrong. The only reason I don't want to go your route is that we have multiple clauses in different standards that could have the same questions and we want to have it only listed once and answered once but according to tblQuestions would have that questions repeated x number of times and in turn it would need to be answered x number of times for the different standards that refer to it. Not a big deal breaker but that's just my understanding.

    Also, I mentioned this earlier but I created a subform using the form wizard that let me use fields from multiple tables and displayed all of them in the same subform. I'm guessing it created the Child-Master links itself but if I were to do this manually using form design how would I go about it? I could create a query with all the fields that I need in the subform from across different tables but then I loose the ability to enter data in, at least that has been my experience so far.

  9. #24
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    according to tblQuestions would have that questions repeated x number of times and in turn it would need to be answered x number of times for the different standards that refer to it.
    If this means you're auditing to two different standards at the same time, then I confess I've never heard of such a thing. If it doesn't, and you're just saying you don't want the questions to be in the table 2x because it's too many records, then that's odd. You'll be stuck when (mostly a matter of when I think) the 2 standards deviate enough from one another. If it's the labour someone objects to, it would take only a few minutes to create an append query that could post the same questions and clause numbers to the table while writing the standard name/number for every record.

    I created a subform using the form wizard that let me use fields from multiple tables and displayed all of them in the same subform.
    Never experienced this myself since I would have said it's not possible unless the wizard automatically built a query for you. My lack of experience with that stems from the fact that when multiple tables are involved, I'd go straight to building a query by myself and base the form on that - provided the query worked satisfactorily. That includes being able to update or append if it was necessary. Suggest you start by looking at the form's recordsource to see if it's a query. The linked fields property can be viewed on the property sheet data tab if you open the main form in design view and select the subform control.

    Seems the end result is that I haven't been much help, partly because you don't agree with the suggested approach and this really started out with a question about where to put your answers. When it comes to that, I think a junction table is the way to go; the rest you can forget if it doesn't suit the way you want to design it (repeating questions). As for my relationships image, I realized while dozing off last night that I included lookup tables that shouldn't be in that long chain, so I don't think it's completely viable solution as posted. Maybe I'll play with it later, but not much point right now if you're not going to go that route at all.

    As for not being able to update your form (assuming it is based on a wizard built query) here are some clues as to maybe why
    http://allenbrowne.com/ser-61.html
    You could also mean that you can't enter a subform record when the main form or a key field has no data. That would be normal. In any event, it's why I advocate building queries before forms. If the query is not updatable and has to be, there's not much point in starting out with a form that won't work.

  10. #25
    Join Date
    Feb 2018
    Posts
    19
    I'm going to pour over your comments this weekend. I'm really swamped for the rest of the week.

  11. #26
    Join Date
    Feb 2018
    Posts
    19
    I do understand your logic for having a unique standard link to a unique question. I'll bring that up in my updates regarding this project with my mangers and see what they would prefer. Our questions for now atleast are created to be wide enough to cover similar clauses across different standards. Your point actually makes perfect sense so thanks.

    For now, if you look at my option2.accb, I have linked questions to QMS Function/Clause plus I've also separately linked IA-ID to QMS Function. Do you think there's a way to only allow linking a IA-ID only to a questions for the appropriate QMS function in the Answers table? I'm not too familiar if a macro could help me do this. It'll be useful to know how to do this regardless of what route I take.

    You could also mean that you can't enter a subform record when the main form or a key field has no data. That would be normal. In any event, it's why I advocate building queries before forms
    Yeah I released this is normal behavior. I would need the primary key to exist in the table to be able to fill in the other fields in the main form. What I was having trouble with is if I had the subform pulling fields from multiple tables, it wouldn't allow me to enter any fields in the subform. I think with the design wizard it automatically creates a query as you pointed out - an update query to be more precise, because I tried to build a subform of a query separately, and that still didn't let enter values into the field. I probably didn't convert it to an update query.

  12. #27
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    if you look at my option2.accb, I have linked questions to QMS Function/Clause plus I've also separately linked IA-ID to QMS Function. Do you think there's a way to only allow linking a IA-ID only to a questions for the appropriate QMS function in the Answers table?
    this is the one from post 19?

  13. #28
    Join Date
    Feb 2018
    Posts
    19
    Yes. It's this one Internal Audit Option 2.zip (47.5 KB, 3 views)

  14. #29
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    if you look at my option2.accb, I have linked questions to QMS Function/Clause
    Not really. You've linked function/clause to the junction table, then the junction to Questions.
    I think you need to remove the PK's from the junction. You can add a junction pk, but the 2 fields you have as PKs now are FK's from the other tables. Since you are applying the same questions to multiple standards, you may have to (eventually if not right away) have duplicate values for the fk fields. You could have a composite index, but I do believe that it's customary that the fields coming from other tables are not also pks in a junction table. I also don't think the join from questions to answers is correct. If the answer was joined to the junction pk (which I'm saying you don't have) then the answer would be specific to the pk that identifies the question pertaining to the function. Right now, you have it joined to the question pk. I don't see how that identifies which standard that question is for. You don't want to have to try going through questions (from answers) to a junction, through that to the function to get the function data for a question.

    Not sure I'm making any sense. Been busy (and continue to be) with other things, so the thread is getting more vague in my mind as time marches on.

  15. #30
    Join Date
    Feb 2018
    Posts
    19
    Hi I'm sorry for the late reply. I'm out on vacation for the next few weeks and will have a look at your suggestion and my database again once I come back.

    Thanks again.

Page 2 of 2 FirstFirst 12
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