Amend Cross Tab Form?

[Gasman]

Hi all,

First of all I apologise for the vagueness of this post, but the db in question was created back in November (with help from this site) and I have not touched it for over 6 months. I no longer have access to it, as I left the company in March.

Background.

In my last place of work I was asked to create a db to assist with allocation work to my colleagues. I made it as data driven as I could at the time.

One form (Form A) allocated work. That had a combo box that contained the queue owner names. The Flow Leader would select a name from the combo and how many cases and then the form would update a table with that data.

Another form (Form B, a crosstab form) displayed the summary information for the work available, consisting of product, method of payment and the queue owners in the format below. (Unable to get it to line up despite creating in Excel, but i hope you get the idea.)

Product	Method	Total	QueueName1	QueueName2	QueueName3	QueueName4
A	Cheque	41	15		26	
A	BACS	34		12	17	5
B	Cheque	23	2	5	7	9
B	BACS	74	11			63

Now at the time I first created the db, it only had 3 queues to process, and I seem to recall it was easier for me to recreate the query and form when a 4th was required.? I was looking at dynamic crosstabs after this change, but not given any time to explore that,(this was not my main job after all ), which in hindsight might have been a bad idea. :banghead:

Problem.
Anyway, I have just been in touch with my ex Flow Leader re a reference, and he mentioned that they need to change the names of the queues.?

For Form A that would be just changing the data in a table to indicate who are the queue owners. Easy enough for them, bearing in mind that I was the most experienced Access user there.

For Form B (the crosstab form) I think there will be a problem, but I am hoping that as it is only the data that is changing (ie, the column headers for the queue numbers) that they *might* be able to just amend the form, with the different names?

I really doubt they can ask their IT dept, as that is why he mentioned this in the first place.

Am I on the right track for instructing them on what might need to be changed on the crosstab form?

Otherwise it would be amending the crosstab query that supported the form and recreating the form, much as I did when the extra queue name was required, which would be even harder for them to do.

How would the experts here approach this problem?

TIA

 

[shoji]

Hi Gasman,

Since I am a code mania, I tend to handle this kind of situations all by code, but I would not recommend this to other people. This is what I would suggest.

Crosstab queries are really powerful, but for a price. As I understand it, your problem stems from the fact that queue owners change and thus the crosstab column names change. So what you should aim at is to use the same names for the columns. This way you don't have to change the form design every time you change queue owners.

First, you have to decide a possible maximum number of queue owners you can take. For this example, let's say 10.

Next, you create a small table, tblqueuowners, and its fields are

1. QueueOwnerID
2. QueueOwner


Create 10 records even if you don't have 10 queue owners. Use texts rather than numbers for the QueueOwnerID with the sequential 2-digit number like "Q01".

QueueOwnerID | QueueOwnerName
Q01 | John
Q02 | Mark
Q03 | Susan
Q04 |
. | ..
. | ..
Q10 |

Now the trick is you have to modify the crosstab so that QueueOwnerID, not QueueOwnerName, should appear on the table. You can just INNER JOIN the original table with this new tblqueueowners. (There will be one more modification with this crosstab query, but that will be discussed shortly.)

Next, go back to your form (Form B) and add more text boxes so that all 10 Queue Owners can be displayed. You can use Labels for the header, but I will use Text Boxes with the appropriate format, and in their Control Source, enter
=DLookup("QueueOwnerName","tblqueueowners","QueueOwnerID='Q01'")

You can adjust the qualifier for the rest of the text boxes.
Now, assuming there will be no more than 10 queue owners, this form does not need any modification even when they are changed.

Lastly, there is one problem with the crosstab query. If a queue owner does not appear in the data, the crosstab will skip that column. This is not desirable. To prevent that, i.e., to show every queue owner whether it appears or not, you can add one more modification in the crosstab query.

Open the crosstab in SQL mode and change PIVOT clause like

PIVOT [QueueOwnerID] IN ("Q01","Q02",Q03", ..., "Q10")

This will ensure that all the column name will appear in the correct order.

Needless to say, I am writing without looking at the real data, so I may have missed something, but I hope you get the idea and make suitable changes.

Good luck.

Shoji

 

[Gasman]

Shoji,

Thanks very much for the reply. Unfortunately I no longer work there and I was pretty much the only one that was capable for creating some simple forms.

TBH I did ask if the queue owners were likely to change and was told No. After adding the 4th queue owner that would be it. That is why I looked at dynamic crosstab forms, but was not alowed any time to produce one. I know more about Access now, than I did then.

So all I can do is try an explain to them what I believe would work, in that if I change the source of the text box 1 from Shoji to Gasman, etc etc, and the owners are added in the Queue table then the form will work with minimum changes.

I should be able to walk/talk them through that easy enough.

I will advise that they try and get an IT Access person to do it, but they were so unsuccessful before this is the reason I was asked to do it.

I will however make note of your method. That is really handy for set number of entries in a cross tab form.

They do this all the time, so i should have known better. In another system they would for instance put your name down as the Queue Owner, then when you leave they change it, but then have to inform everyone who uses that system, that you now need to assign to such and such name, rather than having Payment Queue One or something that can remain static.

 

[shoji]

That is OK. I believe this is not a unique problem. Everyone who uses crosstab and tries to present the result in a report has this problem. So, I hope other members in this forum may find this a useful hint.

Shoji