Possible? Unusual field order sorting. (1 Viewer)

[russi]

Morning!

I need to re-order an existing table with 170 fields (not my design).

The fields have to be alphabetized. They all have data.

Is there an easy way or a tool or code or ... to help me do this.
(After this table I have to do the same for others.)

The reason for all this:
Once alphabetized and saved, I then need to export them as
text and they need to be in that same order.

Russ

 

[thouston]

I've had to do similar and the way I did it was very quick & dirty - not elegant at all.

Export the table to Excel. Copy & special paste (transpose) the whole lot. Sort by what is now the first column (which now has your field names in it). Re-transpose the sorted data. Re-import into Access.

As I said, dirty and inelegant, but I was in a hurry and it worked.

 

[glynch]

It might be easier to run a make-table query and within the query reorder the fields alphbetically. Then rename the new table to the name it needs to be. You could either delete the old or rename it also.

 

[russi]

Thank you BOTH for your ideas.

Glynch, is there a way to easily re-order the fields alphabetically in a query, since one of the tables involved has approx. 170 fields?

If not, Thouston, your way sounds good.

Russ

 

[glynch]

No I don't, not easily anyway.

 

[DALeffler]

Heh! I saw this question and knew I be spending the next few hours figuring out how to do what russi wants. What fun! Learned a lot, too! Thanks for the question!

Sub sbTblFldSrt()
'create a query (NewAlphaQry) on a table that shows the table fields in
'alphabetical order according to the field names of the table.

Dim dbs As Database
Dim tdfNew As TableDef
Dim qdfNew As QueryDef
Dim qdfStrng As String
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim fld As Field

Set dbs = CurrentDb
'create temp Table
Set tdfNew = dbs.CreateTableDef("TmpFldLstTbl")
'create temp field
Set fld = tdfNew.CreateField("FldNme", dbText)
'append field to temp table def
tdfNew.Fields.Append fld
'add new table (and field) def to database
dbs.TableDefs.Append tdfNew
'release field variable - gonna use it for something else soon
Set fld = Nothing
'rst1 for table that needs fields ordered alphabetically
Set rst1 = dbs.OpenRecordset("MyTblToAlpha", dbOpenSnapshot)
'rst2 is temp table created above
Set rst2 = dbs.OpenRecordset("TmpFldLstTbl", dbOpenDynaset)
'add the names of the fields to be ordered into the temp table
For Each fld In rst1.Fields
    rst2.AddNew
    rst2!FldNme = fld.Name
    rst2.Update
Next fld
'release rst2
Set rst2 = Nothing
'set rst2 to an ordered field list of the fields in rst1
Set rst2 = dbs.OpenRecordset( _
    "SELECT * FROM [TmpFldLstTbl] " & _
    "ORDER BY [FldNme]", dbOpenSnapshot)

qdfStrng = ""
'set up SQL string
While Not rst2.EOF
    qdfStrng = qdfStrng & "[MyTblToAlpha]." & "[" & rst2!FldNme & "]" & ", "
    rst2.MoveNext
Wend
'get rid of the ", " (comma & space) at the end
qdfStrng = Mid(qdfStrng, 1, Len(qdfStrng) - 2)
'create the new query
Set qdfNew = dbs.CreateQueryDef("NewAlphaQry", _
            "SELECT " & qdfStrng & " FROM [MyTblToAlpha]")

'export the new query - check your arguements!!!
DoCmd.TransferSpreadsheet acExport, 8, "NewAlphaQry", _
        "C:\WINNT\Profiles\Administrator\Personal\book1.xls", True
'get rid of the temp table
Set rst2 = Nothing
dbs.TableDefs.Delete "TmpFldLstTbl"
'get rid of the new query
dbs.QueryDefs.Delete "NewAlphaQry"
'get the heck outta dodge!
End Sub

Since this is the first time I ever got this far into tabledef and qrydef objects, try the code on a copy of your db - but it works for me on AC97...

One thing to watch: the code may run without an error and you'll see the Excel file except not in the order you'd expect. The code is going by field name, not by the field caption.

HTH (and hope more senior members are going to point out inefficiencies...(at least)...)

Doug.

 

[russi]

Doug,

Thanks so much!

I was away for a fwew days but just this and will try it.

Again, this process is not my 'doing'.

The database I created is being replaced by a federal gov't prg the Feds want every state to use.
And so I had to create identical setup tables to theirs, then convert my data best as I could into the new tables. Then I have to export as text delimited, then upload in ALPHABETICAL order ( a requirement first given to me after doing the aforementioned) into their Oracle software at a server in another state.

Russ

 

[The_Doc_Man]

Actually, they STILL sold you a bill of goods here. ORACLE is perfectly capable of sorting stuff on its own.

But besides that, you could have done a query that did a SELECT * FROM tblGOBBLEDEGOOK ORDER BY fldTEXT1, fldTEXT2, etc. etc.

Then just export the query.

