Dynamic Crosstab Report

[Cbell5]

I have a crosstab query that looks like this:

SupervisorName
EmployeeName NumberofCalls 17 18 19 Michael Jordan Scottie Pippen 3 5% 10%
5%
Joe Montana Jerry Rice 7 6%
10%
8%

I need to create a report from the crosstab query that is dynamic. The numbers 17, 18, and 19 represent different questions. Such as, "Was the caller's name requested correctly?" The numbers below that say how many times the question has been missed, so Scottie has missed that question 5% of the time on his phone calls.

Here is the tricky part. Each person takes different types of calls like Sales, and Customer Service. Each type of call has a different amount of questions - Sales = 10 and Customer Service = 15.

For the report I need to have the actual question for the type of call specified show up instead of the number 17,18, 19, etc...

Does anyone have an idea of how I might do this?

 

[vbaInet]

Here's a link on how to build a SQL string in vba. Construct the names as aliased fields.

 

[Cbell5]

Thank you for the link. I understand much better how to create an SQL in VBA now.

How would I construct the names as aliased fields?

Would I have to do it for each question or would it do it for me?

 

[vbaInet]

If you're using the fields as field names and if they're going to be the same across all records then you need to create a query that will lookup the name of that number. You will include the two tables.

Alias:
http://www.quackit.com/sql/tutorial/sql_alias.cfm

 

[Cbell5]

Thank you vbaInet! Both of the links you provided are very useful.

I got that to work!

Now that it is pulling in the proper names I have to figure out how to get the rich text to appear in the column heading properly instead of this (<div><u>Call Log</u></div>).
Any ideas?

Thanks

 

[vbaInet]

Great!!!

Instead of what exactly? Any formatting you want to do should be done on the control (on the report) that is displaying Call Log.

 

[Cbell5]

Perfect! Thanks for all of your help!

 

[vbaInet]

Good job! Glad you got that working.