Shifting column headings - not data...

  • Thread starter Thread starter Monish
  • Start date Start date
M

Monish

Hi

I receive a file monthly from an automated feed, which we have just realized
has mislabeled some fields:
essentially, the label for column number 20 should be in column number 32
and all labels for columns 21 - 32 need to shift one place to the left ... (I
hope that make sense)
while we work on rewriting the code for the automated feed, I need to
develop a workaround. My question for anyone on here is if there is a query
I can write that can take this file and adjust the headings/labels
accordingly with each monthly file feed?

I am sure there is a fairly simple way to do this (outside of writing new
headers for each impacted column) and make it repeatable for each new file
received.

Thanks for looking,
 
If your tables in Access have fields named "Column21", "Column22", ... then
you have a spreadsheet, not a relational database table.

You've described how you're doing something now. If you aren't irrevocably
welded to that "how", consider describing "what" business need you want to
satisfy and see what suggestion folks here can offer.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Okay, I will try to explain further:

I receive a single flat file which I load in to Access so I am able to
manipulate the data within. The fields are all named appropriately - however
in the production of this file the label for the data in the 32nd
column/field appears as the label for the data in the 20th column/field.
Also the column/field labels for data in between these two columns/fields
have shifted to the right by one space. The data is correct, but just
mislabeled.

All I want to do is manipulate the labels to make the correction once I
receive the file. This will allow me to then go about correctly writing my
queries and cross-tabs while avoiding any confusion and potential mistakes.

As far as business need, I am only trying right now to correct the labeling
issue for the file (which sits in Access as a lone table). I did this
manually but was wondering if there is a way I can set up a query to shift
some of the labels one field to the left and make this repeatable so that
each month when I receive this incorrectly labeled file I will adjust it
using this query...

Hope that makes more sense - again thanks for your time.
 
What I am concerned about (and poorly explained in my previous response) is
the fact that you have columns that change.

A well-normalized relational database does not. Once you've defined the
"entities" and "relationships", you'll very rarely need to change the table
structure.

In fact, changing the table structure (the labels or anything else) will
force you to "maintain" your application -- i.e., modify queries, forms,
reports, code, etc. This makes for a lot of work.

You mentioned what you're working on ... but described it again as a "how"
(i.e., what techniques you are trying to use). What I was asking for was
some sense of the business/domain. For example, are you working on helping
get students registered for classes? ... or tracking the stages of
completion that a set of documents go through? ... or ...? The "what" may
offer some clues to us for coming up with ideas on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
UNTESTED VBA CODE

Something like this MIGHT work. Try it on a COPY of your table

Public Sub RenameFields()
Dim tdef As DAO.TableDef
Dim db As DAO.Database
Dim iCount As Long
Dim strFldNewName As String

Set db = CurrentDb()

Set tdef = db.TableDefs("YourTableName")
'Field numbers are zero-based, so the 20th field is 19
strNewName = tdef.Fields(19).Name
tdef.Fields(19) = "Temp"

For iCount = 31 To 19 Step -1

tdef.Fields(iCount).Name = strNewName
strNewName = tdef.Fields(iCount - 1).Name

Next iCount


End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top