A better way to design tables and forms?

[Leyton01]

I am hoping for some advice about changing the set up of a database to follow better design rules and habits. The database I am working on has slowly grown and needs some changes which is a good time to look at the basic structure.

The current requirement is that we receive a number of different documents which are audited for issues by the office staff. These issues are logged and then fed back to the source. There are a selection of specific documents which are logged in and each has a unique set of issues. New issues are sometimes discovered so these need to be added, new document types are sometimes added.

The workflow is -
Users receive document and log details about the document on a form which is stored in the main table. Depending on the document type a relevant subform is displayed with check boxes for each of the issues related to that document type. Users select the problems and then emails etc are generated based on the checked boxes. There is already auditing built into the database and that functions well at the moment, I have not included it in this design to keep it simple.

The current design:
tblDocumentLog - main table stores info about the document itself
DocID, pk
DocType (Doctype1, DocType2, etc)
FirstName
LastName
etc

tblIssueList - master list of all issues used to generate email replies
IssueID, pk (Issue1, Issue2, Issue3)
IssueDescription
IssueCriticalLevel
IssueEmailReplyText

tblDoctype1 - one of these for each of the document Types
ID, pk
DocID, fk
Issue1 (T/F)
Issue2
Issue3

tblDoctype2 - second doc type
ID, pk
DocID, fk
Issue4
Issue5
Issue6


When feedback is needed the users press a button on the subform and I use VBA to loop through the checked boxes on the subform and generate the string which goes in the email.

This is not very efficient and I cringe each time I edit the database knowing this is poor design. Each new document requires a new table, each new issue requires a database update for the end users (split database).

I want to redesign the database to better manage the data.

What I envisage is:

tblDocumentLog - main table stores info about the document itself
DocID, pk
DocType (Doctype1, DocType2
FirstName
LastName
etc

tblIssueList - master list of all issues used to generate email replies
IssueID, pk (Issue1, Issue2, Issue3)
IssueDescription
IssueCriticalLevel
IssueEmailReply
**RelatestoDocType - new field which specifies which doc type this issue relates to

tblDocumentIssues - stores all identified issues for documents in this table
DocIssueID, pk
DocID, fk
IssueID, fk

I would then use lookups to grab all the Issues relevant to that document and generate the email string.

Where I am struggling is creating the subform for the users to select the relevant issues. It is easy in the current design as I just add all the fields for the specific document type to its individual subform and when checked it adds a TRUE to that column. I am unsure how to achieve this with the new design. How do I make a subform which displays a list of issues for that document type for the user to check? I am still happy to have individual subforms for each document type but I am not sure how to create the checkboxes for something which doesn't "exist".

My questions:
Is this change a better way to design the database or am I doing it unnecessarily?
How do I create subforms with True/False controls which are linked to fields which dont yet exist?
Am I doing it completely wrong?

 

[vba_php]

Am I doing it completely wrong?

that's a loaded question sir. to say that you're doing it "wrong" is not really accurate because more people have "non normalized" setups than those who have "normalized" setups. and even "non normalized" setups are successsful most of the time even though the apps that drive them sometimes end up completely out of whack after they've been worked on for a while.

as far as your question about putting booleans on a subform that are not linked a main form field, I don't see any reason why you *can't* do that. although I despise using subforms, a lot of people like them and they are widely used by access developers. not to mention the fact that they are the most complex and misunderstood objects in microsoft access anyway.

that's about all I've got for you regarding this. your post is quite long and the description of the issue is one that really warrants requesting a paycheck, even though it would be small.

you might find that the retired programmers that are around here will add to what I've said during the next day or two.

 

[Leyton01]

I suppose the question should be "is there a better way of achieving this?", with a clarification that better refers to a more elegant solution and aligning with the normalized setups.

I did want to give extensive information, I can see frustrations when vague questions are asked which depend on many variables.

I think the summary of the question is:
If I move from having separate tables for each individual document, with fields representing each issue, into having one master table which stores only the specific issue for that document how do I generate a form for users to easily select from a list of possible issues?

 

[vba_php]

If I move from having separate tables for each individual document, with fields representing each issue, into having one master table which stores only the specific issue for that document how do I generate a form for users to easily select from a list of possible issues?

I would say experimentation. are you open to that? as I said before, there are endless ways to do all tasks in this application. so no one way is better than the other because at the end of the day, everybody does things there own way anyway.

are you looking for a sample setup based on your original post? I suppose I could post up a sample relationship structure that would get you started.

 

[Leyton01]

I have experimented already and created a sample database which I had in another thread:
https://access-programmers.co.uk/forums/showpost.php?p=1656049&postcount=8

The setup and relationships are not an issue it is creating the form to be able to have simple check boxes for users to select, based on the specific document and issue list.

(The other thread started with a question that was not on the right track and thDBguy suggested I started a more relevant thread)

Direct link to sample database:
https://access-programmers.co.uk/forums/attachment.php?attachmentid=77852&d=1575965144

 

[vba_php]

you only need 2 tables involved in the relationship setup here my friend:

1) tblDocuments (1 side)
2) tblDocumentIssues (many side)

