Three Stooges query/report CHALLENGE!

[Minty]

The last main table is better.

The only issue is that you have entered data for courses and skill levels that haven't been taken yet, and you shouldn't do that. A number of reasons:


That person may never take that course.
We can query who hasn't taken a course.
We only want to query actual results for your report.

Another golden rule of databases - not having any data for a record can sometimes be as important as the data that is there.

You could add a field to the main table to account for course to be taken:
EnrolementDate
CompletionDate

Then you can still see who is due to take a course, and how long it took for them to compete it, but still ignoring anyone who isn't ever going to take a specific course.

[i-Zapp]

OK, like they say "be careful what you wish for!" LOL, my scrubbed FULL "demo" database is attached.

However, some explanation IS required for the methodology contained therein:

• my very first post in the forum (this is the second) asked the question of how to show a list of skills on form, some skills approved, but to also show the skills that have NOT YET been approved (see Red text button on Main form)
• the solution was to create a cross-join between the list of skills and the technicians
• this of course created a challenge in updating the tables, which required it to be done via VBA
• in simple terms, the progression of a technician is defined by the list of required skills being approved in each skill LEVEL. once all the skills for a given skill LEVEL are approved, he is considered to be qualified at that level, for that test type
• ultimately, a calculation is performed to determine what percentage of skills have been approved for each skill level, for each test type, for each technician.

The last piece of the database was to show a summary of technician training progression, as illustrated in my first post. I thought i was close, but seems I've painted myself in a corner.
If a rework of the basic architecture is what's required to achieve that, so be it - I'd rather do it right.

I can't believe you fine gentlemen (gentlepersons?) are willing to dive in and support. Very grateful! I hope someday i can repay the favor.

[Minty]

I thought I had better revisit this, as I have found half an hour on a Friday afternoon, and you have kindly provided the full thing!
It looks as if the main training records are held in tblTrainingMatrix.
Before I dig in too far, you have a mistake in your relationships - Between tblSkill and tblTraingMatrix you need to link on the Skill_ID fields, you have it wrong at the moment.

I am assuming from your description above that you want to calculate the technicians competence in any area, based on how many of the skill levels they have completed within that particular area?

So, looking at test type 1 Test ZM , there are 15 skills listed, with varying numbers of Skill levels. (1 of level 1, 7 at level 2, 2 at level 3 and 5 at level 4)
We can extrapolate from this with a simple query that 4 technicians have done 25 sections of some of this training between them.

To get to the results you want, we have to set up a couple of "pre" queries to determine how much of each skill level is available to be taken against how many have been taken.
You can then join them against the records and see how much progress in each course there is with each technician.

See qryCompletitonResults in the attached.
That should hopefully get you going in the right direction.

Your cross-joins are handy for creating a list to join to to display everything, but not so much for producing a results list.

[i-Zapp]

See qryCompletitonResults in the attached.
That should hopefully get you going in the right direction.

FANTASTIC. I see what you've done. Makes perfect sense now, and "magically" the reports come easier!

Thank you Minty.

[isladogs]

See my recent article which shows how the layout from post #11 can be done using normalised data in a continuous form as well as a report: Hide Duplicate Column Values (isladogs.co.uk)

[madpiet]

Correct thread???

Why do something the hard way in Access when I can just use PowerQuery to pivot the data? I mean, it's not that I don't know how to write VBA, but if I don't have to, I won't. And using PowerQuery means I didn't have to.

[madpiet]

So what about the "simplification"? What does your database do? Just track people's skill levels on various categories of skill? Where's the hard part? I like those.