How do I run a query where the results table will have 392 fields

[Guest]

Access has a limit of 255 fields for its results tables by design. Does
someone know how to increase that please, or how I can run a query that will
result in 392 fields. I am involved in a major research study where we have
asked 74 questions and multiple responses are allowed. We have entered all
the data into an access database and now we need 3 master tables to do our
analysis. I would be grateful for your advice. We are using Access 2000.

 

[John Spencer]

You can't exceed the limit of 255 fields in an Access table or query.

Do you need to look at all the fields at one time? If not you could subset
the data.

Are you trying to set this up to export to SAS or another analysis package
that will accept more then 255 fields? IF so, then you could export the
data into a text file using some VBA routines and have the analysis package
import the data from the text file.

 

[Guest]

Thanks John. Yes, I am trying to make up three master files so I can export
them to SPSS for analysis. I don't use VBA so I am a bit stuck. However, I
may be able to run two queries using the unique patient identifier in the
demographics table (outer join) as the linking data; then just match the
unique identifier for each of the results tables manually, in SPSS. It will
be a bit of work but I think it is the only thing I can do now.

 

[John Spencer]

Does SPSS allow you more than 255 columns? Can it import more than 255
columns from a text file? IF so, I may have a vba routine (if I can locate
it) that will export the columns from multiple queries into one row.
Hopefully, your fields do not contain line feeds, but if they do that can
also be handled.