Access: Transposing data in a flat file format

  • Thread starter Thread starter Julia
  • Start date Start date
J

Julia

Hi, All -

I was wondering if anyone could give me some insight as to how to
change the format of a data set in Access (v1997).

The data I have is currently in this format:

Identifier Var9701 Var9702 ... Var0201 Var0202 Var0203
111111111 $50.00 $250.00 ... $500.00 $50.00 $250.00
Where the variable represents a monthly value on the same variable
over 60 some consecutive months (VarYYMM).

I would like to change the data so that I have sixty some lines of
data with only three variables for each unique identifier instead of
sixty some variables on each person. The format I'd like to convert it
to looks something like this:

Identifier Date Var
111111111 1/1/97 $50.00
111111111 2/1/97 $250.00
....
999999999 1/1/02 $425.00
999999999 2/1/02 $350.00
999999999 3/1/02 $425.00

The only way I know how to transpose data is via a crosstab query, but
rather than run several dozen crosstab queries, I'd like to be able to
run SQL code to convert these files, as I've got about 200 individual
data sets formatted in this way.

Any help would be greatly appreciated!

Thanks,

Julia Drew

P.S. This data originally comes from a SAS file, so if someone doesn't
know how to accomplish this in Access, but does know how to carry this
out in SAS, that knowledge would also be valuable.
 
Julia wrote:

[old]
Identifier Var9701 Var9702 ... Var0201 Var0202 Var0203
111111111 $50.00 $250.00 ... $500.00 $50.00 $250.00
Click to expand...
Where the variable represents a monthly value on the same variable
over 60 some consecutive months (VarYYMM).
[new]

Identifier Date Var
111111111 1/1/97 $50.00
111111111 2/1/97 $250.00
...
999999999 1/1/02 $425.00
999999999 2/1/02 $350.00
999999999 3/1/02 $425.00

The only way I know how to transpose data is via a crosstab query,
Click to expand...

That is the wrong way around here. You need insert queries (on a new
table) for every var9902-column.
rather than run several dozen crosstab queries, I'd like to be able to
run SQL code to convert these files, as I've got about 200 individual
data sets formatted in this way.
Click to expand...

So? Run SQL code. :-)

Your question winds down, maybe, on how to automatically produce that
code. Hmm, that shouldn't be too hard... But how varying is the data
format in these sets?

Let's try for the example you provide. I assume some names:
sapdata: the original -imported?- table (identifier varxxxx etc)
locdata: the new table (identifier, vardate, varvalue) [don't use 'date'
as a field name because it has meaning to Access]

I'm going to examine the TableDef object for sapdata, loop through its
Fields collection, skip the first as it is 'identifier' and execute some
insert statement for all other fields.
What insert statement?
INSERT INTO locdata(identifier,vardate,varvalue) SELECT identifier,
columnAsDate(fieldname), &fieldname FROM sapdata

the function columnAsDate takes the field name, cuts off the 'var' part,
adds 1900 or 2000 to the next two digits -> year part of date, inserts
the last two digits -> month part of the date. Actually, I don't need
messing with 1900 or 2000, DateSerial will handle this for me.
&fieldname means I have to insert the literal value into the statement.

Wait, I'll let you pass the name of source and target tables. That is of
no value if the 'identifier' field will vary, but feel free to complain
in that case.

Air code: may not work immediately. I have compiled, though, to catch my
own stupidities ;-)

Sub unCrosstab(cSource As String, cDest As String)
Dim cSQL As String
Dim td As TableDef
Dim fd As Field
Set td = CurrentDb.TableDefs(cSource)
For Each fd In td.Fields
If fd.Name <> "identifier" Then
cSQL = getStatement(cSource, cDest, fd.Name)
CurrentDb.Execute cSQL, dbFailOnError
End If
Next
Set td = Nothing
End Sub

Function getStatement(cSource As String, cDest As String, cField As
String) As String
Dim cSQL As String
cSQL = "INSERT INTO " & cDest & "(identifier,vardate,varvalue)"
cSQL = cSQL & " SELECT identifier, " & columnAsDate(cField) & ","
cSQL = cSQL & cField & " FROM " & cSource
getStatement = cSQL
End Function

Function columnAsDate(cField As String) As Date
columnAsDate = DateSerial(Mid(cField, 4, 2), Right(cField, 2), 1)
End Function
 
On my website (see sig below) is a small sample database called
"Normalize2.MDB" which does this with VB code. Perhaps it will give you
some ideas.
 
Roger said:
On my website (see sig below) is a small sample database called
"Normalize2.MDB" which does this with VB code. Perhaps it will give you
some ideas.
Click to expand...
Nice solution, using VBA/DAO. Clear coding style. I think I like your
way of working. I will keep your site in my bookmarks.
 
Bas Cost Budde said:
Nice solution, using VBA/DAO. Clear coding style. I think I like your
way of working. I will keep your site in my bookmarks.
Click to expand...

Your website had a lot of helpful examples. Thanks for your help!

Julia Drew
 
Back
Top