Data transformations

[MikeD]

Hello,

I have a query showing results similar to below:

ID Price User
---- ---- ----
1 15 12
1 15 15
2 20 12
3 25 12
3 25 15

.... and I could do with one that shows ...

ID Price User1 User2
---- ---- ---- ----
1 15 12 15
2 20 12
3 25 12 15

.... but don't know how to do it. Any ideas?

<M>ike

 

[Michel Walsh]

Hi,

You need to rank the users...


SELECT *, DCount("*", "tableNameHere", "ID=" & ID & " AND User>=" & User )
as Rank
FROM tableNameHere


Save that query, say Q1.

Next, run the Crosstab query Wizard, perform the groups on ID and
Price ( at least, from the data you supplied, that seems to be on those two
fields), Pivot on RANK, aggregate with the operator LAST on field User.


With the data you supplied, the result you expect should be
obtained.


Hoping it may help,
Vanderghast, Access MVP

 

[MikeD]

Yeap,

With a bit of fiddling that seems to do what I was after.

Thanks,

<M>ike