the other 2 tables should be LOOKUP tables.

tblDocumentTypes should be a combo on a form and its bound field should be one in the table tblDocuments. all of the fields in tblIssuesMatrix should be their own fields in tblDocumentIssues (other than perhaps "AppliestoDocType").

when you do things this way, what you'll find is that all of the "structure" that would otherwise be complex to understand becomes a moot point because its being offset by the lookup work that is done by combos on the form. in terms of having checkboxes for the users to specify, with this simple 2 table setup, you can simply link ur 1st table to ur 2nd table on the common fields and tell the users to check the boxes on the subform for which they are interested. then whatever you're doing after that should be a piece of cake regardless of where you take things. the checkboxes in this regard SHOULD appear as "tag along" fields that are generated when the records from the 2nd table are displayed.

make sense?

 

[plog]

I'm glad you've seen the headache that is an unnormalized database and are moving toward a normalized one. I've downloaded your database and think you are on the right track.

The only issue I see is that you've created a loop in your Relationship tool. There should only be one way to trace a path from one table to antoher in your Relationsip Tool, you have 2 ways (clockwise and counterclockwise). I believe the solution to this is to not connect blDocumentTypes to both tblDocuments and tblIssuesMatrix. Instead you probably need a new instance of tblDocumentTypes--one for each of those tables. Not a new table, just bring in tblDocumentTypes into your Relationship tool twice and connect one to tblDocuments and one to tblIssuesMatrix.

After that, feed some data into your database to test it. Whatever reports you will need to extract from this system, try and create them. If you can, then your tables are correct, if not then its time to troubleshoot.

When you are able to create the Reports, then you move on to forms. No point making amazingly brilliantly beautiful forms if you're just throwing data down a hole from which you can't properly retrieve it.

 

[Leyton01]

The only issue I see is that you've created a loop in your Relationship tool. There should only be one way to trace a path from one table to antoher in your Relationsip Tool, you have 2 ways (clockwise and counterclockwise). I believe the solution to this is to not connect blDocumentTypes to both tblDocuments and tblIssuesMatrix. Instead you probably need a new instance of tblDocumentTypes--one for each of those tables. Not a new table, just bring in tblDocumentTypes into your Relationship tool twice and connect one to tblDocuments and one to tblIssuesMatrix.

Thanks for this - I have tried adding the tblDocumentTypes twice in the relationship view (it automatically creates tbDocumentTypes_1) and unlinked/relinked. But when I close and open the database it automatically recreates the original link and orphans tblDocumentTypes_1. Is this what you meant or am I reading that incorrectly?

After that, feed some data into your database to test it. Whatever reports you will need to extract from this system, try and create them. If you can, then your tables are correct, if not then its time to troubleshoot.

