I'm chasing how best to store the field names from all "true" fields from a table in an array (recordset? SQL?) and feedback of whether I am going about this correctly.
We audit documents and each document can have any number of issues from a defined issues list (although this list grows over time as we find new ways users can stuff up a document). All the issues are in the format XXX.XXX.XXX (eg 102.101.135 refers to a missing signature).
Currently I store the data in a table (tblAHDIssues) which has the issue identification number as the field name:
ID(key) | DocID (num unique) | 100.100.100 (yes/no) | 100.100.101 (yes/no) | etc
I have a master table (tblIssuesMatrix) of all the issues including their identification number, descriptions and some other data such as how critical the error is.
I want to be able to give the users scrolling through the database a list of all the issues associated with the document they are looking at in plain English. The way I was hoping to achieve this was:
Use the DocID to look up the relevant row in the table (tblAHDIssues) and return an array of the field names of all the "true" fields. I can then use this array to grab the descriptions and critical rating for all those fields from the tblIssuesMatrix.
Is using a recordset the best way to achieve this? Is this just poor database design and should be fixed? Appreciate any help.
We audit documents and each document can have any number of issues from a defined issues list (although this list grows over time as we find new ways users can stuff up a document). All the issues are in the format XXX.XXX.XXX (eg 102.101.135 refers to a missing signature).
Currently I store the data in a table (tblAHDIssues) which has the issue identification number as the field name:
ID(key) | DocID (num unique) | 100.100.100 (yes/no) | 100.100.101 (yes/no) | etc
I have a master table (tblIssuesMatrix) of all the issues including their identification number, descriptions and some other data such as how critical the error is.
I want to be able to give the users scrolling through the database a list of all the issues associated with the document they are looking at in plain English. The way I was hoping to achieve this was:
Use the DocID to look up the relevant row in the table (tblAHDIssues) and return an array of the field names of all the "true" fields. I can then use this array to grab the descriptions and critical rating for all those fields from the tblIssuesMatrix.
Is using a recordset the best way to achieve this? Is this just poor database design and should be fixed? Appreciate any help.