Complicated Loop to Group Records

[jmcclain]

Here's a doozy that I could use some help with. I'm thinking some sort of loop would accomplish this...

My table has the following two columns:
UserID
TransactionID

Each user ID may have an unlimited number of TransactionIDs and there may be an unlimited number of different TransactionIDs.

For example:
UserID TranID
UserA XXX1
UserA XXX2
UserB XXX3
UserC XXX1
UserC XXX2

What I need to show is the different combinations of TranIDs user's have.

TranIDs
XXX1
XXX2
Users with this combination of TranIDs
UserA
UserC
XXX3
Users with this combination of TranIDs
UserB

Basically I need to create a dynamic group for each combination of TranIDs that exist in the table. This will eventually end up on a report

I tried concatenating the tranIDs into one field, that somewhat worked, but I'm having problems grouping it as the field is excessively long (1000+char).

Any ideas on how to do this?

 

[PHV]

And what about a PivotTable ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Golom]

Seems to be the same issue as thread701-789535. See my comments there.

 

[PHV]

Something like this ?
TRANSFORM Count(*) AS Expr1
SELECT Table1.UserID
FROM Table1
GROUP BY Table1.UserID
PIVOT Table1.TransactionID;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vbajock]

Usually when I have a problem like that I set up a recordset based on a SELECT DISTINCTROW sql statement. This will create a recordset exactly as you are describing.