Re: Multiple Locations

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Duane Hookom" wrote:

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



.

Relevant Pages

  • Re: PL/I, COBOL, Advantages, Equivalence, et al
    ... declarations to PL/I ... DCL 1 listsctl ... bin fixed, n bin fixed, ... At end of my program's reading of lists, I show that the data is contained ...
    (comp.lang.pl1)
  • Re: Enumerating Partitions
    ... partition_generator is how to partition ... bin must contain at least one item (used in my Collatz Conjecture ... Lists, a=, may be indexed from right to left using ...
    (sci.math)
  • Re: control-center??
    ... When faced with that sort of question, do an "rpm -ql" query on the ... they'll usually have bin somewhere in the filepath). ... e.g. rpm -ql control-center | grep bin ... read messages from the public lists. ...
    (Fedora)
  • Re: trictionary?
    ... Regardless of how it smells, it's tight, small, and elegant. ... Lists are so lightweight that there really isn't a better way to solve this problem. ...
    (comp.lang.python)
  • Time 100: Time Magazines list of the worlds most influential people
    ... I don't thnink one should make too much out of such lists, but Time Magazine's fifth annual list of the world's most influential people only list two royals, Sheik Mohammed bin Rashid al-Maktoum and Prince Alwaleed bin Talal bin Abdulaziz al-Saud. ... (Time Magazine 12 May 2008) ...
    (alt.talk.royalty)