seperating columns from a table

[Guest]

I have been working with an Excel spreadsheet that contains over 150 columns
of data. I tried to create one table in Access to mirror this spreadsheet,
but there are too many columns for a single table. How can I break these
columns into two tables and maintain integrity?
Thanks

 

[Jeff Boyce]

MS Access tables can hold up to 255 columns each. This is NOT a good use of
Access, however, as it is rarely necessary to have more than 30 fields in a
well normalized table.

In Excel, you don't have much in the way of other options, so you have to
keep expanding "wide".

In Access, if you take the time to normalize your data structure, you'll
find you have "narrow, but deep" tables, rather than "wide" tables.

As for parsing the data, you should be able to import the Excel data into a
"temporary" table (don't use cut/paste, use File/Get External
Data/Import...). From there, create queries that parse appropriate pieces
into their respective tables. If you want to ensure referential integrity,
you'll need to work out the one-to-many process to ensure you get the "ones"
appended to their "permanent" tables before working to append the "manys" to
their tables.

You may have to use a temporary ID to keep things connected during this
parsing. Depends on your data, which you haven't described...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

GBlin,

When you say that you wanted to create a table, does it mean you were trying
to import the data from Excel into an Access table? You may have to cosider
breaking your data into more than only 2 tables.