Rad Second Access Project to CRUSH! (1 Viewer)

tgg1

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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!
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:49
Joined
Jan 23, 2006
Messages
15,361
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.
 

Attachments

  • TeamEmployeeQualification_Draft.jpg
    TeamEmployeeQualification_Draft.jpg
    32.5 KB · Views: 164
Last edited:

tgg1

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:49
Joined
Feb 28, 2001
Messages
26,996
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.
 

tgg1

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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.
 

Attachments

  • team training model.png
    team training model.png
    45.1 KB · Views: 166
  • company structure.png
    company structure.png
    62.7 KB · Views: 155
  • big picture.jpg
    big picture.jpg
    77.1 KB · Views: 162
Last edited:

Lightwave

Ad astra
Local time
Today, 12:49
Joined
Sep 27, 2004
Messages
1,521
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:

tgg1

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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.
 

Attachments

  • ACCESS CO_DIV_TEAM QUAL.jpg
    ACCESS CO_DIV_TEAM QUAL.jpg
    87.3 KB · Views: 174
  • TEAM TRAINING_COURSE_MODULE_ASSESSMENT.jpg
    TEAM TRAINING_COURSE_MODULE_ASSESSMENT.jpg
    94 KB · Views: 141

plog

Banishment Pending
Local time
Today, 07:49
Joined
May 11, 2011
Messages
11,611
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).
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:49
Joined
Jan 23, 2006
Messages
15,361
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.
 

Attachments

  • DivisionTeamsTrainingAssessment_Draft0.jpg
    DivisionTeamsTrainingAssessment_Draft0.jpg
    77 KB · Views: 170
Last edited:

tgg1

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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!
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:49
Joined
Jan 23, 2006
Messages
15,361
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,
 

tgg1

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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

New member
Local time
Today, 08:49
Joined
Aug 10, 2017
Messages
9
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:49
Joined
Feb 28, 2001
Messages
26,996
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:
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.
 

Lightwave

Ad astra
Local time
Today, 12:49
Joined
Sep 27, 2004
Messages
1,521
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:

plog

Banishment Pending
Local time
Today, 07:49
Joined
May 11, 2011
Messages
11,611
Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data.

To represent a 1-many relationship in forms you would have a main form based on the 1 table and a subform based on the many table.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:49
Joined
Jan 23, 2006
Messages
15,361
tgg1,

You have been given advice/suggestions to your questions-- what is status?

@lightwave
Nice analogy for the form as a window into/onto the database!!
 

Lightwave

Ad astra
Local time
Today, 12:49
Joined
Sep 27, 2004
Messages
1,521
Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data.

OK I should qualify that - there is a body of thought that says that forms should be based on queries but certainly delete and add are better done by queries based on tables.

The larger your datasets the increasingly likely your forms may have to be based on queries.
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:49
Joined
Oct 17, 2012
Messages
3,276
And sometimes you'll need to use queries on edit forms anyway. In my case, I deal with tables with millions (and in one case, billions) of records. Because of that, I generally use a query based on one single table to return a single record based on selected criteria. However, because the query is NOT one of the special non-updatable types and is based on a single table, the data can be edited.

Those types of games aren't as necessary with more reasonable table sizes, however.
 

Lightwave

Ad astra
Local time
Today, 12:49
Joined
Sep 27, 2004
Messages
1,521
Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data.

Granted I was thinking about use cases such as bookings where a teacher may want to see only those students in a form that are in his classes and do some simple edits to their details.
 

Users who are viewing this thread

Top Bottom