Reformating a Table through VBA

[Guest]

This is my problem, but I lack the programming language/understanding to
accomplish it.

I need to turn a table that looks like this:

field names: DATE ABS_CAN ABS_ONT
ABS_ALB
data: 12/1/1995 50 25
25
data: 12/1/1996 60 30
30

Into this:

field names: ID KEY DATE
VALUE
data: 1 ABS_CAN 12/1/1995
50
data: 2 ABS_CAN 12/1/1996
60
data: 3 ABS_ONT 12/1/1995
25
data: 4 ABS_ONT 12/1/1996
25

And so on....


As I figure, I need to cycle through a) the fields in the original table,
and b) the records in each field of the original table. Would probably look
something like:

for each fld in table.fields
for each ???? in ?????
key = fld.name
...............................?????

Dont know where to go from here. I want to make a new table out of this
query, and it will contain many more tables.

Any help is greatly appreciated, Thanks!

 

[Guest]

Apparently my last post was messed up, leading to a confusing table. This is
what I meant:

I need to turn a table that looks like this:

DATE | ABS_CAN | ABS_ONT
----------------------------------------------------
12/1/1995 | 50 | 25
12/1/1996 | 60 | 30

Into this:

ID | KEY | DATE | VALUE
----------------------------------------------------------
1 | ABS_CAN | 12/1/1995 | 50
2 | ABS_CAN | 12/1/1996 | 60
3 | ABS_ONT | 12/1/1995 | 25
4 | ABS_ONT | 12/1/1996 | 30


This is what I've got so far - It almost works, but the resulting table
"Trial" has about 40000 records too many, with wierd keys like
~TMPCLP105351_KEY and it got hung up on a table MySysObjects or something,
which doesnt even exist.

I would like to run this little update/sort program once a month. I have
Access 2003. All of the tables are linked to worksheets in an excel file
(easiest for me to update this way).


Dim newtbl As Recordset
Dim rs As Recordset
Dim db As Database
Dim tblnme As String
Dim fldnme As String
Dim rwsrce As String
Dim dt As Variant
Dim vl As Variant
Dim intloop As Integer
Dim tbl As TableDef

Set db = CurrentDb

For Each tbl In db.TableDefs
tblnme = tbl.Name

Set rs = db.OpenRecordset(tblnme)
Set newtbl = db.OpenRecordset("Trial")

For intloop = 1 To rs.Fields.Count - 1

Do Until rs.EOF = True

With newtbl
.AddNew
!Key = tblnme & "_" & rs.Fields(intloop).Name
!Date = rs!Date
!Value = rs.Fields(intloop).Value
.Update
End With

rs.MoveNext
Loop

rwsrce = rwsrce & fldnme
rs.MoveFirst

Next intloop

Next tbl

End Sub

 

[Tim Ferguson]

I need to turn a table that looks like this:

ABSDATE | ABS_CAN | ABS_ONT
----------------------------------------------------
12/1/1995 | 50 | 25
12/1/1996 | 60 | 30

Into this:

ID | KEY | ABSDATE | VALUE
----------------------------------------------------------
1 | ABS_CAN | 12/1/1995 | 50
2 | ABS_CAN | 12/1/1996 | 60
3 | ABS_ONT | 12/1/1995 | 25
4 | ABS_ONT | 12/1/1996 | 30

Sorry: I had to change the name of the DATE field because it's far too
prone to errors.

SELECT "ABS_CAN" AS Key,
AbsDate,
Abs_Can AS Value
FROM OldTable

UNION

SELECT "ABS_ONT" AS Key,
AbsDate,
Abs_Ont AS Value
FROM OldTable

ORDER BY Key, AbsDate ASC



I have to guess about the order. You can put this whole thing into a
ranking query (google for Access SQL Ranking Query) to put in the ID
number; on the other hand it might be easier just to dump the output into
an Excel sheet and have it done for you.

Hope that helps


Tim F

 

[Guest]

Tim, thanks for your help but Its impractical for me to run an sql query each
time as I have over 60 separate tables. This is why I'd like to have a
little program that runs when I open the database to automatically update the
records that have been changed in excel and put them in the proper form.

 

[Tim Ferguson]

Its impractical for me to run an sql query each
time as I have over 60 separate tables.

Huh? You run it every time you want to look at the data, surely. If you
have lots of tables, that's a reason to use queries rather than trying to
duplicate the tables, missing one out, trying to go back, getting confused,
deleting it and starting over, then discovering you deleted the good
version not the messed up one, phoning the network admin to undelete the
files, giving it all up and getting drunk instead.

On the other hand, maybe it's not<g>


All the best


Tim F