Three Stooges query/report CHALLENGE!

[i-Zapp]

Now that I have your attention here's my predicament - I'd like to translate the output of a query which reports the "training level completion" for a group of technicians (Larry, Curly, and Moe) into a more compact format. That format is essentially a nested IF statement, and easy enough to implement if not for the 4 different skill levels. The idea is to report the highest skill level with 100% completion.



In simple words,

1. For each Technician (in rows), and for each Area (in columns) -
2. Starting with Advanced skill level, is the value =100%? If so, then capture the value of “Adv” for the report
3. Else, move to next lower skill level, Moderate, and check is value=100%? If so, then capture the value of “Mod” for the report
4. Else, move to next lower skill level, Basic, and check is value=100%? If so, then capture the value of “Bas” for the report
5. Else, move to next lower skill level, Trainee, and check is value=100%? If so, then capture the value of “Trn” for the report
6. Else, capture a blank for the report

Here's what the sample Table looks like, and what the desired Report would look like (with some cond formatting) which was simple to do in Excel.

Thanks in advance!

[Minty]

Your data is not normalised. You are currently looking at a spreadsheet, not a database table I'm afraid.
If this data was normalised your query would be very simple by comparison.

If I get 20 minutes I might be able to put your data into the correct tables.

[i-Zapp]

Your data is not normalised. You are currently looking at a spreadsheet, not a database table I'm afraid.
If this data was normalised your query would be very simple by comparison.
If I get 20 minutes I might be able to put your data into the correct tables.

the attached access database is a table. don't know what "normalized" means though!

[i-Zapp]

Your data is not normalised. You are currently looking at a spreadsheet, not a database table I'm afraid.
If this data was normalised your query would be very simple by comparison.

If I get 20 minutes I might be able to put your data into the correct tables.

i've attached a new DB, with additional tables and a query. I hope that's what you wanted. (?)
thanks for the quick feedback.

[Minty]

the attached access database is a table. don't know what "normalized" means though!

It's a table but not a data table that any database would use.
If you need to add a skill level or another course(Area of expertise) you would have to add new fields to your table, and the entire structure would need throwing away and re-creating.

You need at least four tables to store your data sensibly so you can query it a make a database.

ID	Technician
1	Larry
2	Curly
3	Moe

SkillLevel_ID	SkillLevelDescription	SkillLevel_Ordering
1	Trainee	10
2	Basic	20
3	Moderate	30
4	Advanced	40

Course_ID	CourseName	CourseSetupDate
1	Area-1	01/01/2024
2	Area-2	01/01/2024
3	Area-3	01/01/2024
4	Area-4	01/01/2024
5	Area-5	01/01/2024
6	Area-6	01/01/2024
7	Area-7	01/01/2024
8	Area-8	01/01/2024
9	Area-9	01/01/2024
10	Area-10	01/01/2024

You would then combine all those entities into a table that stores your results;

CourseResult_ID	Technician_ID	Course_ID	SkillLevel_ID	CourseMark
1	2	1	1	100.00%
2	2	1	2	100.00%
3	2	1	3	100.00%
4	3	6	1	100.00%
5	3	6	2	100.00%
6	3	6	3	75.00%
7	3	6	4	12.50%

This is how a database needs to be setup to work correctly.
Terrible sample attached, with a single form to enter the data on.

[Minty]

Sorry our replies crossed in the post

EDIT: Your second example is getting there but you need to separate out the courses and results into a meaningful structure.
The sample I uploaded is a very stripped down example.

[i-Zapp]

Your second example is getting there but you need to separate out the courses and results into a meaningful structure.
The sample I uploaded is a very stripped down example.

LOL, yeh my attachment is a MUCH simplified version of the REAL database i'm working on. Figured a simple 2D example would suffice, because at least in my case it's the final query in the development of the data that I assumed would be the basis for the Report. I suppose the subtlety I'm missing is that the underlying relationships will provide the structure to the Report?

[madpiet]

I just cheated and used Excel to unpivot that thing. Took like 10 seconds.

let
Source = Access.Database(File.Contents("C:\Users\User\Deskt op\ToolSheetDatabase.accdb"), [CreateNavigationProperties=true]),
#"_MachineTbl Query" = Source{[Schema="",Item="MachineTbl Query"]}[Data],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"_MachineTbl Query", {"MachineNum"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Tool Number"}})
in
#"Renamed Columns"

Then you'd just import that into Access.

[Minty]

LOL, yeh my attachment is a MUCH simplified version of the REAL database i'm working on. Figured a simple 2D example would suffice, because at least in my case it's the final query in the development of the data that I assumed would be the basis for the Report. I suppose the subtlety I'm missing is that the underlying relationships will provide the structure to the Report?

As is often the case simplifying things for examples doesn't help solve the problem at hand.
If you data really is stored as per table 1 then you have a problem.

if it's stored in a similar fashion to the example I loaded then your query is relatively simple.

So, show us the actual data structure, assuming it's not table 1?

[Minty]

I just cheated and used Excel to unpivot that thing. Took like 10 seconds.

let
Source = Access.Database(File.Contents("C:\Users\User\Deskt op\ToolSheetDatabase.accdb"), [CreateNavigationProperties=true]),
#"_MachineTbl Query" = Source{[Schema="",Item="MachineTbl Query"]}[Data],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"_MachineTbl Query", {"MachineNum"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Tool Number"}})
in
#"Renamed Columns"

Then you'd just import that into Access.

Correct thread???

[i-Zapp]

So, show us the actual data structure, assuming it's not table 1?

Minty - the database in post #4, is about as elaborate as I think it needs to be... Just think of each Area's value as being entered directly into the Main table with a form. No need for a level of complication to determine HOW that number is generated/calculated.

I think the crux of the challenge is (at a Report level) to retrieve the individual "detail-level" values that would be shown in the groups, so that you can do the IIF business (as per my original conditional IF statements). I suppose if there was a way to construct the QUERY to generate the conditional values, that'd be even more slick.

[Minty]

I'm afraid you need to read up about normalisation. Here is a good start
https://www.accessforums.net/showthr...133#post352133

You are storing data as field names. (your training Areas) any time you see either a repeating group (Contact1, Contact2 etc) you should in 99.9% of cases move that data to a table. Because it is data, not an entity.
Once your data is stored as per tblCourseResults the report and query to drive it become trivial.

As it stands you will have to manoeuvre through more hoops that a hoop shop stocks to try and get your results.
And if anything changes in the structure (lets say skill level Very Advanced arrives or Course Area-11) the whole thing will break because you have had to kludge you way around a bad structure.
I know it's not what you want to hear, but fix it now, or forever regret not fixing it, and all the band-aids you will waste hours on working around it.

Excel works on wide column data. Access and any relational database normally needs Long and Thin data.

Trust me a little bit on this, I do earn a living out of it.

[Welshgasman]

the attached access database is a table. don't know what "normalized" means though!

Read this post's links.

https://www.accessforums.net/showthr...553#post510553

[i-Zapp]

ok one more attempt at a suitable "normalized" database...

[madpiet]

unpivot that thing.

Yup. I unpivoted the data.