Multiple Locations

[Guest]

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,

 

[Guest]

Wrong thing to do to a database.

If you want that kind of display then use a crosstab query.

 

[Duane Hookom]

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]);

 

[Guest]

Thanks for your reply Duane,

I've tried a couple of times off and on (more off than on) to get the query
to run but I keep getting the following message "Syntax error (missing
operator) in query expression 'First (Bin Location Table.Bin)'. The table
which lists the part numbers and bins is called 'Bin Location Table'. Any
suggestions?
--
Joe

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

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,

 

[John Vinson]

Thanks for your reply Duane,

I've tried a couple of times off and on (more off than on) to get the query
to run but I keep getting the following message "Syntax error (missing
operator) in query expression 'First (Bin Location Table.Bin)'. The table
which lists the part numbers and bins is called 'Bin Location Table'. Any
suggestions?

If you (unwisely, in my opinion) use blanks or other special
characters in table or field names, you MUST use square brackets to
delimit the name. Otherwise it's seeing Bin as one word, Location as
another, Table.Bin as a third.

Try changing all instances of Bin Location Table in your query to [Bin
Location Table].

John W. Vinson[MVP]