When you are able to create the Reports, then you move on to forms. No point making amazingly brilliantly beautiful forms if you're just throwing data down a hole from which you can't properly retrieve it.

I have done this in anticipation of moving forward and you can see the 2 queries which I created in that sample database. This gave me the information I usually need, which is a count of the issue types. Previously I would just access each document type table and count the 'true' fields for each issue. Now I have to do a totals query and it can run all the documents and issues at once from the DocumentIssues table.

I have reattached the database with the table names reflecting those listed in the first post.

 

[plog]

In the relationships tool, just include tblDocumentTypes once and attach it only to tblDocumentLog. In Query1 you can do what I suggested with 2 instances of tblDocumentTypes and it will hold, although you probably only need 1 instance of it there.

 

[Leyton01]

In the relationships tool, just include tblDocumentTypes once and attach it only to tblDocumentLog. In Query1 you can do what I suggested with 2 instances of tblDocumentTypes and it will hold, although you probably only need 1 instance of it there.

OK I will update.

tblDocumentTypes should be a combo on a form and its bound field should be one in the table tblDocuments. all of the fields in tblIssuesMatrix should be their own fields in tblDocumentIssues (other than perhaps "AppliestoDocType").

I'm not sure if I understand this - when you say that all of the fields in tblIssuesMatrix should be their own fields in tblDocumentIssues isn't this just the current set up I have which seems inefficient?

If there are 100 possible problems doesn't that mean there is at least 100 fields in the tblDocumentIssues and each time a new problem is discovered I have to add to the field list?

 

[Leyton01]

the original question probably still stands - how do I make a form, with check boxes to fit into the above structure?

 

[Leyton01]

So your 100 issues would NEVER, EVER be columns in a table. They would be rows in a child table. You would have a table to define the issues. That table would contain just 100 rows and would include the text and whatever else uniquely describes a particular issue but you need a separate tblDocumentIssues where you can link one or more issues to a document. So, tblDocumentIssues will have one row for each of the 100 potential issues so it could contain 0 (if no issues) to 100 (if every issue is found).

Yes this is the behaviour I am trying to correct. And is described above as the old design but looking to change with the new design.

Looking at your relationship diagram,DocumentTypes should not be related directly to IssuesList - I'm pretty sure someone already suggested that but they were too wishy-washy. You can find the document type for an issue by going back to the DocumentLog through the Document issues.

This link is only to the "IssueAppliesTo" field - in other words each problem only applies to one particular document type and this is stored for ease of review. If I want to print a list of all the issues, their critical status and which document it actually applies to I can use this field. There may be "identical" issues (eg "Section A is missing") but refers to different document types. This relationship was only set up as a lookup table but is poorly demonstrated in the example db.

PS - I don't recommend the use of ID as the PK name for all tables. The PK name should probably be made up of some part of the tablename followed by ID. So DocumentID, DocumentIssuesID, and IssueID would be reasonable names fo those three tables.

This is not how the actual database is set up - please ignore the PKs, FKs and ID names, I cobbled together the example to demonstrate the way the tables interact rather than the structure. The current relationships in the database works well with the DocID setup I have. My primary question is about what to do with forms once I have set up this new structure.

Issues would be listed on a subform where you would use a combobox to add each new issue as a new row. You would NOT create all the issues at once since any given document will be unlikely to have more than a couple of issues.

This come closest to the question I have. The current (poor) set up allows me to have a subforms with:

fsubDoctype1:
[] Issue 1 desc [] Issue 3 desc
[] Issue 2 desc [] Issue 4 desc
… [] Issue 15 desc

fsubDocType2:
[] Issue 16 desc [] Issue 18 desc
[] Issue 17 desc [] Issue 19 desc
…. [] Issue 35 desc
etc.

The appropriate subform is displayed on selecting the document type at the start.

