If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting Question
Am running an Access 2K database in Access 2003. Have not converted it yet
and cannot for awhile until entire sales force has the new Laptops running Access 2003. My application deals with some 24,000+ part numbers that sometimes need to be sorted in our part number sequence in Reports. Our part numbers involve both numeric only number consisting of string lengths from 4 to 8 AND part number that begin with Alpha characters (1 or 2 or 3 Alphas) then numeric numbers with string lengths of 3 to 8. Some of these numbers have a hyphen in them that skew the actual string length for sorting based on length. We publish our price lists and put our products on the shelf based on the length of the part number (up to the hyphen), then the sort. I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number when the Report is printed. If someone could point me in correct direction, it would be much appreciated! TIA! -- Tom |
#2
|
|||
|
|||
Sorting Question
Tom
Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Tom" wrote in message ... Am running an Access 2K database in Access 2003. Have not converted it yet and cannot for awhile until entire sales force has the new Laptops running Access 2003. My application deals with some 24,000+ part numbers that sometimes need to be sorted in our part number sequence in Reports. Our part numbers involve both numeric only number consisting of string lengths from 4 to 8 AND part number that begin with Alpha characters (1 or 2 or 3 Alphas) then numeric numbers with string lengths of 3 to 8. Some of these numbers have a hyphen in them that skew the actual string length for sorting based on length. We publish our price lists and put our products on the shelf based on the length of the part number (up to the hyphen), then the sort. I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number when the Report is printed. If someone could point me in correct direction, it would be much appreciated! TIA! -- Tom |
#3
|
|||
|
|||
Sorting Question
Yes, we sort our numbers before our alpha numbers, but we do it based on
length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom |
#4
|
|||
|
|||
Sorting Question
I am looking for a way to sort our numeric numbers based on length and
then sort our Alpha/numeric parts up the hyphen in the part number i took the list of values you posted and dumped them into a single-field table, then created a query on that table, as SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1)) AS SortBy FROM Table13; the [x] field is the values, the SortBy field is a calculated field that returns the [x] value when there's no hyphen, and the [x] value up to the hyphen on all others. but what i'm seeing is that sorting by the SortBy field returns the same order as sorting by the [x] field, so there's no need to "get rid of" the hyphenated suffix before sorting. can you post an example of how you *want* the previously posted list to be sorted? hth "Tom" wrote in message news Yes, we sort our numbers before our alpha numbers, but we do it based on length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom |
#5
|
|||
|
|||
Sorting Question
Thanks for the reply.
The sort I want is numeric first based on the number of places, i.e. 2 digits in order, then 3 digits in order, then 4 digits in order, etc all the way to 6 digits in order, each group in numberical order, not computer order. Then, the sort should deal with strings that begin with Alphas. Sort on the Alpha letters first and then the numbers following the alpha, i.e. Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6 digits. What I am after is very close to the example I offered although I think I got some out of actual order. Some of the Alpha 5 digit have a hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit. Also, I was planning to put the sorting formula in a Report, probably in the Sorting and Grouping area. BTW, the Field I want to sort is named "Comp Part" if that helps! Thanks On Sun, 24 Jun 2007 15:42:22 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number i took the list of values you posted and dumped them into a single-field table, then created a query on that table, as SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1)) AS SortBy FROM Table13; the [x] field is the values, the SortBy field is a calculated field that returns the [x] value when there's no hyphen, and the [x] value up to the hyphen on all others. but what i'm seeing is that sorting by the SortBy field returns the same order as sorting by the [x] field, so there's no need to "get rid of" the hyphenated suffix before sorting. can you post an example of how you *want* the previously posted list to be sorted? hth "Tom" wrote in message news Yes, we sort our numbers before our alpha numbers, but we do it based on length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom -- Tom |
#6
|
|||
|
|||
Sorting Question
To better answer your question, the sort should be able to handle the
following: 1 9 10 99 100 999 1000 9999 10000 99999 100000 999999 Alpha1 Alpha9 Alpha10 Alpha99 Alpha100 Alpha999 Alpha1000 Alpha9999 Alpha10000 Alpha99999 Alpha100000 Alpha999999 And some of the Alphas have hyphen number, i.e. H156666-2 or H4433-2. Thanks On Sun, 24 Jun 2007 15:42:22 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number i took the list of values you posted and dumped them into a single-field table, then created a query on that table, as SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1)) AS SortBy FROM Table13; the [x] field is the values, the SortBy field is a calculated field that returns the [x] value when there's no hyphen, and the [x] value up to the hyphen on all others. but what i'm seeing is that sorting by the SortBy field returns the same order as sorting by the [x] field, so there's no need to "get rid of" the hyphenated suffix before sorting. can you post an example of how you *want* the previously posted list to be sorted? hth "Tom" wrote in message news Yes, we sort our numbers before our alpha numbers, but we do it based on length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom -- Tom |
#7
|
|||
|
|||
Sorting Question
I am looking for a way to sort our numeric numbers based on length and
then sort our Alpha/numeric parts up the hyphen in the part number okay, hopefully i understood your goal better this time. paste the following SQL statement into a new query's SQL pane, as SELECT IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA, IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0) AS SortB, Table13.[Comp Part] FROM Table13 ORDER BY IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))), IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0), Table13.[Comp Part]; remember to replace Table13 with the correct table name, of course. if the sort order fits your needs, then include the two calculated fields in the "real" query that the report is based on. in the *report*, sort the data by fields SortA, SortB, and [Comp Part], in that order. (note: don't bother sorting the fields at the query level at all, it's the report level sort that will "stick".) hth "Tom" wrote in message ... Thanks for the reply. The sort I want is numeric first based on the number of places, i.e. 2 digits in order, then 3 digits in order, then 4 digits in order, etc all the way to 6 digits in order, each group in numberical order, not computer order. Then, the sort should deal with strings that begin with Alphas. Sort on the Alpha letters first and then the numbers following the alpha, i.e. Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6 digits. What I am after is very close to the example I offered although I think I got some out of actual order. Some of the Alpha 5 digit have a hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit. Also, I was planning to put the sorting formula in a Report, probably in the Sorting and Grouping area. BTW, the Field I want to sort is named "Comp Part" if that helps! Thanks On Sun, 24 Jun 2007 15:42:22 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number i took the list of values you posted and dumped them into a single-field table, then created a query on that table, as SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1)) AS SortBy FROM Table13; the [x] field is the values, the SortBy field is a calculated field that returns the [x] value when there's no hyphen, and the [x] value up to the hyphen on all others. but what i'm seeing is that sorting by the SortBy field returns the same order as sorting by the [x] field, so there's no need to "get rid of" the hyphenated suffix before sorting. can you post an example of how you *want* the previously posted list to be sorted? hth "Tom" wrote in message news Yes, we sort our numbers before our alpha numbers, but we do it based on length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom -- Tom |
#8
|
|||
|
|||
Sorting Question
Tina,
When I trigger the Query, it's looking for a Parameter Value for x. My Test Table is Called "Part" (Changed the Table Name in the SQL Statement). Field "Comp Part" is formatted as Text if it matters. Thanks On Sun, 24 Jun 2007 18:13:53 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number okay, hopefully i understood your goal better this time. paste the following SQL statement into a new query's SQL pane, as SELECT IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA, IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0) AS SortB, Table13.[Comp Part] FROM Table13 ORDER BY IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))), IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0), Table13.[Comp Part]; remember to replace Table13 with the correct table name, of course. if the sort order fits your needs, then include the two calculated fields in the "real" query that the report is based on. in the *report*, sort the data by fields SortA, SortB, and [Comp Part], in that order. (note: don't bother sorting the fields at the query level at all, it's the report level sort that will "stick".) hth "Tom" wrote in message .. . Thanks for the reply. The sort I want is numeric first based on the number of places, i.e. 2 digits in order, then 3 digits in order, then 4 digits in order, etc all the way to 6 digits in order, each group in numberical order, not computer order. Then, the sort should deal with strings that begin with Alphas. Sort on the Alpha letters first and then the numbers following the alpha, i.e. Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6 digits. What I am after is very close to the example I offered although I think I got some out of actual order. Some of the Alpha 5 digit have a hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit. Also, I was planning to put the sorting formula in a Report, probably in the Sorting and Grouping area. BTW, the Field I want to sort is named "Comp Part" if that helps! Thanks On Sun, 24 Jun 2007 15:42:22 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number i took the list of values you posted and dumped them into a single-field table, then created a query on that table, as SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1)) AS SortBy FROM Table13; the [x] field is the values, the SortBy field is a calculated field that returns the [x] value when there's no hyphen, and the [x] value up to the hyphen on all others. but what i'm seeing is that sorting by the SortBy field returns the same order as sorting by the [x] field, so there's no need to "get rid of" the hyphenated suffix before sorting. can you post an example of how you *want* the previously posted list to be sorted? hth "Tom" wrote in message news Yes, we sort our numbers before our alpha numbers, but we do it based on length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom -- Tom -- Tom |
#9
|
|||
|
|||
Sorting Question
yes, looks like i missed replacing two of the [x] field names in the posted
SQL. just go through the SQL statement and replace those two x's with the correct field name, hon. hth "Tom" wrote in message ... Tina, When I trigger the Query, it's looking for a Parameter Value for x. My Test Table is Called "Part" (Changed the Table Name in the SQL Statement). Field "Comp Part" is formatted as Text if it matters. Thanks On Sun, 24 Jun 2007 18:13:53 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number okay, hopefully i understood your goal better this time. paste the following SQL statement into a new query's SQL pane, as SELECT IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA, IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0) AS SortB, Table13.[Comp Part] FROM Table13 ORDER BY IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))), IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0), Table13.[Comp Part]; remember to replace Table13 with the correct table name, of course. if the sort order fits your needs, then include the two calculated fields in the "real" query that the report is based on. in the *report*, sort the data by fields SortA, SortB, and [Comp Part], in that order. (note: don't bother sorting the fields at the query level at all, it's the report level sort that will "stick".) hth "Tom" wrote in message .. . Thanks for the reply. The sort I want is numeric first based on the number of places, i.e. 2 digits in order, then 3 digits in order, then 4 digits in order, etc all the way to 6 digits in order, each group in numberical order, not computer order. Then, the sort should deal with strings that begin with Alphas. Sort on the Alpha letters first and then the numbers following the alpha, i.e. Alpha 2-digit, then Alpha 3 digit, then Alpha 4 digit, etc up to Alpha 6 digits. What I am after is very close to the example I offered although I think I got some out of actual order. Some of the Alpha 5 digit have a hyphen number, i.e. H15664-2 that need to sort with the Alpha 5 digit. Also, I was planning to put the sorting formula in a Report, probably in the Sorting and Grouping area. BTW, the Field I want to sort is named "Comp Part" if that helps! Thanks On Sun, 24 Jun 2007 15:42:22 GMT, "tina" wrote: I am looking for a way to sort our numeric numbers based on length and then sort our Alpha/numeric parts up the hyphen in the part number i took the list of values you posted and dumped them into a single-field table, then created a query on that table, as SELECT Table13.x, IIf(InStr(1,[x],"-")=0,[x],Left([x],InStr(1,[x],"-")-1)) AS SortBy FROM Table13; the [x] field is the values, the SortBy field is a calculated field that returns the [x] value when there's no hyphen, and the [x] value up to the hyphen on all others. but what i'm seeing is that sorting by the SortBy field returns the same order as sorting by the [x] field, so there's no need to "get rid of" the hyphenated suffix before sorting. can you post an example of how you *want* the previously posted list to be sorted? hth "Tom" wrote in message news Yes, we sort our numbers before our alpha numbers, but we do it based on length within those groups, i.e. 1234 12345 123456 ABS12345 ABS123456 ATD123 ATD123C ATD123M ATD1234 ATD1234C ATD1234M BC1234 BC12345 BC123456 BH1234 BH12345 BH123456 CMA12345 CMA123456 H12 H12-2 H123 H123K H123W H123-2 H1234 H1234-2 H1234K H1234W H12345 H12345-2 H12345K H12345W MC1234 MC12345 MC123456 PGD123 PGD123C PGD123M PGD1234C PGD1234M SC12345 SC123456 SGD123 SGD123C SGD123M SGD1234 SGD1234C SGD1234M WC1234 WC12345 WC123456 There are more Alpha prefixes, but if I can sort this sample, the rest will fall into place as well. Thanks! On Sun, 24 Jun 2007 06:29:54 -0700, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: Tom Without an example to help "see" what you're doing, this is only a guess... It sounds like you want to sort your "part numbers" in alphabetic order. If any of the part numbers start with numbers, those should sort before the alpha-beginning numbers. -- Tom -- Tom -- Tom |
#10
|
|||
|
|||
Sorting Question
Making progress. Now I get a "Data Mismatch in criterion expression" error
message. On Sun, 24 Jun 2007 19:30:52 GMT, "tina" wrote: SELECT IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[Comp Part],Left([Comp Part],InStr(1,[x],"-")-1)))) AS SortA, IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0) AS SortB, Table13.[Comp Part] FROM Table13 ORDER BY IIf(Asc(Left([Comp Part],1))58,0,Len(IIf(InStr(1,[Comp Part],"-")=0,[x],Left([Comp Part],InStr(1,[Comp Part],"-")-1)))), IIf(Asc(Left([Comp Part],1))65,CLng([Comp Part]),0), Table13.[Comp Part]; -- Tom |
Thread Tools | |
Display Modes | |
|
|