Unique sort with location ID

[geocan2006]

I need to solve below puzzle, example Unique sort with location ID
I hope lots of folks can benefit from this query
Thanks


Table1

Model	Location	Qtty
RedHat	Bin1	5
RedHat	Bin7	2
RedHat	Bin8	7
BlackHat	Bin2	2
BlackHat	Bin1	4
BlackHat	Bin4	6
YellowHat	Bin5	6

Query1

Model	Look1	Look2	Look3
RedHat	Bin1 =5	Bin7 =2	Bin8 =7
BlackHat	Bin2 =2	Bin1 =4	Bin4 =6
YellowHat	Bin5 =6

[ranman256]

in the table that holds the Locations, add a sort column
or
sort in the query with: Val([location])
(using the number in the location)

[June7]

If Location contains text data that begins with alpha characters, Val([Location]) will not return the numbers.

You need sequence numbering for each Model group. This sequence number field will be the ColumnHeader for a CROSSTAB query.

Why bother including the word "Bin" in the data? Is there ever anything else but "Bin"? Following suggestion uses table that does not include the word "Bin" in the table data. And Location field is a number type.

TRANSFORM First("Bin" & [Location] & " = " & [Qtty]) AS Data
SELECT Table1.Model
FROM Table1
GROUP BY Table1.Model
PIVOT "Look" & DCount("*","Table1","Model='" & [Model] & "' AND Location<" & [Location])+1;

This version deals with Location as text field with "Bin" in the data:

TRANSFORM First([Location] & " = " & [Qtty]) AS Data
SELECT Table1.Model
FROM [Table1]
GROUP BY Table1.Model
PIVOT "Look" & DCount("*","[Table1]","Model='" & [Model] & "' AND Val(Replace([Location], 'Bin', ''))<" & Val(Replace([Location], "Bin", "")))+1;

If the number of possible bins never exceeds 9 then the Val() and Replace() functions are not necessary. Also, if the sequence count exceeds 9 the columns will not sort in proper order with the "Look" text prefix. Sorting on text is less efficient and performance might be noticeably slower with large dataset.

[geocan2006]

If Location contains text data that begins with alpha characters, Val([Location]) will not return the numbers.

You need sequence numbering for each Model group. This sequence number field will be the ColumnHeader for a CROSSTAB query.

Why bother including the word "Bin" in the data? Is there ever anything else but "Bin"? Following suggestion uses table that does not include the word "Bin" in the table data. And Location field is a number type.

TRANSFORM First("Bin" & [Location] & " = " & [Qtty]) AS Data
SELECT Table1.Model
FROM Table1
GROUP BY Table1.Model
PIVOT "Look" & DCount("*","Table1","Model='" & [Model] & "' AND Location<" & [Location])+1;

This version deals with Location as text field with "Bin" in the data:

TRANSFORM First([Location] & " = " & [Qtty]) AS Data
SELECT Table1.Model
FROM [Table1]
GROUP BY Table1.Model
PIVOT "Look" & DCount("*","[Table1]","Model='" & [Model] & "' AND Val(Replace([Location], 'Bin', ''))<" & Val(Replace([Location], "Bin", "")))+1;

If the number of possible bins never exceeds 9 then the Val() and Replace() functions are not necessary. Also, if the sequence count exceeds 9 the columns will not sort in proper order with the "Look" text prefix. Sorting on text is less efficient and performance might be noticeably slower with large dataset.

The 2nd option worked like a charm,
Best regards!!!