Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-09-2017, 04:47 PM   #1
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Rad Second Access Project to CRUSH!

All,

Bottom Line Up Front: I hit a home run with a complex Access Database (after 30 days of failures and about 1000 working copies; my first Access Project) and I guess made my self so "valuable" I got tasked to do it again; only BIGGER!

So heres where I am struggling to put pieces together to make it work in Access 2007.

Basics of the structure I currently have:

Table 1: fields for "employees" names, their assigned "teams", and each "employees" current qualifications.

Table 2,3,4, and 5: These are Courses so we'll call them "Course_1","Course_2", "Course_3",...ect. Each Course table has 20 different fields which grades different course modules the "teams" complete through training.


So how to tie it all together.

I have one end user that wants to see the data with the "teams" current qualifications per "employee", as well as all information in Table 2,3,4,5 associated to that team.

I have another end user that solely wants a simplistic way to monitor the progress of each "team" without having to click through the whole database.

The end users are dumber than me, probably why they were wowed by my first product. I am having the hardest time understanding how to employ keys, relationships, and whether is is easier to make each of these "teams" their own tab. Also how to make this pretty and user friendly.

Ready to take any advice, criticism, and banter; either way, I'll eventually crush this product.

Thanks again guys!

Cheers!

tgg1 is offline   Reply With Quote
Old 08-09-2017, 05:20 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,719
Thanks: 36
Thanked 1,711 Times in 1,662 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Rad Second Access Project to CRUSH!

Here is a link with info that may help.
I suggest you work through 1 or 2 of the tutorials listed with For practical experience / learning
You will learn a process to go from a description to a data model you can test.
Good luck.


Update: Without knowing the details of your needs, I have attached a very rough draft model (as a jpg) that may be of some value.
Attached Images
File Type: jpg TeamEmployeeQualification_Draft.jpg (32.5 KB, 56 views)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 08-09-2017 at 05:36 PM.
jdraw is online now   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
tgg1 (08-09-2017)
Old 08-09-2017, 06:07 PM   #3
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Re: Rad Second Access Project to CRUSH!

Thank you so much for the link and draft. I'm spending time now sorting through where I may have broken some business rules on this one and simplifying the entries to validate or start from scratch. Much appreciated! I'll be back shortly!

tgg1 is offline   Reply With Quote
Old 08-09-2017, 09:35 PM   #4
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,714
Thanks: 36
Thanked 848 Times in 761 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: Rad Second Access Project to CRUSH!

Quote:
I may have broken some business rules
For the initial effort, it is OK to omit or bend a rule. You don't have to get everything working all at once. However, remember that in a finished product, when the app and the business don't agree exactly, the app is wrong.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 08-10-2017, 05:37 AM   #5
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Re: Rad Second Access Project to CRUSH!

Ok, So sorting through all the links you provided me I decided start from scratch and put my thoughts on paper, similar to your draft. Attached are my rough versions of how I'm trying to get the database to look, and how I think they are best suited to relate.

What problems do you see, I still failed all day trying to get this type of basic shell into Access. hah, better luck tomorrow I guess.
Attached Images
File Type: png team training model.png (45.1 KB, 51 views)
File Type: png company structure.png (62.7 KB, 49 views)
File Type: jpg big picture.jpg (77.1 KB, 45 views)

Last edited by tgg1; 08-10-2017 at 03:53 PM.
tgg1 is offline   Reply With Quote
Old 08-11-2017, 03:41 AM   #6
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,302
Thanks: 94
Thanked 107 Times in 102 Posts
Lightwave will become famous soon enough
Re: Rad Second Access Project to CRUSH!

You are just struggling with something called many to many relationships. You are essentially dealing with a booking system. The basis of a booking system is usually.

Table of Courses

Table of People

Table of Bookings

1 person can take more than one course AND 1 course will take more than one person.

For example
You have 2 courses and 3 people
Course A
Course B

and 3 People
John
Paul
Ringo

So how do you link all of these together. Lets Say John wants to take Course A and Course B while Paul and Ringo just want to take Course B

