ACCESS Tables

[Art]

Hi,

I am working on a ACCESS db that someone else has built. The db itself
contains many tables with repeating fields (as many as 150 fields/columns).
The only difference in the field names are years, months, Patients and
Fractions. Here is a sample of 4 fields in one table.
field1 AVM/AVOM- Oct FY08 Num of Patients
field2 AVM/AVOM- Oct FY08 Num of Fractions
field3 AVM/AVOM- Nov FY08 Num of Patients
field4 AVM/AVOM- Nov FY08 Num of Fractions

What I want to do is grab the field names along with their numerical value
and place it in a table with fewer columns. Any suggestions would be very
much appreciated.

Art

 

[Golfinray]

You could use a query. Put the command New Field:Left ([your field name],10)
and that would get you field1 AVM (the first ten characters) then change the
query type to make-table, run it, and there is you table. Then you can add
whatever other fields you need.

 

[Art]

Thanks golfinray for that quick response. I was also hoping the get the
values in each column related to those fields. any suggestion how i would do
this? thanks for the help.

You could use a query. Put the command New Field:Left ([your field name],10)
and that would get you field1 AVM (the first ten characters) then change the
query type to make-table, run it, and there is you table. Then you can add
whatever other fields you need.

Hi,

I am working on a ACCESS db that someone else has built. The db itself
contains many tables with repeating fields (as many as 150 fields/columns).
The only difference in the field names are years, months, Patients and
Fractions. Here is a sample of 4 fields in one table.
field1 AVM/AVOM- Oct FY08 Num of Patients
field2 AVM/AVOM- Oct FY08 Num of Fractions
field3 AVM/AVOM- Nov FY08 Num of Patients
field4 AVM/AVOM- Nov FY08 Num of Fractions

What I want to do is grab the field names along with their numerical value
and place it in a table with fewer columns. Any suggestions would be very
much appreciated.

Art

 

[John W. Vinson]

Hi,

I am working on a ACCESS db that someone else has built. The db itself
contains many tables with repeating fields (as many as 150 fields/columns).
The only difference in the field names are years, months, Patients and
Fractions. Here is a sample of 4 fields in one table.
field1 AVM/AVOM- Oct FY08 Num of Patients
field2 AVM/AVOM- Oct FY08 Num of Fractions
field3 AVM/AVOM- Nov FY08 Num of Patients
field4 AVM/AVOM- Nov FY08 Num of Fractions

What I want to do is grab the field names along with their numerical value
and place it in a table with fewer columns. Any suggestions would be very
much appreciated.

Art

You'll need to use some VBA code using the Table's properties to do this. For
example (untested air code):

Dim db As DAO.Database
Dim tdf As DAO.Tabledef
Dim fld As Field
Set db = CurrentDb
Set tdf = db.Tabledefs("tablename")
For Each fld IN tdf.Fields
debug.print fld.Name, fld.Value
Next fld

You can of course build the SQL of a query, or open a recordset based on the
normalized table and insert the data into it.

 

[Art]

Thanks John that's great.

Would I implement the code you provided as a button on a form? thanks.

 

[John W. Vinson]

Thanks John that's great.

Would I implement the code you provided as a button on a form? thanks.

That's certainly one place. YOu can call the code from whatever event you
like.