The users work through the documents and the issues are ordered according to when they are possibly encountered (page 1,2,3). The users tab through or mouse click the check boxes as they spot an issue. This is a very quick and efficient way for them to enter data. Selecting from 20+ issues in a combo box is difficult and often the descriptions are quite detailed (long).

How would I build these subforms with the new database structure?

 

[Leyton01]

Thanks Pat.

I will take on board all the field/naming conventions and include them in the final. It is always good to improve the basics.

Unfortunately the drop down form list would not suit the current work flow. The users use the subform as a checklist/'cheat sheet' for the issues they need to check. It displays all the possible problems and they work through them ticking the ones they find. Having them displayed only as a drop down that they have to scroll through would not work. Some of the issue descriptions are also a couple of sentences long as they are made up of many parts (Section A, B and C missing has a higher critical rating than just Sections A and C missing)

I am starting to think that this will not work with the improved database structure. The only way I can see this being able to function is to have an AfterUpdate event for each check box write or remove the entry from the table. This is both tedious to program (more than 150 problems so far) and would probably require looping through the list every time the form is reopened to correctly set the checkbox states.

This might be a case of form (literally) over function.

 

[Leyton01]

On average it is about 15-30 possible problems per document. 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. With the issues ordered by page number it is quick, thorough and does not require a lot of clicks.

Generally the problems are known for a new document type when it is incorporated (which takes work and maintenance anyway). On the odd occasion a new problem is found and this does require a database update.

In the original set up, mostly due to poor design and luck, rather than planning, each document had its own issues table. This would mean that it does not reach the 256 limit.

The problems are being renamed from a simple number to a convention (XXX.YYY.ZZZ) - X being the doc type, Y being the problem category and Z the sub issue. This is mostly for consistent auditing and reporting, the users mostly see the description of the problem ([ ] "Page 6 - signature missing"). It was part of this renumbering work that I was looking over the database and thinking "there must be a better way", hence the questions.

Again I think it is this want from the users to have a friendly entry format which prevents best practice set up in the back end.

 

[plog]

Again I think it is this want from the users to have a friendly entry format which prevents best practice set up in the back end.

No, nothing but a poor/lazy/inexperienced developer prevents best practice on the back end.

You can achieve the user input you want with a properly structured database. That might mean you run an APPEND query to populate tables with all known possible issues so the front end runs correctly, or it means some VBA to hack together functionality on the user form to get the effect you want. But you never intentionally put cracks in your databases' foundations so you can build a form.

 

[Leyton01]

No, nothing but a poor/lazy/inexperienced developer prevents best practice on the back end.

You can achieve the user input you want with a properly structured database. That might mean you run an APPEND query to populate tables with all known possible issues so the front end runs correctly, or it means some VBA to hack together functionality on the user form to get the effect you want. But you never intentionally put cracks in your databases' foundations so you can build a form.

I am inexperienced, but smart enough to seek guidance from those with more experience.

By your very own words 'hacking together functionality with VBA' does not seem like the most efficient way to go with the forms and display. My point is that the correct DB structure actually makes the forms on the front end difficult to create and possibly more resource intensive when considering VBA vs inbuilt functionality.

With more experience comes the realisation that I can't have my cake and eat it too with Access.

 

[Galaxiom]

No, nothing but a poor/lazy/inexperienced developer prevents best practice on the back end.

You can achieve the user input you want with a properly structured database. That might mean you run an APPEND query to populate tables with all known possible issues so the front end runs correctly,

Inserting records to pre-populate tables is frequently done but it is an unimaginative (poor/lazy/inexperienced?) developer's kludge.

See how it should be done in the sample database of post 3 on this thread.

But you never intentionally put cracks in your databases' foundations so you can build a form.

Definitely.

 

[Leyton01]

I am sorry this is cross posting but these original posts did ask separate questions and it was suggested I start a new thread when they diverged.

Here is the form explanation from the other thread:
-----

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.

 

[Leyton01]

See how it should be done in the sample database of post 3 on this thread.

I will check out the database but virtual records, if it is what I think it is, sounds promising.