And if your problem was that uppercase and lowercase were in the way, try

... ORDER BY UCASE$(fldTEXT1), UCASE$(fldTEXT2).....


Now, if you had a really ugly sort order that WASN'T alphabetical, there are ways to do that. Like, say, the implied font was Greek or Cyrillic and you had to sort in some bizarre order. THAT would have required some programming, maybe.

 

[DALeffler]

Ok, now I'm a bit confused (very typical...)

russi, did you need the data in the table fields re-ordered (if so, ignore my post above because that code doesn't come close to doing that) or did you need show the field objects in a table re-ordered by the field name (which is what the code in the post above does)?



I should have asked this first rather than assuming, but, either way, still a good lesson for me. Hope my confusion doesn't confuse you...

Creating a query to re-order the data in every field would go something like this (like The_Doc_Man says):

Sub sbTblFldDataSrt()
'create a query (NewAlphaQry) on a table that shows every 
'table fields' data ordered alph-numerically.

Dim dbs As Database
Dim rst1 As Recordset
Dim fld  As Field
Dim qdfNew As QueryDef
Dim qdfStrng As String

Set dbs = CurrentDb
'rst1 for table that needs field data ordered alpha-num
Set rst1 = dbs.OpenRecordset("MyTblToAlpha", dbOpenSnapshot)

qdfStrng = ""

For Each fld In rst1.Fields
    qdfStrng = qdfStrng & fld.name  & ", "
Next fld

'get rid of the ", " (comma & space) at the end
qdfStrng = Mid(qdfStrng, 1, Len(qdfStrng) - 2)

Set qdfNew = dbs.CreateQueryDef("NewAlphaQry", _
            "SELECT  * FROM [MyTblToAlpha] ORDER BY " & qdfString)

'export the new query - check your arguements!!!
DoCmd.TransferSpreadsheet acExport, 8, "NewAlphaQry", _
        "C:\WINNT\Profiles\Administrator\Personal\book1.xls", True

'get rid of the new query
dbs.QueryDefs.Delete "NewAlphaQry"

End Sub

Doug.

 

[thouston]

Given that you need to get the data into text delimited ready for uploading into an Oracle database, my "dirty" way of going via Excel may well be most efficient after all, as you can go straight from the Excel file to text delimited without having to go back into Access at all.

The thing that made this approach a bit messy is the re-import back into Access (see thread "Problems importing Excel Files" by Mike Gurman) - if you bypass this step you won't have to worry about it.

 

[russi]

Thanks again, you two.

As often happens, I have been putting out 'fires' so I did not get to even try ideas yet.

Yes, the issue is to physically re-sort the fields and their respective data in the tables.

Russ

 

[The_Doc_Man]

You may be trying to do something you don't need to do.

Ordering the fields for ORACLE import is legit if you are planning on doing one of the ORACLE "fast import" operations. I actually understand that problem. Had to do it once on another system that was using ShareBase databases, not Access. But there are several ways to skin that cat.

That export step can be done by exporting a query with explicit sort ordering. Or, you can do a normal import into ORACLE with no indexes, then go back and define the indexes you want. ORACLE actually recommends this if you have more than just a couple of indexes anyway. Only one of them would have been in the right order. The others? Forget about it. What we finally decided to do was make ORACLE work FOR us, not against us.

But ordering the fields inside an Access table is moot. The first time you add or change any field that has a declared index, particularly if it is the prime key, the table's internal order can be thrown into question.

Pat could check me on this, but I believe I'm right.

Internally to Access, there is no such thing as 'natural' table order except by accident. You view tables according to their keys. You view queries in datasheet mode according to their sort criteria. But internal to Access, it's all lists of record-pointers anyway. And you store records pretty much according to the order in which they were added. I.e. implied chronological order regardless of what the keys really are. Compressing the database doesn't change the relative order of stored records. Only the actual internal db-relative address at which the record starts.

In table datasheet view, all you are doing is calling out the pointers to each record in the table's implied recordset and displaying them in the order (and with the format) in which that datasheet view was last saved after modification. But not a single actual record gets moved if you do a datasheet sort followed by a save. Access just shuffles the pointers and remembers the new order.

Or, like that GREAT line from The Matrix .... "there is no spoon"

Well, inside Access.... there is no table. So getting a table in any particular order is going to become a bit more difficult.

 

[Pat Hartman]

According to the Jet Programmers Guide, the records of a table are reordered physically into primary key sequence when the db is compacted.

I still can't understand this thread. I can't figure out if russi is trying to order columns or rows or both. In any case, all that is required is a query. If you want to change the field order in a query, then select the fields in the order in which you want them to appear in the exported file. So rather than selecting tbl.*, select each column specifically. Then include an order by clause to order the rows into what ever sequence you want. Although ordering the rows is irrelevant as far as I can tell. The column order is relevant if column headings are not included in the exported text file.

 

[russi]

Hi.

To The_Doc_Man:
Your thoughts re: Oracle are undoubtedly correct, but things are seldom as they seem or simple. - Remember the saying: I am not allowed to blow the train whistle, steer, or decide where to stop, but I must get everyone to their destination on time? - This is like that. -- An outside business has contracted thru our agency (and some others in the country) and get to basically call the shots. The data from here will actually be running thru another state's system, etc. - So bottom line, I am bound to follow things their way, logical or not.

To Pat:
The other company says I MUST in ftp'ing a text delimited file submit all fields in a set order, alphabetically by the fields' names. There are many tables. One in particular has approx. 170 fields (not by my choice). And to manually sort them in a query seemed tough esp where the names often are almost identical.

Unfortunately this other company is not really flexible and I have little say.

Russ

 

[The_Doc_Man]

So compaction re-orders by primary key? Which version of Jet, Pat? I don't recall that it used to do that. Maybe it is time to update my library again. (Darn, it all, Bill Gates! There you go, yanking that rug out from under my feet again.)

As to what this thread is REALLY about, I suspect that Russi has been given a task to do something about table ordering because one of his ORACLE 'gurus' didn't want to be bothered with actually having to earn his keep for the week.

And it ain't the first time I've seen such a thing. ORACLE can be a bit cumbersome when importing large amounts of data. But that's why God let us invent computers. (Besides, they make a much easier scapegoat when something goes wrong.)

I guess the only reason I don't hate Larry Ellison (of ORACLE) more is because Bill Gates is still alive.

 

[The_Doc_Man]

Russi, your post and mine must have crossed in the mail.

OK, are you trying to mechanically build a query in which the FIELD NAMES are presented alphabetically, regardless of the sort order?

If we can clear the air on that question, perhaps we can make some serious progress here. Pat's take about three or four posts ago was the first time I realized that what you were asking was at all amiguous. So perhaps that explains some of my answers that might appear to have been off-center.

 

[russi]

Sorry that this has gotten to be such a 'pain in the ....'.

To accomodate these people, I combined a bunch of my tables and created new ones according to their specs.

So let's take just one table I have to do this with:

Table has 170 fields. There are over 1,000 records. Some fields have data, others are null. The table was created according to their docs that listed the fields in a specific order.
NOW, they say the fields WITH their data in the records must be re-ordered, alphabetically. (Many are almost identical named fields so sorting manually would be a pain.)
Then I must export the table with the data into a text delimited file. ONLY THEN can their software accept my upload and be processed by their software.

Again this must be done with a number of tables. And I then must do this again with other agencies and depts.

Hope I am finally clear. Thanks for 'hanging in'.

Russ

 

[Pat Hartman]

The_Doc_Man,
I wish I had known that I was actually going to meet Bill face-to-face two weeks ago in Montreal at the World Mixed Pairs, I would have given him my list of wishes and complaints.

russi,
Did DALeffler's suggestion work for you?

 

[The_Doc_Man]

In general, there is a way to do this but it is a pain in the toches. (Pardon my Yiddish!)

Here is an outline....

Phase 1: Write VBA code to build a table of field names for you.

Basically, step through each tabledef in the tabledefs collection. For each tabledef, step through each field in the tabledefs.fields collection. Store the result in a recordset that has two fields: TableName and FieldName.

Phase 2: This new table will probably have included itself. Take out the two records it stored for itself. Now write a query that sorts this new table by TableName and FieldName, alphabetically.

Phase 3: Open the names query as a recordset. Step through the records of this query. For each new table name, create a new querydef. OK, now, here is where you get "ugly". The way to add fields to a querydef is through SQL. So you step through all the fields for this table, adding them to a string separated by commas. Suppose the field names were A, AA, AAA, AAB, etc.

You would successively concatenate "A," then "AA,", then "AAA," then "AAB", etc. until you reached the last field of the table. WARNING: If the names can contain spaces or oddball characters, remember to include [] around the names. So you would add "A," but "[A A]," if the field were [A A].

Now you have a string that looks like

"A,[A A],AA,AAA,AAB,....,ZZZ,"

Remove the trailing comma (it should be the last character) using the LEFT function on the string (with Len(string)-1 to remove that dangling comma.)

Now complete the operation by tacking on

"SELECT " & {field-list-string} & " FROM {table-name} ;"

and store this in the querydef's SQL property. Save this querydef as "FLDSRT_{table-name}" and step to the next one. When your loop is done, you have a bunch of new queries you can go back to use for exporting.

Now if the fields have to also be sorted in ascending order in the same order as the names apply, the string would become

"SELECT " & {field-list-string} & " FROM {table-name} ORDER BY " & {field-list-string} & ";"

But there might be a problem with sorting by that many fields in the case of the 170-field table. So you had better warn your folks that you have limits in Access regarding how far you can take the sort operation. I think you can't sort on more than a total of 255 characters at a time, in aggregate. That is, if you try to sort on four text fields of 64 bytes each, that is 256 characters in a sort, which is illegal.

Does this idea help you?