Crosstab Query with SUBGROUPING in Column Heading.

[ZaZa]

Hi Everyone,

I have a really sticky problem with a cross tab query that I am developing for a welding department.

I want to split the column Heading into two "sub columns" depending on the value Column having a certain criteria.

Let me explain: I have a CT query with the follwong row headings:
a) WeldNo
b) DateWelded
c) Welder

The Column Heading is: TestType ( this is becasue there are are up to five different tests that can be done on a weld)

The Column Values is: TestNO, ( Total = FIRST)

So for each weld, welder and WeldDate, the CT is supposed to show the type of test done and the TestNo of the test.

The CT looks like this:
WeldNo WeldDate WelderID TestX TestY Test X
---------------------------------------------------
Weld1 12-06-02 James X12 Z10
Weld2 13-06-02 Mark X16 Y24
Weld2 13-06-02 James X16 Y24
Weld3 15-06-02 Steve X20 Y25 Z13



I hope you get the meaning.( Note that the same weld could be welded by two persons eg weld2 was welded by Both James AND Mark)

BUT I WANT TO SHOW THE RESULT AS A SUBGROUPING OF THE TESTTYPE COLUMN HEADING. Eg, If James failed the TestY on Weld2 , I want the TestY column to look something like this:

WedlNo /WeldDate / WelderID / TestX / TestY / Test X/
----------- / / / /Pass/Fail
-----------------------------------------------------
Weld1 12-06-02 James X12 Z10
Weld2 13-06-02 Mark X16 Y24
Weld2 13-06-02 James X16 Y24
Weld3 15-06-02 Steve X20 Y25 Z13


So , in effect the Columm heading would have another subgrouping For Pass/Fail.


I hope this makes sense to every one and I that someone can help, i have been working on this for the past couple of weeks and I am really frustrated.I can get the standard CT querry but have not been successful in getting the column heading to have a sub grouping.

Regards,
ZaZa

 

[ZaZa]

Opps,

It looks like the CT Table is not properly aligned. The /'s are supposed to represent vertical lines between columns and the words "Pass/Fail" are supposed to fall directly under the word "TestY".

Regards,
ZaZa

 

[ZaZa]

ANYONE???[Sadeyes] [Sadeyes]

ZaZA

 

[gusbrunston]

Hi ZaZa:

I'm sorry that you haven't gotten any help yet. That is not typical of Tek-Tips. However...

I can't understand your question. I use crosstabs and I've never tried to divide columns. Sometimes I have to use preliminary queries as data sources for the crosstab. I suppose you can nest these preliminary queries as deep as you want. Maybe you could base your crosstab on a pyramid of queries.

About your alignment: you can use TGML to align tables--
e.g., when you use the "code" marker,

"01234 5678 AbcdEfgh" looks like":

01234 5678 AbcdEfgh

It changes the proportionally spaced font to a fixed spaced font so numbers and letters can line up properly.

Sorry I'm not able to help with your crosstab query.



Gus Brunston An old PICKer, using Access2000.