Alphabetize Multiple Fields

I have table structure below. All spaces represent different fields
so there are 7 below.

Bill ES AR IM LV BB DE

I want the output to alphabetize fields 2-7 (after Bill). So output
would be

Bill AR BB DE ES IM LV

I either want the output like that or to figure out a way to rearrange
the initial table with an update query that would accomplish same
thing. Then my output would automatically be in alphabetical order.

I cannot really change table structure to:
Bill AR
Bill IM
Bill LV

Because there is more data in each table then I am showing above.

Re: Alphabetize Multiple Fields

On Nov 2, 3:10 pm, "ve...@aol.com" wrote:
> I have table structure below. All spaces represent different fields
> so there are 7 below.
>
> Bill ES AR IM LV BB DE
>
> I want the output to alphabetize fields 2-7 (after Bill). So output
> would be
>
> Bill AR BB DE ES IM LV
>
> I either want the output like that or to figure out a way to rearrange
> the initial table with an update query that would accomplish same
> thing. Then my output would automatically be in alphabetical order.
>
> I cannot really change table structure to:
> Bill AR
> Bill IM
> Bill LV
>
> Because there is more data in each table then I am showing above.

What is your REAL objective? What is your data representing? It
looks to be a case of non-normalization. Why not just create a query
to slect the fields in the order you wanrt them? Are AR BB DE ES EM
and LV the actual field names or the Values in the fields for the
record for Bill?

Re: Alphabetize Multiple Fields

On Nov 2, 3:48 pm, DavidB wrote:
> On Nov 2, 3:10 pm, "ve...@aol.com" wrote:
>
>
>
>
>
> > I have table structure below. All spaces represent different fields
> > so there are 7 below.
>
> > Bill ES AR IM LV BB DE
>
> > I want the output to alphabetize fields 2-7 (after Bill). So output
> > would be
>
> > Bill AR BB DE ES IM LV
>
> > I either want the output like that or to figure out a way to rearrange
> > the initial table with an update query that would accomplish same
> > thing. Then my output would automatically be in alphabetical order.
>
> > I cannot really change table structure to:
> > Bill AR
> > Bill IM
> > Bill LV
>
> > Because there is more data in each table then I am showing above.
>
> What is your REAL objective? What is your data representing? It
> looks to be a case of non-normalization. Why not just create a query
> to slect the fields in the order you wanrt them? Are AR BB DE ES EM
> and LV the actual field names or the Values in the fields for the
> record for Bill?- Hide quoted text -
>
> - Show quoted text -

They are data in the fields, not the names. That actually is the real
objective, to get them in alphabetical order for the output. The data
represents language codes and will go into a printed directory. I
needed the languages to be listed in order, however they do not come
in order on the source file. I guess I'm not sure how to create a
query to select the languages in the order I want them. There are
100+ language codes and they could be in any order in the 6 different
language fields.

Re: Alphabetize Multiple Fields

veaux@aol.com wrote:

> On Nov 2, 3:48 pm, DavidB wrote:
>
>>On Nov 2, 3:10 pm, "ve...@aol.com" wrote:
>>
>>
>>
>>
>>
>>
>>>I have table structure below. All spaces represent different fields
>>>so there are 7 below.
>>
>>>Bill ES AR IM LV BB DE
>>
>>>I want the output to alphabetize fields 2-7 (after Bill). So output
>>>would be
>>
>>>Bill AR BB DE ES IM LV
>>
>>>I either want the output like that or to figure out a way to rearrange
>>>the initial table with an update query that would accomplish same
>>>thing. Then my output would automatically be in alphabetical order.
>>
>>>I cannot really change table structure to:
>>>Bill AR
>>>Bill IM
>>>Bill LV
>>
>>>Because there is more data in each table then I am showing above.
>>
>>What is your REAL objective? What is your data representing? It
>>looks to be a case of non-normalization. Why not just create a query
>>to slect the fields in the order you wanrt them? Are AR BB DE ES EM
>>and LV the actual field names or the Values in the fields for the
>>record for Bill?- Hide quoted text -
>>
>>- Show quoted text -
>
>
> They are data in the fields, not the names. That actually is the real
> objective, to get them in alphabetical order for the output. The data
> represents language codes and will go into a printed directory. I
> needed the languages to be listed in order, however they do not come
> in order on the source file. I guess I'm not sure how to create a
> query to select the languages in the order I want them. There are
> 100+ language codes and they could be in any order in the 6 different
> language fields.
>

I don't know if this will help. You state
I cannot really change table structure to:
Bill AR
Bill IM
Bill LV

Well, you can in a manner of speaking. I created a table (Table1) with
the fields Nm and L1...L6. I filled a couple of records and created
this query (Query1).

SELECT Nm, L1 AS Language
FROM Table1
UNION ALL
SELECT Nm, L2 AS Language
FROM Table1
UNION ALL
SELECT Nm, L3 AS Language
FROM Table1
UNION ALL
SELECT Nm, L4 AS Language
FROM Table1
UNION ALL
SELECT Nm, L5 AS Language
FROM Table1
UNION ALL SELECT Nm, L6 AS Language
FROM Table1;