You have a separate table called BOOKINGS

This will have the following fields
Attendee
Course

and to record ANY booking you simply add records to the booking table
John - Course A
John - Course B
Paul - Course B
Ringo - Course B

Each line represents a new record in the table

See how John is in twice - this is because he is taking two courses. If Paul had now wants to take course A as well you would enter

Paul - Course A


So you want to know who is on course B. Simple you query the BOOKING table on the Course Field for all items = to Course B and you will get

John / Paul and Ringo.

Want to know who is on course A simple again do a query on the booking table on the course field = to Course A

You should get John and Paul (after adding Paul)

The booking table is sometimes referred to as a Junction Table and it holds the many to many relationship. The absolutely superb thing about this is if you use foreign keys you can update the details of the individuals in the Course and person tables and they will essentially appear in the inidividual bookings.

Beginners have no problem understanding One to many and one to one relationships but they struggle with many to many as it just isn't as intuitive. You absolutely must understand the concept though if you want to take your design further.

So with the qualification I would add two extra fields into the bookings table one called passed qualification and one called qualification obtained. If you then need to know what qualifications John has you query the Junction bookings table on John and Passed course to get what qualifications he has. The persons table is kind of finite - limited to the number of people and the courses offered are kind of finite - limited to the number delivered. Given time the bookings could be infinite though - a finite number of people could just simply take a finite number of courses an infinite number of times or until they pass. This is the structure travel companies choose as they want to be attracting people back an infinite amount of times!

Last edited by Lightwave; 08-11-2017 at 03:59 AM.
Lightwave is offline   Reply With Quote
The Following User Says Thank You to Lightwave For This Useful Post:
tgg1 (08-11-2017)
Old 08-11-2017, 10:19 AM   #7
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Re: Rad Second Access Project to CRUSH!

Lightwave,

Thanks for the reply and guidance. I appreciate how you broke it down in such a simplistic manner.

I am having a bit more success today (see attached screenshots of my table structures).

As of now, don't be fooled by the data entries, I hand typed all of it in so you all can see where I may be screwing up. Still haven't gotten to the point of figuring out the relationships and it auto populating as of yet, but that will come in due time.

On the Company/Division/Team & Employees section, I am initially leaning towards compiling the employees with their qualifications in one table (associating them to their Division and Team). Not sure if this is the right answer or if it will work.

The Team Training/Courses/Modules grading or assessment criteria are solely shown on a Team level and not per employees. I need to have each module graded on an above, at, or below average criteria related to the Division and Team as they progress through their courses/modules. Courses/Modules may or may not be applicable to each Team, similar to how not all employees will hold the same qualifications.

Standing by for where you guys think my elementary knowledge of Access will cause me problems.
Attached Images
File Type: jpg ACCESS CO_DIV_TEAM QUAL.jpg (87.3 KB, 44 views)
File Type: jpg TEAM TRAINING_COURSE_MODULE_ASSESSMENT.jpg (94.0 KB, 38 views)

tgg1 is offline   Reply With Quote
Old 08-11-2017, 10:52 AM   #8
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,107
Thanks: 10
Thanked 1,949 Times in 1,910 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Rad Second Access Project to CRUSH!

