Tables...Designing a database using imported text data.

[Guest]

The table I've imported consist of 20,000 records across 140 fields (all text
data type). It's very consistent even though it's large and 100% text
values. I find it easy to import, delete, and append several (appox 500 to
1,000) records everyday. BTW - data integrity is critical so I never type
new data into the 140 fields…new data is imported 100% of the time.

Once the table is updated, I still export to Excel so I can use formulas
(vlookup), filters, and sorts to find or convert information I'm looking for.
I am extremely comfortable with Excel, however, It takes a lot of memory.
Furthermore, it's slow but it is difficult to give up my crutch (Excel)
because it still gives me results.

I concluded I'm long overdue to convert to a relationship database. I've
tried several things using Access and have seen success, but eventually I run
into the same underlying problems with table design. I can't figure out how
to break up the 140 field table and still imported and append the the "text"
data imported daily.

I've made several unsuccessful attempts breaking the large table into a
relationship database. I've spent hours trying to logically reason which
subjects/tasks would break up the large table into more useful tables. I've
already deciding which fields need to be converted from text to numbers,
dates, etc. to run queries and establish relationships between tables.

Assuming I am successful soon…How do I import and append text data to the
newly defined tables and fields?

 

[Douglas J. Steele]

Import your data into a temporary table, and then use queries to take that
data and populate your properly normalized tables.

To be honest, I'm shocked you've been able to get 140 text fields to work,
unless each text field is fairly small. There's a 2000 byte limit on row
size on Access, so your fields must average less than 15 characters per
field.

 

[Guest]

Yes...all of the fields are small.

Ok...I currently import the data into a table but I really don't now how to
use it within Access so I still export to Excel. How can I use this table as
the "temporary" table? How do use convert the data with queries? How do I
know I have properly normalize my tables?

Maybe this is to big of a task for my skill level? Is the resources or
someone that would be willing to help me "properly" normalize my tables?