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.
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.