Tool to transfer SPSS variable view between SPSS and Access or SQL Server?

Access uses a relational database as its data store and what you are describing violates first normal form and so would not be supported by a relational database including Jet, ACE, SQL Server, Oracle, etc.

What format does SPSS export data in?  If it exports to Excel, the sub table would be contained within a single cell.  If it is a .csv format, the sub table would be within a single field and would have to be either enclosed in quotes or delimited with something other than commas.

In either case, Access can import the file and stuff the sub table into a single column.  Of course working with it that way would be problematic so you might want to write a program that pulls the mushed data out of the column and appends it to a child table.

If SPSS can export as XML, Access can import that format.

I have never seen a tool that converts SPSS files to Jet/ACE but that doesn't mean that one doesn't exist.

SPSS has a variety of native export options (to Access, Excel, etc.) but none of them export the sub tables.

SPSS visually presents a table within a cell and allows the user to edit that table or select a value from it. I have no idea how it stores it in its proprietary data file. When it does an export, it just exports the specific value for that record in that field and does not export the table (i.e. the various possible values).

To model this in Access, we'd essentially need sub tables joined to the main table where each sub table contains the embedded tables from SPSS.

I realize this is HIGHLY optimistic of me :), but I was hoping that a tool exists to do that.

So the structure is normalized.  It only contains a single value.  From your original description, it sounded like Pick which actually embedded tables into fields.  What you are describing is like the RowSource of a combo box.  You would need to look at the table definition.  There may not be an automated way to export the list but you might at least be able to see it.

Sadly, it DOES embed a table in a field. It's just that it's built-in export command doesn't replicate that so it only exports the actual value for each record. But we need to be able to export the embedded table, too, so that the created Access database has a list of all the possible values for each field - not just a list of the values that have actually been used up to this point.

so it only exports the actual value for each record

Now you're confusing me.  Is it ONE value or MANY values in one field?  Possible has a different meaning from actual so again I am confused.  Possible values are the RowSource of a combo or list box.  The RowSource is a list of all POSSIBLE values.  The actual value will be ONE of those.  Sounds like with SPSS, you could choose several values.  Actual values are what is stored in the table and in the case of Access, that would normally be one attribute per column.  But a database isn't smart enough to evaluate what you stuff in a column so if you want to stuff a string of values all together in one column, you can.  It is just that the database engine provides NO tools to work with that kind of data since is it is officially unsupported.

What EXACTLY gets exported?  Will you see a comma separated list like - blue, red, orange?

Sorry for the confusion.

Let me try to clarify.

When they are setting up the database in SPSS for each field you can specify the following parameters:


If that's all there was, then we could model it easily in Access. However, they also have an extra parameter you can set for each field:


And this is our problem. Because the "Values" parameter is actually an embedded table of options that are valid entries for that field. So, for example, they have a field for marital status and it is set up in SPSS with these parameters:


But when you click on the Values parameter, it pops up a table (I'm going to call it a value table from now on for clarity):

1 = Married
2 = Single
3 = Divorced
4 = Widowed

This table is entirely contained in the Values parameter for the marital status field.

When I uses SPSS to export to Access, it creates an Access database that is populated with the actual data for each person in the database. The marital status field on each record will just have the number that corresponds to the marital status of that person. So we end up with a table in Access that has 1, 2, 3, or 4 in the marital status field for each record. But nowhere in exported Access database do we get an explanation of what those numbers mean. Even though the value table (1 = Married, 2 = Single, etc.) is embedded in the SPSS database, it doesn't export this table to Access.

They have dozens of SPSS files (and are getting more all the time) that they want to export into an Access database. Each of those SPSS files might have dozens or even hundreds of fields per record. And most of those fields have their own value table.

So I'm looking for a way to export these value tables because otherwise, I get an exported Access database that is full of numbers but we don't know what any of those numbers mean.

I hope I haven't further confused things.

Access has the same feature.  It's called Lookup and you can specify a RowSource right in the table definition.  when Access tables are exported, the "lookup" values are not exported; only the chosen value.  To get the "lookup" values, you need to examine the table definition so that is probably the case for SPSS also but you'll probably get a better answer in an SPSS forum since this really has nothing to do with Access.