Shifting column headings - not data...

[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,

 

[Jeff Boyce]

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

 

[Monish]

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.

 

[Jeff Boyce]

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

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could change the import specification - put your own headings for
each column. Then use that import specification for each import of that
file.

You don't say how you're importing the data - I'm assuming you're using
the File > Get External Data > Import... menu selection. If my
assumption is correct:

1. When the import dialog box appears click the Advanced button.
2. The Import Specification dialog box appears. In the lower half of
the box the columns are listed. Just change those column names to their
proper name.
3. Save the import specification w/ a memorable name and click the Exit
button.
4. Continue the import.
5. When doing a new import just select the Advanced button and select
the above saved import specification, exit and continue the import.

You can also use the named Import Specification in a macro or VBA
routine - use the DoCmd.TransferText method - as an automated import.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqhAHYechKqOuFEgEQKkzwCgnNL01y/V3Ca9PjCrkkG58UQm7DEAn2Bn
j8KNS1NvdgTSCNWlqw8Vix+m
=yHav
-----END PGP SIGNATURE-----

 

[John Spencer]

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
'====================================================