Converting fields to variables

[polisci grad]

For my purposes, I think it may be easiest for me to copy my tables from
excel files and paste them as tables into Access. These tables have 'time' as
the row headings and have multiple columns (fields), each of which are for a
different variable.

When I copy them into Access, the column headings become the field headings.
NO problem so far. What I would like to do, however, is create a table with
the name of each data table stored, along with the full name of the field, on
which I can build queries to separate tables, and make new ones with fields
from different tables.

My problem is that I dont know how to take the column heading of a table and
turn it into a variable that can be used in a query (say cross tab, with
field name as row headings). Is this possible?

James

 

[Tim Ferguson]

For my purposes, I think it may be easiest for me to copy my tables
from excel files and paste them as tables into Access.

It is very rare for a good Excel design to be a good Access design at the
same time. It's a bit like saying you've started with a really good laser
printer and you've copied it into a jet ski.

I would recommend starting with a good relational database design, and
then transferring the excel data into it.

What I would like to do, however, is create a
table with
the name of each data table stored, along with the full name of the
field, on which I can build queries to separate tables, and make new
ones with fields from different tables.

Answer (1) this is already there. A relational database always holds its
metadata (i.e. the schema) in R tables. Unfortunately, the system tables
in Access are not documented and are not intended for end user or
platform developer use.

Answer (2) you can use DAO to get at all the tables and field names, like

for each tdf in Tabledefs
for each fld in tdf.Fields
debug.print "[" & tdf.Name & "].[" & fld.Name & "["
next fld
debug.print
next tdf

or do something useful with them.

Answer (3) goes back to my first point: if you actually design the
database, then there is nothing to look up because it's all in the desing
documentation. Spreadsheets make bad databases. You build your queries
using the logical structure of the entities. If you store data in field
names and table names (which is common and good practice in Excel), you
will make Big Trouble for yourself.

My problem is that I dont know how to take the column heading of a
table and turn it into a variable that can be used in a query (say
cross tab, with field name as row headings). Is this possible?

The easiest way to create a cross tab query is to use the cross tab query
designer. You really don't need to go messing about with "knowing field
names".

Hope that helps


Tim F

 

[polisci grad]

i see...thanks..

For my purposes, I think it may be easiest for me to copy my tables
from excel files and paste them as tables into Access.

It is very rare for a good Excel design to be a good Access design at the
same time. It's a bit like saying you've started with a really good laser
printer and you've copied it into a jet ski.

I would recommend starting with a good relational database design, and
then transferring the excel data into it.

What I would like to do, however, is create a
table with
the name of each data table stored, along with the full name of the
field, on which I can build queries to separate tables, and make new
ones with fields from different tables.

Answer (1) this is already there. A relational database always holds its
metadata (i.e. the schema) in R tables. Unfortunately, the system tables
in Access are not documented and are not intended for end user or
platform developer use.

Answer (2) you can use DAO to get at all the tables and field names, like

for each tdf in Tabledefs
for each fld in tdf.Fields
debug.print "[" & tdf.Name & "].[" & fld.Name & "["
next fld
debug.print
next tdf

or do something useful with them.

Answer (3) goes back to my first point: if you actually design the
database, then there is nothing to look up because it's all in the desing
documentation. Spreadsheets make bad databases. You build your queries
using the logical structure of the entities. If you store data in field
names and table names (which is common and good practice in Excel), you
will make Big Trouble for yourself.

My problem is that I dont know how to take the column heading of a
table and turn it into a variable that can be used in a query (say
cross tab, with field name as row headings). Is this possible?

The easiest way to create a cross tab query is to use the cross tab query
designer. You really don't need to go messing about with "knowing field
names".

Hope that helps


Tim F