Re: Multiple Locations

Tech-Archive recommends: Fix windows errors by optimizing your registry

Try this SQL which assigns the bin sequence using DCount():

TRANSFORM First(tblWhichListsPartNumbers.BIN) AS FirstOfBIN
SELECT tblWhichListsPartNumbers.[Part#]
FROM tblWhichListsPartNumbers
GROUP BY tblWhichListsPartNumbers.[Part#]
ORDER BY tblWhichListsPartNumbers.[Part#]
PIVOT "Bin" & DCount("BIN","tblWhichListsPartNumbers","[Part#]=""" & [Part#]
& """ AND BIN<=" & [Bin]);

--
Duane Hookom
MS Access MVP

"A Boy Named Joe" <ABoyNamedJoe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:63C2F4A0-C5FC-44F7-ACEB-C40D51227561@xxxxxxxxxxxxxxxx
Good morning MS Community,

I have a table which lists part numbers in the first field and the bin
locations in the 2nd field. Since a part may have more than one bin
locations (up to 5 or 6) , the table looks like this:

PART# BIN
A1 123
A1 456
A2 789
B1
etc....

I'd like to get the information organized into a table like this:

PART# BIN#1 BIN#2 BIN#3....
A1 123 456
A2 789

So there is only one instance of the part number showing all possible bin
locations. Any suggestions?

Thanks,

--
Joe


.