Group report based on field values

[convexcube]

To keep a record of training levels for different tasks, I have 18 option groups with 4 options values each: 0 labelled as "None", 1 labelled as "Trainee", 2 labelled as "Competent" and 3 labelled as "Expert".

I would like to develop a detailed report that will show one employees training levels based on the values of training for each of the 18 categories.
For example, an employee with these values in a table:

Service = 3
Cleaning = 2
Restocking = 3
Administration = 1
Cash Balancing = 0

would be produced in the report like this:

Expert
Service
Restocking
Competent
Cleaning
Trainee
Administration
None
Cash Balancing

I realize this would involve the use of an expression in the group by section of the report, but I'm not sure what to put in it. Any help would be greatly appreciated as I have only just started with the reports section of this, my first database.

Thanks in anticipation,
Ken.

[Dököll]

To keep a record of training levels for different tasks, I have 18 option groups with 4 options values each: 0 labelled as "None", 1 labelled as "Trainee", 2 labelled as "Competent" and 3 labelled as "Expert".

I would like to develop a detailed report that will show one employees training levels based on the values of training for each of the 18 categories.
For example, an employee with these values in a table:

Service = 3
Cleaning = 2
Restocking = 3
Administration = 1
Cash Balancing = 0

would be produced in the report like this:

Expert
Service
Restocking
Competent
Cleaning
Trainee
Administration
None
Cash Balancing

I realize this would involve the use of an expression in the group by section of the report, but I'm not sure what to put in it. Any help would be greatly appreciated as I have only just started with the reports section of this, my first database.

Thanks in anticipation,
Ken.

Hello, convexcube!

Nicely detailed info. Have you a working query to build on? You may need to get your query to grab all of this informaiton first if you haven't already, then build a form from it. You can do additional calculations form the query. whichever you're more comfortable.

You could in turn select the proper fields and place them whereever you need on the report. The Header of the report is helpful at times. Some people like to add fields to the Details portion.

Let us know where you are.

Also, please stay tuned even if you have heard from me, a number of experts are here and will soon come to your aid.

In a bit!

[convexcube]

Thanks for the swifty reply,

I realized that I probably wasn't as clear as I could be in my original post. To clarify, I already have my forms built and I am now working on reports.

I did look into using a query for this section of the report but it wont give me the results I'm after. What I want in the report is basically the reverse of how the table stores the information. So, in my previous example: Service, Cleaning, Administration, etc. are fields in the table and the option groups on the form allow values of 0-3 (None,Trainee,C ompetent & Expert).

What I would like to show on the report is a list of what fields in the table under a heading of each of the values 0-3 (None,Trainee,C ompetent & Expert).

I have been working on it and have found a solution involving a text box on the report for each value. This text box holds and expression that will display a list of fields that have that value. For example the "None" value (0) text box has an expression like this:

="<B>None<B/>" & IIf([fldService]=0,"<BR>Service ",Null) & IIf([fldCleaning]=0,"<BR>Cleanin g",Null) & IIf([fldRestocking]=0,"<BR>Restock ing",Null) & IIf([fldAdministrati on]=0,"<BR>Adminis tration",Null) & IIf([fldCashBalancin g]=0,"<BR>Cash Balancing",Null )

This is of course all in one line. The text format is set to rich text and I have also set the can grow property to yes so that the text boxes only take up the space they need to.

The result is a very clean looking list of what areas an employee has opportunities for improvement in, rather than having to look through all the tasks they do to see what level they are at.

Just thought I would share the solution I came up with in the hope it may help some one else in the future,

Regards,
Ken.

[NeoPa]

I'm pleased you've found a solution Ken.
However, this is a perfect illustration of where normalisation (See Normalisation and Table structures) would have saved you a whole lot of complication. If the data had been structured where any and all levels were stored separately, then processing through via the competence rather than via the user, would have been a matter of ridiculous ease.
It's rarely easy to redefine a structure after the fact, so I'm not suggesting you try that (unless you want to of course). What I do strongly suggest is that you look into the concept and use it in future projects.

