Challenge on Crosstab Query

[mlai08]

Challenge on Crosstab Query -RESOLVED

Hope any Access guru can help me with this problem.

I want to create a cross training matrix with crosstab query in Access to show the relationship between the trainers and trainees with purpose of the trainings from the following table.

XTrainID...Purpose.............Trainer............ .Trainee
1.............Web Focus......... John................Edward
2.............Search Engine.....Mary................John
3.............CEI................... Matthew...........Edward
4............SAS................... Paul..................Mary
5.............NET.................. Greg.................Sam
6............SAS................... Jennifer..............Mary
7............SAS................... Matthew.............Edward

The resulting matrix should look something like this:

............ Greg...... Jennifer....John.............Mary........Matthew.. ....Paul
Edward..............................Web Focus...................CEI, SAS
John.............................................. .......Search Engine
Mary...................SAS........................ ....................................SAS
Sam....... .NET

I tried to use crosstab Q and picked Trainee as Row Heading, Trainer as Column Heading and Purpose as Value - with the Total option as First, but it would only show the first purpose even if trainer with the same trianee has more than 1 cross training involvement. The example is for Matthew & Edward. In this case, it only show CEI but not SAS. My challenge here is how to concatenate training purposes with the same trainer and trainee.

Any suggestion would be much appreciated.

Mike

 

[CraigDolphin]

Mike,

The issue here is that you want the crosstab 'cell' to contain more than one unit of text information if more than one record exists in your source table. With numerical data you can use built-in summary options like sum or average to mathematically convert the many into one unit of information. Unfortunately, there really aren't any total options to do this with text (except to choose one option from the many available as you've learned)

The answer is that you'll need to create a query of your table that does this job for you, then base your crosstab on that query.

So, for your new query, you'll probably group by trainer and trainee and add an expression field that calls a vba function that you'll need to write.

You'll need to pass two pieces of information to the function (Trainer and Trainee) when you call it from the query field. ie:

MyCombinedPurpose: YourFunctionName(Trainer,Trainee)

In the function itself, you'll need to create a recordset of the original table. The recordset is likely to be a simple select query of the original table where trainer = <this trainer> and trainee = <this trainee>.

You'll be left just with one or more records for that pairing of trainer and trainee.

Then you'll need to define a string variable, and loop through the records in the recordset building the text string as you go.

Something like

Do While Not rsMyRS.EOF
MyString = ", " + MyString
MyString = rsMyRs!Purpose & MyString
rsMyRS.MoveNext
Loop

Then when the loop terminates, the function returns the string you've built to the query or else a zero Length String.
ie

YourFunctionName= Nz(MyString,"")

Here's a link to a tutorial on creating and using recordsets (http://www.devdos.com/vb/lesson4.shtml) that may come in handy for you to reference.

Then, once you have your function working and the new query showing the results you need, then base your crosstab on that and you can select Total by First for the MyCombinedPurpose field (since there will be only one record for each pairing of trainee and trainer this won't matter and the crosstab still requires some direction in case you did have more than one record in the source query)

If you get stuck or need further explanation post back.

HTH

 

[mlai08]

HTH,

Your suggestion is a good approach but the problem is I can not make the function work. The CombinePurpose field in the query always return nothing. Not sure what I have done wrong. The Cross Training table have the TrainerID & TraineeID looked up to the Employee Table for Employee Name. I don't think this should impact on the running of the function.

I have attached a test db and hope you can help me to resolve the problem.

Thanks for your assistance.

Mike

 

[CraigDolphin]

Mike,

you had a couple of issues with your db that I've sorted out. The biggest issue for getting your function to work was that you were not using the correct function name. Your query was calling CombinePurpose() whereas your function is named CombPurpose.

You also included table-level lookups. There are numerous threads on this forum as to why these are generally a bad idea. I've removed them and re-done your query to show you the information you need without them. A good example of why these are a nuisance occured when I created your crosstab. Instead of showing the name of the trainer (column heading) the crosstab only showed the number.

I also switched the datatype of one of your function variables to variant so that it can support a null 'value' and therefore can remove an unwanted commas in your string.

There are some other issues I've left alone....like not naming the key fields the same in both tables. Although you can do this and have everything work, it is a much better practice to name them identically in both the parent table and the child table. This prevents confusion later on, and helps anyone else that may someday inherit your database figure out what the relationships are much more easily.

Goo luck with the rest of your project.

 

[mlai08]

Challenge on Crosstab Query - RESOLVED

CraigDolphin,

Thanks for your quick response. You are very resourceful and helpful.

I believe I did put the function name correct on the test db. I used CombinePurpose as the field label only.

Anyway, you hit it right on the nail - the problem is with using the employee table look up. I made the changes as suggested and the function is working like a charm. Thanks also for the reminder of using consistent field name in different tables.

All in all, you are the best!

Mike