Transform Data .. pls help!

[Toble]

Hi

I have a table with the following columns:

Code	Name	Master Code
A	Test1	125
A	Test1	983
A	Test1	608
B	Test2	452
B	Test2	367
C	Test3	195
D...	...	....

I would like to reformat the data in this format:

Code	Master Code 1	Master Code 2	Master Code...
A	125	983	608
B	452	367
C	195
D...	...	...	...

I tried using the CrossTab Query function but it comes up with a "too many headers" error.
Your assistance is much appreciated. Thanks!

[June7]

Why do Code and Name data look synonymous? Code A is always Test1, Code B is always Test2, etc?

Need another field in dataset that numbers each group of records. Is there a unique identifier field in the table? Autonumber will serve.

SELECT Code, [Name], MasterCode, DCount("*", "tablename", "Code='" & [Code] & "' And ID<" & ID)+1 AS GroupSeq FROM tablename ORDER BY Code, ID;

Then use that query as source for a CROSSTAB. The GroupSeq field will be the ColumnHead.

[NTC]

....or better yet - advise you that what you seek is not a concept that scales. Any side-by-side display is inherently problematic unless the ultimate quantity of columns is known not be excessive; because it cannot at some point be displayed whether it be on screen or paper.

in terms of your data - there is no specific value that defines who is Master Code 1 from 2, 3, etc. You seem to be relying on the position or order of your records for that - and that is a problem since record sets can be sorted any of a number of ways in a database - you really need a field with a unique ID for the 1s, 2s, 3s,.

[rpeare]

I think he's always going to have a problem with the number of fields in a crosstab june, depending on how many master codes there are. If he's actually got 900+ master codes there's no way a crosstab will work no matter what you do. The only way I can see to do this is with a massively complex UNION query (short of running code and creating a temp table) based on Individual crosstab queries for each 'code' and this would likely bomb out if the number of columns isn't the same. I really don't see a way to do this short of using code and a temp table for reliable results. You would also need to know the upper limit of master codes per main code.

[June7]

It's not the number of Master Code values that is the complication, it's the number of records for each Code. The example data shows max of only 3 records for each Code. Regardless, the dynamic nature of CROSSTAB query, as NTC says, is still problematic.

[rpeare]

Assume Code A has 3 Master Codes 001, 002 and 003 and Code B has 3 master codes 004, 005, and 006

If he creates a crosstab based on this data he'll end up with this:

Code	001	002	003	004	005	006
A	001	002	003
B				004	005	006

Which I don't think he wants, maybe the OP can clarify or unless I misread June's post he actually wants

Code	Group1	Group2	Group3
A	001	002	003
B	004	005	006

Whether or not he wants them to be in ascending or descending order doesn't really matter (at least as far as I can see), it's really a matter of just having enough columns to handle the number of master codes each primary code has associated with it. What is done with the data after that whether it's exported or reported can be handled just fine even if it is a crosstab but getting it into this format is the problem.

[Toble]

Hi all
Thanks for the reply. My data set has a maximum of 15 mastercodes.
The number of codes is 300+

And yes, I am after this format:

Code	Group1	Group2	Group3....
A	001	002	003
B	004	005	006
C	007	008
D	009	010
E	011
......

[June7]

Did you try my suggestion in post 2?

Building a report to run perpetually based on CROSSTAB is not easy. Review http://allenbrowne.com/ser-67.html

[Toble]

Hi June will give it a try shortly. On my way to the office

[rpeare]

Oh I see what you're suggesting now june (color me dumb) using a domain function to get a sequence number then basing the crosstab headers on that.

I'm unduly biased against domain functions in queries so I didn't even consider it!

You can base a report on a crosstab without knowing the column names but not without some programming see this:

https://www.accessforums.net/sample-...ery-45032.html

[Toble]

Ok I was able to reformat the table into a new query.
Dataset now looks like this

Code	MasterCode	GroupSeq
A	232	1
B	568	1
B	654	2
C	545	1
D	579	1

[June7]

Now build the CROSSTAB query.

[Toble]

Ok I was able to reformat the table into a new query.
Dataset now looks like this

Code	MasterCode	GroupSeq
A	232	1
B	568	1
B	654	2
C	545	1
D	579	1

I then used this query to create a cross tab query but the data now looks like this:

Code	GroupSeq1	GroupSeq2	GroupSeq3	...
A	1
B	1	1
C	1
....

I would like to have the MasterCode (i.e. 232, 568...) in the cell instead of the no. of occurence.
Thanks in advance

[June7]

Then select First() function instead of Count().

[Toble]

THANK YOU June7 !!!