The biggest mistake (and the one made very often) is you do not store redundant data. [TEAM TRAINING] is fine for now (I have a nit to pick, but let's not miss the big picture). [COURSES] is not. You do not need to store both [training_id] and [course] in this table. If you know [training_id] you have access to [course]. [course] needs to go from [COURSES].

You made the same mistake in [COMPANY STRUCTURE]. With [module_id] in this table you do not need all the other fields it relates to (course, module, training_id).
plog is online now   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
tgg1 (08-11-2017)
Old 08-11-2017, 11:13 AM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,719
Thanks: 36
Thanked 1,711 Times in 1,662 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Rad Second Access Project to CRUSH!

tgg1,

Do the following statements represent your situation??? Adjust as necessary.

The Company is divided into 1 or more Divisions
A Team is Composed of Employees from a Division
An Employee works in 1 Division
A Division may have 1 or more Teams
An Employee may have 0,1 or Many Qualifications
A Team takes Training
Training is Composed of Courses
A Course may have 1 or many Modules
TeamTraining is assessed based on Course and Module
Assessments are rated against an identified scheme


I drafted a model based on these, but I may have misunderstood or misinterpreted.
Feel free to adjust or ignore.

Good luck.
Attached Images
File Type: jpg DivisionTeamsTrainingAssessment_Draft0.jpg (77.0 KB, 41 views)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 08-11-2017 at 11:56 AM.
jdraw is online now   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
tgg1 (08-11-2017)
Old 08-11-2017, 03:07 PM   #10
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Re: Rad Second Access Project to CRUSH!

jdraw,

I have literally tried to draw this out a million times and was so close each time. That is definitely closer to what I need that anything i've created!

I think that model will suite what I'm looking for, and it is extremely clear. Thank you so much. I'll start crunching it into Access tomorrow!

Thanks again, you are awesome!
tgg1 is offline   Reply With Quote
Old 08-11-2017, 03:14 PM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,719
Thanks: 36
Thanked 1,711 Times in 1,662 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Rad Second Access Project to CRUSH!

No! Don't jump into Access yet. Make sure you understand the model --then test it.

Take some sample data and a few test scenarios based on your business.
See if the test data and test scenarios can be handled by the model.
If you have any issues --resolve them -- is it the data, is it the model, is the test case incorrect???
Work on it to you resolve it. Then test again. You'll end up with a blue print for your database design.

See my stump the model post.

Good luck,
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 08-11-2017, 03:23 PM   #12
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Re: Rad Second Access Project to CRUSH!

Jdraw,

Will do. I'd rather understand and get this right on the front end before I get errors and failures at the end.
tgg1 is offline   Reply With Quote
Old 08-14-2017, 07:49 AM   #13
tgg1
Newly Registered User
 
Join Date: Aug 2017
Posts: 9
Thanks: 4
Thanked 0 Times in 0 Posts
tgg1 is on a distinguished road
Re: Rad Second Access Project to CRUSH!

Alright,So combing through the model, which has been extremely helpful, I understand how each of these fields relate and what the overall structure and intent is.I see my past errors on combining duplicate information in past attempts.Some questions still lingering:1. When relating the primary key to the foreign key, how can the information be viewed as the data vice the ID, how is it auto updated, and how do you simplistically enter data into one central location to update all fields (i.e. employee, team, division, quals in one location that updates all remaining fields/tables)2. Are there negatives to restricting inputs by utilizing dropdowns? (i.e. using dropdowns for divisions, which autopopulate which team they are on based on relationships)I am working the side of employee/team/division/qualification, prior to starting the course/module/assessment section.Any pointers to tutorials/help is appreciated. I feel like I am endlessly combing through forums and instructions only to fall flat again putting this together on a simple scale.
tgg1 is offline   Reply With Quote
Old 08-15-2017, 06:57 AM   #14
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,714
Thanks: 36
Thanked 848 Times in 761 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: Rad Second Access Project to CRUSH!

I'll take a shot at #2. Restricting inputs by using dropdowns (based on a query of valid vaules for same) is never a full negative. However, there can come a time when you might wish to say, "... and if the choice is not in the list, we'll have to add it somehow." If you ever want that to be possible, then you complicate matters slightly. The solution is in something called the "Not In List" event, but I prefer to address why dropdowns are often your friends.

I have seen too many cases where, left on their own, people will incorrectly spell something so badly that it cannot be identified. By giving people choices, you give them less chance to make a mistake. Have you ever heard of Murphy's Law? "What can go wrong WILL go wrong." But have you heard the 2nd half of Murphy's Law? "...Therefore, build it in such a way that it CANNOT go wrong." The dropdown is a way of building things to minimize the chance that people will make the wrong choice when possible.

Now, here is something else that I see in your question:
Quote:
using dropdowns for divisions, which autopopulate which team they are on based on relationships
Where it is possible to KNOW an answer by using a query or exploiting an extant relationship, don't ask. TELL. Ask only when you don't really know. Now, if you can limit the range of choices before asking because the relationship allows that, it is still OK to give RELEVANT choices and ask. Just don't ask where there is already no alternative.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 08-17-2017, 05:46 AM   #15
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,302
Thanks: 94
Thanked 107 Times in 102 Posts
Lightwave will become famous soon enough
Re: Rad Second Access Project to CRUSH!

OK - I'll have a go at answering 1.

Although on first impressions it feels like you need to have multiple copies of the same information so that you can view it in different ways and it feels that if the information is not in one location you might not be able to edit it that is not the case. If information is relatable, algebra theorizes that you can simply display the same source information in pretty much infinite ways.

This is what queries are. Even sorting in alphabetical order is a query. You don't create a new set of information every time you want a spreadsheet organised by a different column, simply resort the base information. In itself a table can be thought of a series of columns in which the information is itself related to each other column in a 1-1 relationship. So there is no difference between that and two tables in a 1-1 relationship. Users just get used to the idea that there is somehow a difference if information is held in two different tables.

If you can build a form from a query it will give the user the illusion that the information is in a single place but often it will be in multiple tables. IF there is a one to one relationship a query can be made up which has a single form. If there is any kind of master child relationship between tables - eg one individual can have infinite qualifications then you would have a master details form which is implemented as two forms - one a master form and two a linked list form that filters by the parent.

In most (but not all instances) the information displayed after queries can be edited (there are some specific types of queries which will not let you update information generally because the query in some way aggregates the information from a form but try not to worry about that at the moment).

Have a go at creating a form from a query which itself is based on a couple of tables and you should start to see what a powerful concept this is. Start with just a query with just 2 tables.

Using this principle out of only 3 tables you can often create hundreds of different "windows" into the information optimized for the usage cases of differing users. Think of it like cameras on a football pitch. You don't have a different football game for every camera - you have one football game and view it from multiple viewpoints. In this way you can't have any inconsistency in the underlying information (the game) and everything is synced - imagine if you had to have 2 games and in the second game you told the players to copy the actions of the previous players. It would quickly divert. One set of information and multiple viewpoints.

As for handling all of these different views and the update when multiple viewpoints edit the information - That's the database engine's job. Any good database will handle this for you a lot of very intelligent people have sorted all of that out. People can be viewing the same information on a mobile on a table on anything some sorted alphabtically others by weird dates may all be looking at the same information contained in a single cell - the database handles all of that and has strict rules to prevent corruption of the information. On the rare occasion a database is unable to allow a users to update the information - it should gracefully disallow the users to make the update.

So in summary
  • Group fields related to each other in a 1-1 relationship in the same table. If you feel the need to have multiple fields with the same information eg Qualification 1 / Qualification 2 / Qualification 3 - you need to create a new table and relate it back to its relations appropriately be they parents or children.
  • If a field is in one table it should NOT reappear in another - eg date of birth
  • Create as many queries as you need that aggregate information in the ways you wish to view it.
  • Build forms based on these queries
    Postscript -
    This point altered subsequently to - Forms can be based straight from tables or from queries - addition / deletion and edit forms may benefit from only being based on single tables viewing of information only forms,your only choice may be to have them based from a query. In certain circumstances you could base a form on either. With the passage of time you will start to see the benefits of each choice.


Last edited by Lightwave; 08-17-2017 at 01:41 PM.
Lightwave is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Project Access Leeroybrown Tables 25 06-26-2011 02:32 AM
Access 2003: Create Project and add Records to Project spudracer Queries 7 01-20-2011 01:17 PM
Access 2003 Project (ADP) vs Access 2007 Project ions General 32 03-16-2010 12:00 PM
Converting Access to Access Project Query problem mjliscio Queries 7 09-19-2007 08:38 AM
Deploying Access 2003 project in different Access versions Ayat General 2 12-29-2005 07:21 AM




All times are GMT -8. The time now is 07:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World