Help! Transposing Data via Cross Tab Query?

[William McKinley]

Hi All,



I have a requirement to transpose my warehouse storage locations into logical groupings to print new labels. I was doing this by hand in Excel, but unfortunately it's taking way to long and I have to do 19,000 by today. Help! Here's what I'm trying to do:

I have a list of storage locations that looks like the below. I need to come up with some kind of logic in a query to reorganize the data so that the printer can efficiently produce the labels. Basically the storage location is broken into 3 sections, separated by a . The first section is the Aisle(150,151). The second section is the Bay (001,002). The third and final section is the Location (100,200,300). The data needs to be structured so that every Each Aisle and Bay is split to a new row of the query results, with the Location numbers ascending from smallest to largest.

Can anyone think of a way to do this? I'm stumped right now and time is running out

Need it to look like this:

150.001.100.	150.001.200.	150.001.300.
150.002.100.	150.002.200.	150.002.300.
151.001.100.	151.001.200.	151.001.300.
151.002.100.	151.002.200.	151.002.300.

Old Data Structure:

150.001.100.
150.001.200.
150.001.300.
150.002.100.
150.002.200.
150.002.300.
151.001.100.
151.001.200.
151.001.300.
151.002.100.
151.002.200.
151.002.300.

[rpeare]

Assuming your table name is tblLocationCode and your field name is LocationCode

Use this query:

TRANSFORM Max(tblLocationCode.LocationCode) AS MaxOfLocationCode
SELECT Left([locationcode],3) AS Asile, Mid([locationcode],5,3) AS Bay
FROM tblLocationCode
GROUP BY Left([locationcode],3), Mid([locationcode],5,3)
PIVOT Right([locationcode],3);

[William McKinley]

The query is structured correctly, however the output is not quite there. Instead of showing all of the storage locatiosn for that Aisle and Bay, it only shows the Max. Any ideas? Here's what it looks like:

150	001	150.001.300.
150	002	150.002.300.
151	001	151.001.300.
151	002	151.002.300.

[William McKinley]

Never mind, I figured it out. the Right([locationcode],3) needed to be switched to Right([locationcode],4).

Thanks so much for the help!