Can't figure it out query

[GLam]

Hi, I have a data table like this:

ItemID Location ItemQty
abc A12B 500
abc B13C 20
abc P11D 50
ead B20C 100
ead B21E 3
fcd A12B 80
dec B22E 100
dec P22E 5

How could I make a query and have the result set like this

ItemID Location1 Loc1Qty Location2 Loc2Qty Location3
Loc3QTY
abc A12B 500 B13C 20
P11D 50
ead B20C 100 B21E 3
fcd A12B 80
dec B22E 100 P22E 5

The table has 5000+ records.
Appreciate your help or pointer.
Thank you.
GL

 

[dcichelli]

GL -

Your issues stem from your database design. It is all wrong!

You need 3 tables:

Product
Location
LocationProduct

In your product table you will have 3+ fields:

ProductID - Autonumber
ProductName - Text (abc, fed, beg, etc...)
ProductDescription - Memo (hammer, nails, drill, etc.)

In your location table you will have 3+ fields

LocationID - Autonumber
LocationName - Text (A12B, B13C, P11D, etc,)
LocationDescription - Memo (back of store, front of store, isle 13,
etc.)

In your Locationproduct table you will have 3+ fields

LocprodID - Autonumber
Product - Lookup field to productID from the product table
Location - Lookup field to LocationID from location table
Quantity - number - the quantity of the product per location


Start by cleaning up your tables....