Make-table query...

[Jason.A.Oliver]

Hi everyone,

Quick run-down of the problem. We are trying to export tables from
Access to SPSS for analysis. We have tons of variables so had to
break the data up into about 100 different tables within Access.
We're looking for a good way to recompile these into several giant
tables in SPSS.

Basically, what we are doing is using queries to compile the data to
the extent that Access allows us (think the limit is 255 variables).
Then we use a program called Stat Transfer to export the data straight
to SPSS. The problem is that when we run the query, it rips out the
description field, which is where Stat Transfer pulls the variable
labels from.

I've tried setting up a "Make Table" query instead of just a select
query, but it still drops off the descriptions. Is there ANY way to
get around this? I realize there's probably some way to do it with
code, but I'm not a database guy, just the research assistant who got
stuck doing something outside his area of expertise I'm hoping
there's just a setting to "Include descriptions in Make-table queries"
somewhere that I'm not finding.

Any advice would be appreciated. If you need more details just let me
know.

Jason

 

[Jeff Boyce]

I may not be understanding your underlying need...

Have you looked into exporting your data as a text-delimited (e.g.,
Comma-separated) text file?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jason.A.Oliver]

Sorry about that, I will try to clarify.

The issue is that we have tons and tons of variables (a couple
thousand). Because of how access works, we needed to store those
variables in about 100 different tables in Access. Now that its data
analysis time, we need to get them all in a single SPSS file.

We need to retain not just the variable names, but also the variable
labels in SPSS since I don't want to retype or even copy/paste 5000
variable labels into SPSS. Stat Transfer does this by inserting the
"Description" field from tables as the "variable label" when it
exports to SPSS.

The issue we are running into is that we want to combine tables within
Access as best we can FIRST, to save some extra steps later. However
when I use any kind of query to combine the tables, the description
field is removed. Thus we don't have variable labels when I export.

Unfortunately exporting as comma-delimited just adds in more
steps(defeating the purpose), plus it doesn't seem to retain the
description field anyways, which doesn't help us.

Does that help explain things at all? I appreciate you taking the
time to respond!

 

[Jeff Boyce]

I wasn't sure if the delimited text file approach would help. I'll step
back now ... perhaps one of the other newsgroup readers can offer a suitable
approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Sorry about that, I will try to clarify.

The issue is that we have tons and tons of variables (a couple
thousand). Because of how access works, we needed to store those
variables in about 100 different tables in Access. Now that its data
analysis time, we need to get them all in a single SPSS file.

So you basically need to generate a very wide flat matrix from multiple Access
tables, each with fewer than 255 fields, splicing the tables together "side by
side"?

We need to retain not just the variable names, but also the variable
labels in SPSS since I don't want to retype or even copy/paste 5000
variable labels into SPSS. Stat Transfer does this by inserting the
"Description" field from tables as the "variable label" when it
exports to SPSS.

Is this Description field a Text or Memo field that you created in the table,
or is it Access' little-used Description property of the table? The former can
be readily exported; the latter will be much more of a problem.

The issue we are running into is that we want to combine tables within
Access as best we can FIRST, to save some extra steps later. However
when I use any kind of query to combine the tables, the description
field is removed. Thus we don't have variable labels when I export.

About all I can suggest is one more "index" table with the tablename and that
table's associated description.

John W. Vinson [MVP]

 

[Jason.A.Oliver]

You pretty much nailed it on the description, that is exactly what
we're looking to do. I'm talking about Access's ready-to-use
"description" field when you go to the design view of a table. I can
export those just fine since I have a program that does it, so that
isn't a problem.

The issue is that a select query doesn't store the "description"
field, and if I just set up a make-table query, it still drops all the
values in "description" in the new table. Is there any way to stop
that from happening?

 

[John W. Vinson]

The issue is that a select query doesn't store the "description"
field, and if I just set up a make-table query, it still drops all the
values in "description" in the new table. Is there any way to stop
that from happening?

Not really. About all I can think of is to retrieve the Description property
from the tabledefs collection using VBA code, and - somehow - expose it to
the import software.

John W. Vinson [MVP]