Oh, and by the way, thanks for returning to post your solution. We like that attitude here. Welcome to theScripts :)

[convexcube]

Restructuring

I'm pleased you've found a solution Ken.
However, this is a perfect illustration of where normalisation (See Normalisation and Table structures) would have saved you a whole lot of complication. If the data had been structured where any and all levels were stored separately, then processing through via the competence rather than via the user, would have been a matter of ridiculous ease.
It's rarely easy to redefine a structure after the fact, so I'm not suggesting you try that (unless you want to of course). What I do strongly suggest is that you look into the concept and use it in future projects.

Oh, and by the way, thanks for returning to post your solution. We like that attitude here. Welcome to theScripts :)

Thanks for the link about normalisation which I now understand much better than from reading any other article about it. Being my first Database, There have been a lot of issues I wasn't aware of when I began. Deciding to present the report this way is one of those issues.

Just so I'm clear, could you let me know if I'm on the right track in my thinking. As I see it I should have had a separate table for Training levels that would include a foreign key for employeeID, then columns for the None, Trainee, Competent and Expert levels. The fields would then allow the different tasks to be chosen in these.

Since I am using an unbound form so that searching can be performed on any or all criteria, I am uncertain as to how the form would interact with the table. I suppose, instead of using the value from the option group values would have to be generated directly from the option buttons to ensure only one value for each employee over the 4 columns. But, How would you then recall that information to the option groups in the form that is structured the opposite way? I imagine it would be through quite extensive VBA code.

These are just a few initial thoughts I'm having in solving this. I may have completely the wrong end of the stick or just not thought through all the options. I would definitely appreciate any feedback.

It is really great that you guys are so willing to help a newbie like me and I have learned a huge amount already from this and other forums.

Thanks,
Ken.

[NeoPa]

I would expect a table structure similar to the following :
Table Name=tblEmploye e

[i]Field; Type; IndexInfo[/i]
EmployeeID; AutoNumber; PK
EmpName; String
'Various fields that relate directly to the employee (only)

Table Name=tblCategor y

[i]Field; Type; IndexInfo[/i]
CategoryID; AutoNumber; PK
CatName; String

Table Name=tblEmpCat

[i]Field; Type; IndexInfo[/i]
EmpCatID; AutoNumber; PK
EmployeeID; Number; FK
CategoryID; Number; FK
Score; Number

Some sample data for an employee "Fred Bloggs" might be :

Table=tblEmployee
1; 'Fred Bloggs'
2; 'John Smith'
3; ...

Table=tblCategory
1; 'Service'
2; 'Cleaning'
3; 'Restocking'
4; 'Administration'
5; 'Cash Balancing'
6; ...

Table=tblEmpCat
1; 1; 1; 3
2; 1; 2; 2
3; 1; 3; 3
4; 1; 4; 1
5; 1; 5; 0
6; 2; 1; ...

This stores the data as you had it before, but far more flexibly.
To add another category later on does NOT involve a redesign of the system.
It's true that, with a more flexible structure, reports can sometimes be more complicated as they are (naturally) easier with a rigid structure, but rigidly designed systems do have a habit of breaking when things change.

PS *** WARNING ***
It is very strongly advised that you keep backup copies of your work before embarking on fundamental changes if that is your plan.
It's definitely a good way to go, but don't get caught needing it to work after making some changes, and then getting stuck.

[convexcube]

Thanks NeoPa,

I can see how this would make the system much more flexible. I don't intend to change the exisiting structure at this time as it would involve a lot of work and push the project back too much, but I may rewrite it in the future when I add other features that I've discovered along the way. I will certainly use this information for all my future databases.

Once again, thank-you to all of you and to this site which is an excellent source of information.

Regards,
Ken.

[NeoPa]

No problem Ken.
That's probably a wise choice in the circumstances ;)
Keeping it in mind for later is about the best thing to do, as turning a project upside-down half way through is a dangerous way to go.