Return field name into array where field is true (1 Viewer)

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:31
Joined
Aug 30, 2003
Messages
36,118
That last thing. ;)

The issues should be records in a related table, related one to many by the document ID. Then it's a simple query join to get the descriptions.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
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
The "best" way to accomplish what you want to do is to "fix" your table structure. Using data as field names is not the best way to design a database. Each issue should be a record in your audit table. Then, it would be trivial to find out what issues each document has.


Edit: Oops, too slow...
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
I suspected that the design was poor but let me add some more info.

When entering the document details there is a table (tblDocumentLog) which contains the DocID and all relevant info to that document (Name of person completing etc)

eg:

Code:
DocID |    Name    | DocType | Site
5677  | John Smith |   AHD   | North
5678  | Jason Doe  |   EPA   | South

When the users select the appropriate document type they are presented with a sub form which lists all the possible problems for that document type as check boxes. Not all problems relate to all documents and the list is large.

eg the user selects "AHD" - the sub form is displayed with:
[] Issue description 1
[] Issue description 2
[] Issue description 3

They can tick none, one or many of the issues. Only the relevant issues for that doc type are displayed.
I have this data stored in a table (tblAHDIssues linked via DocID) with the format:

Code:
DocID | 100.100.100 | 100.101.100 | 121.101.100 |
5677  |      []     |      [X]    |      []     |

(again not all docs have all the issue types, each doctype has its own table with unique list of issues, max about 30 issues).

There is then a master table (tblIssuesMatrix) of all the issues:
Code:
  IssueID   | CriticalLvl | Description
100.100.100 |      1      | Desc Text here
100.101.100 |      1      | Different Desc Text here
121.101.100 |      1      | More Desc Text here

I am wanting create that link between the tblAHDIssues and the tblIssuesMatrix so I can access the descriptions etc of ONLY the selected (true) issues.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
Hi. And if I may... It doesn't matter what you want to have as a user interface for data entry. You should fix the table structure first, then worry about how to present it to the users. So, maybe something like:


tblDocuments
DocumentID, pk
etc.


tblIssues
IssueID, pk
etc.


tblDocumentIssues
DocIssueID, pk
DocumentID, fk
IssueID, fk


tblAudits
AuditID, pk
DocumentID, fk
AuditDate
etc.


tblAuditIssues
AuditIssueID, pk
AuditID, fk
IssueID, fk
etc.


Hope that helps...
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
OK - now is a good time to redesign as I have some other changes to make.

Looking at that layout it looks to have an extra layer of "audit details" and "audit issues" and although it is good practice to record the audit details I think the table of audit issues is probably not relevant (or am I missing something - document issues already exists)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
OK - now is a good time to redesign as I have some other changes to make.

Looking at that layout it looks to have an extra layer of "audit details" and "audit issues" and although it is good practice to record the audit details I think the table of audit issues is probably not relevant (or am I missing something - document issues already exists)

Hi. DocumentIssues are those things you were using as checkboxes, and AuditIssues are those checkboxes that you marked as checked in your current audit process. In the new structure, you don't need a record for an issue that passed audit inspection. Hope that makes sense. If not, I'll try to explain again tomorrow. Good night.
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
OK I tried it out on a blank database to see if I could get the build right. I did not include an Audit Issues table but including a field in the Issues table as to which document type it applies to.

I did add an extra table with the "document type" so this could be reused between the main documentlog and the "applies to" field in the issues table.

Is this the correct way to set this up? I still could not figure out the additional table you suggested. I haven't built the forms for input yet so is that what the extra table is related to?

I included some dummy data and was able to run queries (included in the db) on the data.
 

Attachments

  • Database2.accdb
    500 KB · Views: 166

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
OK I tried it out on a blank database to see if I could get the build right. I did not include an Audit Issues table but including a field in the Issues table as to which document type it applies to.

I did add an extra table with the "document type" so this could be reused between the main documentlog and the "applies to" field in the issues table.

Is this the correct way to set this up? I still could not figure out the additional table you suggested. I haven't built the forms for input yet so is that what the extra table is related to?

I included some dummy data and was able to run queries (included in the db) on the data.
Hi. Sorry for the delay...



