A better way to design tables and forms? (1 Viewer)

Leyton01

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

Forum Troll
Local time
Today, 00:02
Joined
Oct 6, 2019
Messages
2,884
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

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

Forum Troll
Local time
Today, 00:02
Joined
Oct 6, 2019
Messages
2,884
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

Registered User.
Local time
Today, 15:02
Joined
Jan 10, 2018
Messages
80
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
 
Last edited:

vba_php

Forum Troll
Local time
Today, 00:02
Joined
Oct 6, 2019
Messages
2,884
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

Banishment Pending
Local time
Today, 00:02
Joined
May 11, 2011
Messages
11,613
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

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

Attachments

  • Database21.accdb
    524 KB · Views: 278

plog

Banishment Pending
Local time
Today, 00:02
Joined
May 11, 2011
Messages
11,613
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

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

Registered User.
Local time
Today, 15:02
Joined
Jan 10, 2018
Messages
80
the original question probably still stands - how do I make a form, with check boxes to fit into the above structure?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Feb 19, 2002
Messages
42,981
Think of it this way.
If you have ONE of some attribute, it can go in the main table.
If you have MANY of some attribute, you need a sub table.

A simple example is tblPerson. A person has only a single name and a single birth date but could have many children and many addresses. So you need a tblChildren to hold the data about each child and tblAddresses to hold a row for each Address.

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

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.

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.

I also see that you have ID which is probably an autonumber and DocID. I would recommend using the autonumber as the PK and creating a unique index on the DocID to prevent duplicates. You probably have no control over DocID and so it is not a good choice as a PK in your table. Change tblDocumentIssues to relate to the autonumber instead of the DocID. You will thank me for that advice if DocID ever has to change. The change doesn't impact your application at all. The users will see DocID and they still search by it. DocumentID (the autonumber) is simply a hidden PK that the user doesn't see but which your app uses in relationships.

If you decide to keep DocID as the PK, then you should remove the autonumber. Autonumbers exist only for the purpose of providing a unique identifier. using the DocID as the PK is not wrong. It is simply not my choice. A slightly different example is SSN (for those in the US) some older systems use SSN as their PK. It has been illegal since at least the 70's when computer systems came into being in virtually all companies to use SSN as a PK since it violates privacy rules. Not to mention the fact that you can't employee 25 illegal aliens all with Jams Jones' SSN because duplicates are not allowed as the PK. Oh! maybe allowing SSN as a PK would have solved that problem years ago.
 
Last edited:

Leyton01

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

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Feb 19, 2002
Messages
42,981
OK, here's a rudimentary example. The issue combo restricts the list to issues that apply to this docType. It could be further reduced to show ONLY issues not already assigned to this doc which would make the list easier to pick from since it decreases the options as you add more issues.

It is also possible to change how the issue is shown, I used a concatenated value of the number + the text. It could be text + number or it could be sorted by priority if a couple of the issues occur more frequently than others so you would prefer to items # 45 and 3 pop to the top of the list.

PS - I also fixed the naming conventions and the fields that are used in the relationships.
 

Attachments

  • Database21PAT.accdb
    576 KB · Views: 191

Leyton01

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

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Feb 19, 2002
Messages
42,981
If the user actually has to say yes or no to 100 issues, that is a different problem.

This is never going to work the way a spreadsheet works. Every time you add a new issue, you will have to change the table, the form, the queries, and your code, potentially many queries and code procedures. This is simply not viable. And even if you were willing to take on the maintenance, you will ultimately run into the hard limit of the number of columns in a table or query which is 256.

If you need y/n for EVERY possible issue, then there is a way to do it with the list. Just be sure that is what the user wants. I'm also pretty sure that no user has memorized 100 issues named 001-001-001 so presenting the issues in a way where the definition is visible isn't going to work on a form in any event.

I would talk this over with the user community. Actually spend a day in their shoes doing their job. It just might change your opinion on how to approach this problem.
 

Leyton01

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

Banishment Pending
Local time
Today, 00:02
Joined
May 11, 2011
Messages
11,613
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

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

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Feb 19, 2002
Messages
42,981
You are fixated on the "flat" data entry method because that is the way it has always been done. Try building the form with a list type subform for the errors as I and the others have suggested. Post it and we'll review it before you bring it to the users.

Rather than using a compound description for the errors which produces a very long list, think about using three nested lists. the first list filters the second list, the second list filters the third list. This rapidly reduces the items in the list. This is called cascading combos.
 

Users who are viewing this thread

Top Bottom