Crosstab?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi all

I've a query that shows something like this:

Field1 Field2
A 1
A 7
A 8
A 4
B 5
B 6
B 2
B 3
etc


And I want to make something like:

A 1 7 8 4
B 5 6 2 3
etc


The idea is to create (later) some fields in a report that will say:

Unit: A Data: 1,7,8,4
Unit: B Data: 5,6,2,3
etc


I tried the crosstab query, but I end with something like:

1 2 3 4 5 6 7 8
A x x x x
B x x x x


Where 'x' is some value dependant on the grouping function.
Taking into account that reality is a lot more complicated (not only A
and B, and hundreds of different data values for each), I'm really at a
loss on how to proceed.
Any hints are appreciated!
Thank you in advance.
Kind regards,
 
Hi,

You would need a third field

Field1 Field2 F3
A 1 1
A 7 2
A 8 3
A 4 4
B 5 1
B 6 2
B 2 3
B 3 4


and in this case, f3 would supply the new fields generated by the crosstab.


To get the list-aggregated result, in the other hand, you can proceed like this.

I assume your initial table, ListArgs, has two fields, Grouping and Item.

First, create a table , ListResults, like (VBA code):



CurrentDb.Execute "SELECT DISTINCT ListArgs.Grouping, IIf(False,'',Null) AS Aggr INTO
ListResults FROM ListArgs;"



Next, make the following update:


CurrentDb.Execute "UPDATE ListResults INNER JOIN ListArgs ON
ListResults.Grouping=ListArgs.Grouping SET Aggr=(Aggr + ', ' ) & ListArgs.Item "


The result is in table ListResults.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top