UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access 2010 Web App To Track User Training, Access 2010 Web    
 
   
lmossolle
post Nov 21 2017, 08:40 AM
Post#1



Posts: 5
Joined: 20-November 17



Greetings all! I have a Access 2010 Web App I am try to develop. I have A Class Table and a user Table. On the Class Table, There is a Required Group that I would like to use to be able to assign classes to the users. On the User Table, there is a Current Role. If the selection of the courses are the same as the users current role, the users would be able to add a completed date and attach a certificate if the certificate is deemed necessary. Can someone please assist if possible?

Thanks,

Lee Mossolle
Attached File(s)
Attached File  MyTraining_Test.zip ( 397.66K )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 08:50 AM
Post#2


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

There's good news and bad news.

The bad news first: Microsoft has deprecated Access Web Databases. They moved beyond that technology with the 2013 introduction of the Access Web App. Both are (or more accurately, were) supported on Office 365 and--if your organization does have an on-premises SharePoint Server--on your on-premises SharePoint site. As of this point, however, Microsoft plans to drop both from Office 365 in April, 2018. You need to be aware of that, and plan accordingly. Of course, if you DO have an on-premises SharePoint Server, you can continue to use them. Or you can seek out an alternative hosting company for your Access Web Databases/Apps.

So, the good news. You can handle this sort of requirement by adding a third table, which we usually call a "junction table". In this table, you have two Foreign Keys. One Foreign Key is to the User table. The other Foreign Key is to the Class table. This allows you to assign one or more users to any class, and also assign one or more classes to any user. You'll find many examples of this sort of School, or Class Schedule database available by searching online.

Now, in order to address your question more precisely, we need to know what you mean by "...a Required Group". Is that a group of classes which every user is required to take?

--------------------
Go to the top of the page
 
lmossolle
post Nov 21 2017, 09:46 AM
Post#3



Posts: 5
Joined: 20-November 17



The required group is a group that is required to complete. The user has the same field. I am having issues in joining the tables so that a completion date can be added. Can you assist with that?
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 09:50 AM
Post#4


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, I'm still not really clear what it means to say that a required group is a group that is required to complete. Group of what? Classes? Are you saying that there are groups of classes? And that one of more classes are listed in each group? And that users are required to complete all of the classes in one or more groups? And that users are ALSO assigned to groups of users? And that these user groups are required to complete class groups?

Do you have a table that identifies these groups? How does that work?

Don't forget, we can only know what you tell us, while you are totally familiar with all of the business rules. Please fill in the gaps. Thanks.

--------------------
Go to the top of the page
 
lmossolle
post Nov 21 2017, 10:31 AM
Post#5



Posts: 5
Joined: 20-November 17



Sorry for delay, currently on the Class table. If the Training Coordinator adds a new or modifies an existing class and sets the required group to Contractors, Managers, Military, Non-Supervisor. If the User Table has a current role of Contractors, Managers, Military, Non-Supervisor the course should show up and a user should be able to add a completion date.
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 10:57 AM
Post#6


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, your tables are not properly designed, and you really can't do what you want to do with this table structure, at least not without a rather complicated code or SQL approach.

I STRONGLY urge you to study the materials linked in our Newcomers Reading List. Then take another run at setting up a proper set of tables to support your requirements.

Your Groups apply to the people. You need a Group table which has one record for each type of group: Contractors, Military, etc. Unfortunately, you fell into the trap laid by Microsoft when they introduced those pernicious "Lookup Fields" which allow you to create the sort of field currently in that table, i.e. the multi-select lookup table. I like to use the analogy that this type of field is a lot like sugar and coffee. Putting a table spoon of coffee into a cup of sugar seems sweet at first taste, but you come to realize this is not going to be good for you.

You also need to decide whether you want to track ONLY each person's current position, or if you want to be able to track the history of which positions they have held over time. If the latter, you need a junction table for that.

Finally, you DO need that junction table between classes and people because you need to be able to relate one or more classes to one or more people.

And one more thing. I suspect that you also need to dig deeper into what a "class" is. Is a class the subject matter itself? Or is it a specific instance of delivering the subject matter to a group of trainees on a given date at a given time?

In my experience, a "course" is the better way to capture the subject matter, while a class is the latter, i.e. a specific instance of delivering a course.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 12:22 PM
Post#7


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Actually, now that I've dug a bit deeper, there are a few additional problems in the tables that need to be addressed.

Groups, Branches and Divisions? What is the relationship between each of these? Do Branches report up to Groups and Groups up to Divisions? Or do Groups report up to Branches?

Whatever the relationships are, the NAMES of the people who are Group, Branch or Division leaders do not belong in the user table. You need tables for each of those levels of organization, along with a table (another junction table, in fact) to relate Users to Organization Levels.

You have also put in a field for "FullName", but no first or last names. We would avoid that 99% of the time, and this doesn't appear to be that 1%. You need to replace the current FullName field with two fields: FirstName and LastName.

Only the Primary Key for "users" would be entered in other tables to define relationships, never names.

There is an "attachment" field in one of the tables. You CAN do that, but it's another of those too much sugar for too little coffee situations. Internally stored attachments will quickly bloat your database to the point where it'll be harder and harder to work with.

And, it's important to revisit the fact that this Access Web Database is not going to be supported on Office 365, and unless you intend to use it with an on-premises SharePoint Site owned and operated by your employer, there's no advantage to using it. What is the environment where you plan to deploy it?

--------------------
Go to the top of the page
 
lmossolle
post Nov 21 2017, 01:35 PM
Post#8



Posts: 5
Joined: 20-November 17



I intend on putting it in a SharePoint 2013 env
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 02:00 PM
Post#9


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Okay, well, if your organization is willing to support this database in its on-premises SharePoint Server, it'll work for the short-term. My understanding is that this will be available through the end of the next licensing cycle, perhaps up to 10 years or so. After that, if your organization stays on the 2013 or 2016 version, of course, it's up to your SP admin what happens.

That said, you do need to revisit the table design, as suggested. I can't really offer more specifics about the branches, groups and divisions, because I don't know how they related to one another.

Please invest some time learning more about relational table design and give the tables another go.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 21 2017, 03:23 PM
Post#10


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Take a look at the attached accdb please.

IT'S NOT COMPLETE YET, but I wanted to give you some ideas about what needs to be done with it.

Note that I still don't know how branches, divisions, and groups interrelate, so those tables may well need revision. This, however, is my "best guess" at this point.

Note also that modified the look up fields as needed to minimize the potential for problems with them.

While I really dislike the Lookup field mechanism for tables, the unfortunate reality is that this is the only possible way to define and enforce referential integrity. RI is a higher priority, so they stay.

Attached File  MyTraining_Test.zip ( 62.84K )Number of downloads: 1

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 11:12 PM