Compiling items with same ID horizontally

[tomahs]

So I am currently starting to learn access and i came across a problem, I
have been given a table which includes a column of UserIDs, there are a few
more columns of data relating to the first column (date, amount, etc.) There
are multiple identical UserIDs signifying different date of purchase and
amount. Example: UserID 10 is listed 3 times in the table.

Now my task is to create a table that only list the UserID once, along side
it list the remaining columns horizontally

example:
(current):
UserID Amount Date
10 1 9/10
10 2 11/10
10 3 12/10

I want it to show:


UserID.Amount.Date.Amount.Date.Amount.Date
10.......1.......9/10.....2.....11/10.....3.....12/10
So any tips regarding how to set it that way is appreciated, thank you guys
in advance!

[June7]

You want to pivot (aka transpose, crosstab) the data. Use the Access query wizard and build a crosstab query. The Date field will be used as column headings, grouping on the UserID, use First aggregate function to retrieve the Amount. Unless there are multiple records for each month then use the Sum. Are there multiple years in this table? If so, probably want to add filter criteria. The SQL would be like:
TRANSFORM First(Table1.[Amount]) AS FirstOfAmount
SELECT Table1.[UserID]
FROM Table1
GROUP BY Table1.[UserID]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

Or if that Date field is really text, like:
TRANSFORM First(Table1.[Amount]) AS FirstOfAmount
SELECT Table1.[UserID]
FROM Table1
GROUP BY Table1.[UserID]
PIVOT Table1.[Date];

[tomahs]

You want to pivot (aka transpose, crosstab) the data. Use the Access query wizard and build a crosstab query. The Date field will be used as column headings, grouping on the UserID, use First aggregate function to retrieve the Amount. Unless there are multiple records for each month then use the Sum. Are there multiple years in this table? If so, probably want to add filter criteria. The SQL would be like:
TRANSFORM First(Table1.[Amount]) AS FirstOfAmount
SELECT Table1.[UserID]
FROM Table1
GROUP BY Table1.[UserID]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

Or if that Date field is really text, like:
TRANSFORM First(Table1.[Amount]) AS FirstOfAmount
SELECT Table1.[UserID]
FROM Table1
GROUP BY Table1.[UserID]
PIVOT Table1.[Date];

Thanks for the quick help!

your method gave me the following chart:
UserID... 11/10... 12/10... 9/10
10............2.........3.........1

but the data i was given has tons of columns and the task requires me to keep the columns

so im not sure how to explain this but what i need is to group all the UserID that's '10' into one row with many repeating sets of columns, such as: "Date, amount, deptID, contractnumber, *note repeating* Date, amount, deptID, contract number"
then the next row would be UserID thats '11' and following that is the same set of columns

[June7]

So you want 12 sets of duplicate columns? Is the Date field a Date/Time datatype? Will each UserID have no more than one record per month? If yes to the above, then try DLookup:

SELECT Table1.UserID, "Jan" AS Jan, DLookUp("Amount","Table1","Month([Date])=1") AS JanAmt, DLookUp("deptID","Table1","Month([Date])=1") AS JanDeptID, DLookUp("contractnumber","Table1","Month([Date])=1") AS JanConNo, "Feb" AS Feb, DLookUp("Amount","Table1","Month([Date])=2") AS FebAmt, DLookUp("deptID","Table1","Month([Date])=2") AS FebDeptID, DLookUp("contractnumber","Table1","Month([Date])=2") AS FebConNo
FROM Table1
GROUP BY Table1.UserID;

Continue the repetition of expressions for each month. However, I think this will hit limit for length. So build several queries, maybe 4, one for each quarter, then join those queries. Any additional filter criteria, such as year, will have to be included in each DLookup.

Alternative is VBA. Check this thread: http://forums.aspfree.com/microsoft-...ry-322123.html

There is a limit of 255 (or is it 256, I forget) columns in a table or query.