What I called tblDocuments, you also called tblDocuments in your sample db. It looks okay except for the extra ID column, which you could probably delete.


What I called tblDocumentIssues, you called tblIssuesMatrix in your sample db. I'll have to ask a question since you added the DocType into the mix. Could an issue be applicable to more than one DocType? If so, you'll have a 1-to-many relationship, and you'll need a child table for that. Otherwise, I guess it's fine except for the Primary Key part. Either use an Autonumber field for your relationships or use the Natural Key (like the IssueID). Try not to mix them up. What I mean here is you designated an Autonumber field as a PK but used the IssueID as FK to the DocumentIssues table.


What I called tblIssues would be the Parent table I was referring to the in the previous paragraph. You would have IssueIDs listed here and in the DocIssues table, you would select the DocType and the IssueID to match them up. But if an IssueID can only be applicable to one DocType, you might not need this.


What I called tblAudits would have been a table to store which document is being audited by whom and when. Not sure if you need this because you didn't create one in your sample db. This will depend on your business process.


What I called tblAuditIssues, you called tblDocumentIssues in your sample db. This basically records the results of the audit. If you don't need an Audit table, then the way you have it as using DocID would be fine. However, if your business process requires more than one audit per document, then you may have to create the Audits table.


Hope that helps...
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
OK I think I have the overall design correct then - I do already have some audit logging in another table which works well and I didn't want to include to muddy the waters.

The extra ID fields are redundant but the numbers generated were originally a complicated system which didn't fit well with autonumber. Probably something I could change back now.

There is only 1 audit per document but there may be multiple problems. The issueID does only apply to one document type at this stage but I thought I would build it to be a bit more flexible in case we have some crossover in the future (we add new doc types every now and again).

This is what I am really struggling with though and the reason behind me having such a poor design to start with:

How do I design a sub form which only displays the relevant issues to that document type so users can just select a check box when they see a relevant error.

I achieved this in the previous version by having the the user select the doctype and the db would show the relevant subform with all the checkboxes. This was easily stored as each doc type had its own table and all the check boxes related to an individual field in that table. The table was then linked via the DocID to the master document table. The true/false for each error would then be recorded in the document type specific table which each docID having its own line.

I just don't know how to do this with such a fundamental change to the layout.
 
Last edited:

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
I am thinking this now belongs in the design forum as it is moving well away from VBA to very basic concepts.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:31
Joined
Oct 29, 2018
Messages
21,358
I am thinking this now belongs in the design forum as it is moving well away from VBA to very basic concepts.
Hi. Feel free to start a new thread with your new topic. Good luck!
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
OK after creating new thread it looks like the better DB design would require a lot of custom VBA programming to display the forms (and possibly some resource intensive lookups etc).

I will further investigate the change of DB design as a separate project but in the short term I will have to go back to the original idea of trying to figure out the field names of the "true" for a row in a table and place them in an array.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:31
Joined
Feb 19, 2002
Messages
42,981
A properly normalized schema, once you understand it, will actually require little to no VBA to give you the results you need. If you are envisioning complicated VBA, your design is still not correct.
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
As you know from the other thread there was not an answer to the question of how to achieve the form input with the correct schema. The closest answer was to hack together some VBA.

https://access-programmers.co.uk/forums/showthread.php?p=1661631

I had the new design but no way of translating it to the form design for the users to easily enter data. Although I have been told it can/should be done I do not actually have a workable solution. In the absence of another way forward, I come back to the second option.
 

Micron

AWF VIP
Local time
Today, 03:31
Joined
Oct 20, 2018
Messages
3,476
I skimmed over that thread and this one without absorbing it all. Too much at this point. However, this stuck out
The users don't have to select yes or no for everything, they just work through the list and tick the problems they note. This is definitely the way they want to do data entry
If that is really true, I advocate that it is not your problem. They ought to understand that what they're asking for is akin to being unnatural about something. You should not have to design something poorly any more than they should expect you to speak a language that's foreign to you. As far fetched as that seems, one is as bad as the other. If you don't know that their wants require a poor design because of your knowledge level, then you at least have some idea that the path ahead of you is fraught with barriers because you've been told as much.

