Crosstab Problem...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to transform data such as;

Col a Col b Col c
1 13 45
1 17 670
1 110 32
2 3 51
2 65 540

into;
Col a 1 2 3
1 45 670 32
2 51 540

i.e. I want to do a crosstab, where Col C has the values, col b values are
not important but they give the order, and so instead of using colb as the
column headers I want to use a kind of autonumber.

Any suggestions would be much appreciated!
:o)
Thanks!
 
You can create your Column Heading values from your original records by
using a calculated column like:

ColHead: "Col" & DCount("*","data such as","[Col a]=" & [Col a] & " AND [Col
b]<=" & [Col b])
 
Thanks very much Duane !

Huge help, problem was bugging me for ages!

Duane Hookom said:
You can create your Column Heading values from your original records by
using a calculated column like:

ColHead: "Col" & DCount("*","data such as","[Col a]=" & [Col a] & " AND [Col
b]<=" & [Col b])

--
Duane Hookom
MS Access MVP


Olly said:
I want to transform data such as;

Col a Col b Col c
1 13 45
1 17 670
1 110 32
2 3 51
2 65 540

into;
Col a 1 2 3
1 45 670 32
2 51 540

i.e. I want to do a crosstab, where Col C has the values, col b values are
not important but they give the order, and so instead of using colb as the
column headers I want to use a kind of autonumber.

Any suggestions would be much appreciated!
:o)
Thanks!
Click to expand...
Click to expand...
 
With a DCount() in the select statement it may take "for ages" to run.

--
Duane Hookom
MS Access MVP
--

Olly said:
Thanks very much Duane !

Huge help, problem was bugging me for ages!

Duane Hookom said:
You can create your Column Heading values from your original records by
using a calculated column like:

ColHead: "Col" & DCount("*","data such as","[Col a]=" & [Col a] & " AND
[Col
b]<=" & [Col b])

--
Duane Hookom
MS Access MVP


Olly said:
I want to transform data such as;

Col a Col b Col c
1 13 45
1 17 670
1 110 32
2 3 51
2 65 540

into;
Col a 1 2 3
1 45 670 32
2 51 540

i.e. I want to do a crosstab, where Col C has the values, col b values
are
not important but they give the order, and so instead of using colb as
the
column headers I want to use a kind of autonumber.

Any suggestions would be much appreciated!
:o)
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top