How to alphabetize column names in a table? (1 Viewer)

[pheidlauf]

My employer wants me to update and revise a form with almost a hundred controls. All of the information is stored in a single table.

Is there a way that I can (easily) sort the order of columns in the table to alphabetize them? I know how to sort and filter records, is there a way to sort the columns other than the manual click and drag solution?

Note: I know that the database I'm working on for this project is not normalized. My employer isn't concerned with normalization. I've made as many corrections to the table as possible, already, but some things just won't be fixed.

 

[plog]

My employer isn't concerned with normalization.

The CEO of McDonald's isn't concerned with my Big Mac missing its pickles. He's got staff underneath him whose responsibility that is.

If you are the one in charge of this database (and if your in charge of putting band-aids on this form, it sounds like you are) then you are being derilict with your responsibilities.

With that said, there isn't an easy way to sort the fields of a table. You can't do it in the table but you could generate a query that produces them in alphabetical order, but that would require some tricky VBA.

I'm not done crapping on you yet. Why do they need to be sorted in the table? User's shouldn't be working in the table. If that's the case and you have just one big table, why not use Excel? With that, I'm sure it will be easy to do what you want and its not like your using Access properly anyway so you wouldn't be eliminating functionality from your system.

 

[RainLover]

I did this a long time ago with a large database.

I believe I used a cross tab query and exported to excel.

That is about all I can say to help.

I am in agreement with Plog. Why are you using Access at all. Excel can do what you have described only better.

 

[jdraw]

I tend to agree with plog. You were given this database job/responsibility for some reason(s). If the boss isn't concerned with Normalization, it is because he doesn't know the details, or is depending on you to do what is considered appropriate database design.
Unless someone said to you, do a lousy job; don't worry about design or maintenance;.. then I think you have an obligation to tell others about design principles and the benefits. I don't think your company bought Access and gave a job to you so you could do it poorly. I think you may have an opportunity to learn and teach. If you don't want that opportunity then don't do database. Use Excel. Or take a stand and do it with Access, but use best practices as best you can.

Can you tell us more details about your database? What is is used for?What is the main subject area(s)?

Good luck with your project and decisions.

 

[pheidlauf]

I think you have an obligation to tell others about design principles and the benefits.

As a 22 year old intern, I've tried convincing the 50 year old engineers that they should normalize the database. Convincing the primary user of this "database" that he should use a primary key and autonumber took about 10 minutes by itself. Convincing them that they shouldn't store paired values (Value X is True, Value X is False) took about 15 minutes. I agree, I should convince them all that it needs to be normalized. However, the only role they actually assigned me was to "update the form" to include several new fields and organize the form for better printing. They are expecting this work to take several hours. To entirely fix there database would take days or weeks (due to my not being a database specialist, I'm a mechanical engineering intern helping out where I can).

If that's the case and you have just one big table, why not use Excel? With that, I'm sure it will be easy to do what you want and its not like your using Access properly anyway so you wouldn't be eliminating functionality from your system.

They specifically want a MS Access form. Excel might well be easier for this. However, they already have a system in place (this particular form and database is one of four or five).

Why do they need to be sorted in the table? User's shouldn't be working in the table.

The sorting isn't for the users. The sorting is for my use as I revise the form. I'm entering the control sources using the combo box in the property menu for each record.

Use Excel. Or take a stand and do it with Access, but use best practices as best you can.

Thank for not "crapping" on me. Perhaps I didn't explain it well enough in my first post. I am trying to use best practices (within the scope of this work) as best I can in the time I have to give to this project.

 

[jdraw]

Normalization is fundamental to relational database.
Have you had any formal Access training? Could you ask for same given the assignment you have been given---since They specifically want a MS Access form.
Is there anyone in the "engineering group" that has some knowledge of database?

What exactly does the company use now?

Can you tell us more details about your database? What is is used for?What is the main subject area(s)?

 

[pheidlauf]

Normalization is fundamental to relational database.
Have you had any formal Access training? Could you ask for same given the assignment you have been given---since They specifically want a MS Access form.
Is there anyone in the "engineering group" that has some knowledge of database?

What exactly does the company use now?

Can you tell us more details about your database? What is is used for?What is the main subject area(s)?

My education in MS Access has been entirely through internet tutorials and getting help on this website. I generally learn software very quickly, so I've taught myself the functionality of Access for the most part, and have learned the basic normalization theory from pages like this (http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html). Sadly, I think I'm the most educated on relational databases of all of the engineers related to the project.

I believe they have one younger engineer who knows basic VBA and SQL, and can utilize the primary design tools of MS ACCESS, but doesn't seem to know much about the actual design of the database relationships.

Subject Area: Prototyping Routing Card

The "Routing Card" database has no relationships. It is a single table, a single form, and several reports. They fill in roughly half of the data (the design and engineering information), then print out the form and have operators fill in measurements and time spent and other such values by hand. At the end, this "Routing Card" is entered into the system using the same form that was printed out.

They use the database and print out to provide technical instructions and information to the operators doing the prototyping work, as a checklist for the various inspections it has to undergo, and to record hours spent, materials used, engineering comments, and many other values I don't actually understand.

 

[jdraw]

I have listed some tutorial training materials at
http://www.accessforums.net/access/what-table-should-i-make-purchase-sales-43061.html#3

You normalization reference is a good start.

I'm trying to visualize routing card --- sounds a little like process flow and control. Perhaps you could elaborate.

You say you don't fully understand the whole process and I'm sure none of the others do either. I expect that if there was a model of the processes and the data flows, a lot of changes and efficiencies might evolve.

A data model might help get the definitions made more current. And would give any database activity a base from which to work.

If what they have is working from business perspective ---the company is making money -- then major change should be approached with caution. If there is recognition that there are inefficiencies in the business, then a quick fix is not going to solve much.

 

[plog]

pheidlauf sent me a private message explaining his situation and said I jumped the gun in "crapping on" him. Since my initial post was public, I decided to reply to his private message publicly as well:

I did make some wrong assumptions about your situation and too harshly worded my criticism. So to you I apologize.

I have some harsh criticism for your organization and would love to see the way this system eventually bites them in the butt (its coming). In the meantime I feel for your situation of responsibility but no decision making power. Unfortunately this is the way the world works and why my initial post was so harsh--usually its inept and spineless "Access Developers" who create these systems and your initial post sounded like that described you.

Now it sounds like your just the middleman who didn't oversell his abilities, but none the less got stuck with it. My advice is to either throw yourself whole heartedly into Access and create the system they actually need, or do a half-ass job of it so you don't get tagged as the owner of this system and responsible for continuing to implement their hair brained ideas

Again, sorry for my words and your situtation.

 

[pheidlauf]

Regardless of criticisms and great advice on what the database I'm working on should look like, my initial question still does stand.

Is there a way to alphabetize column names in a table? Just as a refresher, the purpose of alphabetizing the columns is for structure and organization during form development, NOT for the users. Thanks

 

[jdraw]

As for alphabetizing in a table, I'd suggest a maketable query. Use your original table and pick the fields to be output in alpha order,your new table will have all fields in alpha order.