In closing, I have to wonder why you don't use a continuous subform and have a bound combo whose row source is a table of 'issues'. They pick one from the combo, fill out the rest of the record (if there is anything required) and move on to the next record. Table level restrictions can prevent the same issue from being chosen more than once. 2 issues, 2 records. 5 issues, 5 records. The main form contains the document details, of which the first and last name of the reviewer is not an attribute. If that was meant to be the author, then maybe OK. Otherwise I think the reviewer should be part of the combo selection record, unless perhaps it is not possible for someone else to perform this on their behalf (e.g. the responsible person is on vacation). This subform format will support a db where documents are in one table, issues another, employees in another, and review particulars for any document in yet another.

If that was already suggested in a post or uploaded file, I'm afraid I missed it.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:31
Joined
Jan 20, 2009
Messages
12,849
You should not have to design something poorly any more than they should expect you to speak a language that's foreign to you. As far fetched as that seems, one is as bad as the other. If you don't know that their wants require a poor design because of your knowledge level, then you at least have some idea that the path ahead of you is fraught with barriers because you've been told as much.

Now you are confusing the cart and the horse by letting the data structure define the user interface. That is as bad as letting the user interface define the data structure. A poorly designed interface wastes a lot of time and results in resentful users.

The data entry users have specified what they want for a good reason, efficiency. Claiming that it is a poor design is an unconvincing cover for not knowing how to achieve their requirement while maintaining a normalized data structure under the hood.

I have to wonder why you don't use a continuous subform and have a bound combo whose row source is a table of 'issues'. They pick one from the combo, fill out the rest of the record (if there is anything required) and move on to the next record.

Using a Continuous subform certainly is how this should be done. However selecting from a combo is a horribly slow way to enter data. There is absolutely no reason this cannot be done with a checkbox against a preloaded list of virtual records.

The technique to achieve virtual records is demonstrated in the sample database on post3 of this thread.
 

Micron

AWF VIP
Local time
Today, 03:31
Joined
Oct 20, 2018
Messages
3,476
Now you are confusing the cart and the horse by letting the data structure define the user interface. That is as bad as letting the user interface define the data structure. A poorly designed interface wastes a lot of time and results in resentful users.

The data entry users have specified what they want for a good reason, efficiency. Claiming that it is a poor design is an unconvincing cover for not knowing how to achieve their requirement while maintaining a normalized data structure under the hood.

Using a Continuous subform certainly is how this should be done. However selecting from a combo is a horribly slow way to enter data. There is absolutely no reason this cannot be done with a checkbox against a preloaded list of virtual records.

The technique to achieve virtual records is demonstrated in the sample database on post3 of this thread.
I'm not the one who said the design was poor. You are welcome to your opinion but it doesn't make you right. In fact, I can't even see what the disagreement is about. When it comes to design, I rely on time tested and proven principles, not what's contained in the tables. I suspect you've misinterpreted at least some of my comments.
 

Leyton01

Registered User.
Local time
Today, 17:31
Joined
Jan 10, 2018
Messages
80
For a bit more information I will attach a picture of one of the forms that is currently in use for a document.

Problems 1-6 are generic problems for a form (eg not legible). Then the specifics for this form begins.

"7 The name of the principal is missing in the document"
This is on page 1 - the users need to check this and if it is missing they tick the box.

"8 The Statement of nominated doctor is incomplete e.g. the statement is not signed/dated"
This is on page 2 - the users flip the page and checks this (tick if missing).

"11 The Statement of Understanding in the document incomplete:
• is not signed by the principal or signed and dated by an
‘eligible signer’ or
• has not been signed and dated by an ‘eligible witness’"

This is a multi reason issue with page 3, either of which makes this page incorrect.

There are then some sub headings which only apply if a certain part of the form is completed. The users only have to check these if it meets the heading criteria. All issues are ordered by page count meaning the users work through the list in order as they flick through the document.

As you can see this acts as a checklist for what to actually check and as a quick data entry system. Using comboboxes and drop downs is inefficient for the users and absolutely not what they want. They want to be able to see what needs to be checked so they can work through it and single click the issue.

I have an issue here where I know exactly what the users want, exactly the form input they require but I am struggling to deliver this using the correct schema.
 

Attachments

  • form.png
    form.png
    31.3 KB · Views: 477

Users who are viewing this thread

Top Bottom