Distribute row information to columns

[HowardlyDog]

Hello all, I am new to Access and am looking for some help!

I have a Query with many part numbers in column A and their inventory location(s) listed in column B. However, some part numbers are listed 2 or 3 times because the item is located in multiple inventory locations. How can I take these items and put list their inventory locations in multiple columns across one row?

What I have:

ITEM# Quantity INVENTORYLOCATION
A1 5 Warehouse 1
A2 3 Warehouse 1
A2 6 Warehouse 2
A3 2 Warehouse 1

What I want:

ITEM# INVENTORYLOCATION Quantity 1 INVENTORYLOCATION 2 Quantity 2
A1 Warehouse 1 5
A2 Warehouse 1 3 Warehouse 2 6
A3 Warehouse 1 2

Thank you for any assistance!!!!



C

[Bob Fitz]

Not quite what you asked for:

TRANSFORM Sum(YourTableName.Quantity) AS SumOfQuantity
SELECT YourTableName.ITEM
FROM YourTableName
GROUP BY YourTableName.ITEM
PIVOT YourTableName.INVENTORYLOCATION;

You would need to change YourTableName

[HowardlyDog]

Would I use this code in SQL view?

[Bob Fitz]

Yes. Copy/Paste and change YourTableName