I would use 3 tables for your situation. The first 2 tables would each
contain 201 fields. The first field would be an ID field so you can
relate the 2 tables and then 200 fields in one table and the other 200
fields in Table2. The 3rd table would just contain a list of all the
fields, so you would have one text field in Table3 which would contain
400 rows.
When you start getting involved with big data like this you have to use
VBA code. The idea is that the machine will always do the work faster
than a human. If you have enough data, it could be impossible to
manipulate it by hand. Thus, the computer comes to the rescue. Sure,
for small data, doing things manually is easier than writing a bunch of
code. But for big data, you write the big code once and let the
computer do the work from then on.
Not to sound like I'm tooting my horn here, but to illustrate the point
of letting the computer do the work, I have automated several reports at
my company (big company) where it used to take weeks to develop just one
report by hand and to QA (ie, almost impossible to develop some of these
reports by hand). It took me a while to automate these reports (real
big data with lots of fields - not quite 400, more like 200, but over
2,000,000 records to go through), but now it takes a matter of minutes
to run each report (note: I am still not appreciated - at least, not
very much :).
As for data types for the table fields, you want to steer away from
using memo fields in tables with multiple data fields. It would be rare
to have several fields in Excel where people are entering more than 255
characters. Usually, you will have numeric, date, and small text fields
in Excel. So create your tables accordingly.
Another option would be to highlight and copy one half of the Excel
workbook and paste that into Access, then copy the other half of the
workbook and paste that into Access. Now you have your 2 data Tables.
Then just add the ID field to each table, either at the beginning of the
table or at the end.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!