This creates a column Language. I could have done everything in the
query above but I broke it out into two. The second query is

SELECT Nm, Language
FROM Query1
ORDER BY Nm, Language;

So this sorts the records by name and by language.

I suppose you could filter so that Language is not null. And I suppose
if you needed 1 line you could call/create a pivot/crosstab query.


That's all there is.
http://www.youtube.com/watch?v=lTpFUT-lxls

Re: Alphabetize Multiple Fields

If combining the six (or fewer) values into one text string is satisfactory
for your output, you could write, or someone could write for you, a function
to which the six fields are input arguments, that sorts them, and then
returns a string containing the sorted values. You could execute the
function from within the Query that you use as Record Source for your Report
or Form, or within the Report or Form itself. With only six values, most
any simple sort method will suffice. (Perhaps surprisingly, I don't have an
example to give you. Perhaps someone else does, or has the time/energy to
create a simple example.)

Larry Linson
Microsoft Access MVP


wrote in message
news:1194038024.353969.223050@o38g2000hse.googlegroups.com.. .
> On Nov 2, 3:48 pm, DavidB wrote:
>> On Nov 2, 3:10 pm, "ve...@aol.com" wrote:
>>
>>
>>
>>
>>
>> > I have table structure below. All spaces represent different fields
>> > so there are 7 below.
>>
>> > Bill ES AR IM LV BB DE
>>
>> > I want the output to alphabetize fields 2-7 (after Bill). So output
>> > would be
>>
>> > Bill AR BB DE ES IM LV
>>
>> > I either want the output like that or to figure out a way to rearrange
>> > the initial table with an update query that would accomplish same
>> > thing. Then my output would automatically be in alphabetical order.
>>
>> > I cannot really change table structure to:
>> > Bill AR
>> > Bill IM
>> > Bill LV
>>
>> > Because there is more data in each table then I am showing above.
>>
>> What is your REAL objective? What is your data representing? It
>> looks to be a case of non-normalization. Why not just create a query
>> to slect the fields in the order you wanrt them? Are AR BB DE ES EM
>> and LV the actual field names or the Values in the fields for the
>> record for Bill?- Hide quoted text -
>>
>> - Show quoted text -
>
> They are data in the fields, not the names. That actually is the real
> objective, to get them in alphabetical order for the output. The data
> represents language codes and will go into a printed directory. I
> needed the languages to be listed in order, however they do not come
> in order on the source file. I guess I'm not sure how to create a
> query to select the languages in the order I want them. There are
> 100+ language codes and they could be in any order in the 6 different
> language fields.
>

Re: Alphabetize Multiple Fields

wrote in message
news:1194038024.353969.223050@o38g2000hse.googlegroups.com.. .
> On Nov 2, 3:48 pm, DavidB wrote:
>> On Nov 2, 3:10 pm, "ve...@aol.com" wrote:
>>
>>
>>
>>
>>
>> > I have table structure below. All spaces represent different fields
>> > so there are 7 below.
>>
>> > Bill ES AR IM LV BB DE
>>
>> > I want the output to alphabetize fields 2-7 (after Bill). So output
>> > would be
>>
>> > Bill AR BB DE ES IM LV
>>
>> > I either want the output like that or to figure out a way to rearrange
>> > the initial table with an update query that would accomplish same
>> > thing. Then my output would automatically be in alphabetical order.
>>
>> > I cannot really change table structure to:
>> > Bill AR
>> > Bill IM
>> > Bill LV
>>
>> > Because there is more data in each table then I am showing above.
>>
>> What is your REAL objective? What is your data representing? It
>> looks to be a case of non-normalization. Why not just create a query
>> to slect the fields in the order you wanrt them? Are AR BB DE ES EM
>> and LV the actual field names or the Values in the fields for the
>> record for Bill?- Hide quoted text -
>>
>> - Show quoted text -
>
> They are data in the fields, not the names. That actually is the real
> objective, to get them in alphabetical order for the output. The data
> represents language codes and will go into a printed directory. I
> needed the languages to be listed in order, however they do not come
> in order on the source file. I guess I'm not sure how to create a
> query to select the languages in the order I want them. There are
> 100+ language codes and they could be in any order in the 6 different
> language fields.

Here's a small sort function you can use to implement something like Larry's
suggestion:

''' Begin Code'''
Public Sub SortArray(Item$())
Dim lastItem&, SmallestRow&, row&, i&, tmp$

lastItem = UBound(Item)
For row = 1 To lastItem
SmallestRow = row
For i = row + 1 To lastItem
If Item(i) < Item(SmallestRow) Then
SmallestRow = i
End If
Next
'
If SmallestRow > row Then
tmp = Item(row)
Item(row) = Item(SmallestRow)
Item(SmallestRow) = tmp
End If
Next
End Sub
'''End Code '''

That's known as an Exchange sort, which is very slick on small amounts of
data (such as you have).

It takes an array for input, so I suggest you use a recordset to loop over
the records, and generate the array using the Split function:

ary = Split(rs!FieldName)

Then call SortArray (which I've altered to sort from 1, not 0 as would be
natural. This misses out "Bill") :

SortArray ary

Then use the Join function to get them back into string form:

ss = Join(ary)

and finally update the record.