Data transformations

  • Thread starter Thread starter MikeD
  • Start date Start date
M

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
 
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
 
Back
Top