Excel/Access Cross Tab

[dstogner]

I have data listed in 2 columns. Column # 1 contains an account number and the same number can appear multiple times. In column 2 contains an inventory #. So it appears as follows:

Acct Number Item Number
123456 S4567
123456 G8954
456789 F8965
456789 L4565
456789 W678
456789 J8965

What I need is to convert the information so that the account number show up once and the item numbers are then in rows next to the account number as follows:

Acct Number Item #1 Item #2 Item #3 Item #4
123456 S4567 G8954
456789 F8954 L4565 W678 J8965

Any suggestions would be GREATLY appreciated. I have tried in excel and the cross tab wizard in access and can't seem to get the correct result.

THANK YOU!!!!!!

 

[Joe4]

Here is an Access solution:

If you add an Item Counter field, you can do this, i.e.

Acct Number   Item Counter   Item Number
123456               1            S4567
123456               2            G8954
456789               1            F8965
456789               2            L4565
456789               3            W678
456789               4            J8965

Then, you can use a Cross-Tab Query, choosing Acct Number as your Row Headers, Item Counter as your Column Headers, and First of Item Number as the body.

The SQL code would look something like this (assuming the name of the table is "MyTable"):

TRANSFORM First(MyTable.[Item Number]) AS [FirstOfItem Number]
SELECT MyTable.[Acct Number]
FROM MyTable
GROUP BY MyTable.[Acct Number]
PIVOT MyTable.[Item Counter];

Note, you can create the Item Number column pretty easily, either in Excel or using DAO/ADO recordset looping in Access VBA.

 

[dstogner]

That worked perfectly - THANK YOU SO MUCH!!!