Creating, editing, and storing user-friendly logic

[TrainingExcellence]

I am exploring the idea of building a learning management system in Access for my company. Our current LMS is too inflexible. It enables me to enroll users into learning plans using very basic logic. For example, "if a person is in x learning plan, they need to complete y courses." I would like to upgrade the logic to include more complex things such as "everyone hired after x date in y position must take z courses."

I'm guessing queries would be the way to make this happen, but my concern is that queries are formidable to those untrained in Access. Assuming non-Access-trained users will have to edit learning plans in this database, is there a way to make queries user-friendly? Or is it possible to store enrollment data in a table that a query can access, so that the query can be set up once and forms can be used to edit the enrollments?



I hope that makes sense. I appreciate input!

[ssanfu]

It sounds like you are relatively new to Access. You should first work through the tutorials at http://www.rogersaccesslibrary.com/forum/forum46.html to familiarize yourself with how a dB is designed.
Then BEFORE getting on the computer/Access, draw your table designs using a whiteboard, pencil & paper, cardboard, a window and dry marker, etc. (Much easier to make changes to the design)


"Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.



I stole the following from a post by orange - it is better than anything I could write:
---
"I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
<snip>

<snip>
I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
---

Write a description 4-5 lines in plain English (as you would tell an 8 yr old) of the "business involved". No jargon and no database quasi-terms. Bottom line -- get a clear statement of WHAT you are trying to automate. Once that is clear, then consider the options that may exist for HOW to do that automation using Access.
Don't be too quick to jump into physical Access (or any other DBMS)."
Good luck.

[CJ_London]

you've explained your business objective, but not the process as to how this might be achieved. Do you already have all the information you need? What roles (i.e. who) will be making or implementing these decisions? What sort of reporting do you want to find out who needs training and monitoring how the training progress, etc.

You mention queries, yes you will need these. Can they be made user friendly? Perhaps, but at the end of the day all you will be doing is creating your own query builder, which some might find easier some may not - either way they will almost certainly need to understand the concepts of a query.

Basic queries can be written down in English (or language of your choice) so you can give users a prompt using combos, listboxes and tickboxes, then have some vba logic to try to construct the query. Take this very simple query

SELECT fld1, fld2
FROM someTable
WHERE (fld10='X' AND fld11='Y') OR fld11>10

First you need the user to choose a table
Then they choose the fields they want to see
Then apply some criteria - criteria might use AND and OR and need brackets, you have a number of comparators (=,<,>,like etc) and you will need to know datatypes as well

All that can be done fairly simply with a form, subforms etc. But the user needs to understand the importance of bracketing the criteria.

But now up the level - you need two tables, perhaps three, perhaps more. Now it gets complicated

You might want to google the topic 'report writing' for ideas - take a look at the sort of functionality provided by a CRM system for example, but the basic premise of having enrollment data is not sufficient - it will need to join in some way to the training data and no doubt other tables using a 'master query'. Consequently it could be a very large and potentially slow recordset.

But assuming you have your master query you would create a datasheet form on this query, which the user can sort and filter on, hide fields, even change the order. Then they can click a 'save' button to save all these properties in a table somewhere to be applied to the same datasource at a later time.

You might have the facility for the user to say 'I want data from this table, that table and this other table' and you would be able to work out the relationship paths between these tables to create a 'master' query - and save it of course for future use.

Finally, your naming convention would need to be very clear and unambiguous. You could use the field caption property in table design to make the names a bit more user friendly.

[TrainingExcellence]

Thanks Ajax,

So it seems as if either the users will have to be fairly handy with Access or I will have to be a master to set it up to the standard I would like. I'm now leaning towards using a combination of Access, Excel, and Get and Transform queries, thus utilizing each application's strengths: Access for storing data and creating forms, Excel for managing the logic of learning plans, and Get and Transform to bring it all together.

[CJ_London]

fairly handy with Access

I would say handy with database logic - it is not something specific to access. Many users will have a working knowledge of excel , but excel logic is completely different to database logic - I would say almost the complete opposite.

So I would choose a technology your users are familiar with and supply the data to that - e.g. dump out a 'master query' to excel. Alternatively, given your monika, train them in what they need to do, provide a manual.