3800 excel forms to make into table

[Jeff Lane]

I have been given 3800 excel spreadsheets and asked to fill out 65 existing
fields on each.
I know it would be easier if it was a database. Has anyone got any idea how
I could combine this lot into one table.
Don't ask for the logic behind the production of the spreadsheets because "I
ain't got a clue!!"
Thanks

 

[Jeff Boyce]

Jeff

If you haven't a clue about the logic, how do you plan to organize your
database structure? A relational database like Access won't be able to help
you much over your 3800 spreadsheets if you don't have a relational data
model.

Also, what do you plan/need to do with all this, once this part is done? It
could make a difference in what/how you proceed.

 

[Jeff Lane]

Excuse the English irony, "I ain't got a clue " means I don't know why
anyone would produce 3800 individual spreadsheets with the same fields in
each one and then ask a person to fill out the blank fields when common
sense would/should dictate that you would begin with a database instead.
My logic is that I want to end up with one database with 3800 items in.
regards
jeff lane

 

[Jeff Boyce]

Jeff

Thanks for the clarification. My question still stands. What data
structure underlies the 3800 spreadsheets and their fields? For example, is
there one spreadsheet per person, so you are looking at setting up a
database about persons and attributes about them? To make best use of
Access' strengths, you need to start out with paper/pencil and sketch out
the entities and relationships.

 

[Jeff Lane]

The spreadsheets refer to vehicles.
One spreadsheet per vehicle. starts with registration/licence plate number
and the rest of the fieilds are attributes eg make, model, year, colour etc
etc.
Somebody has already produced 3800 spreadsheets with these on. each
spreadsheet duplicates the field names but has it's own individual field
information. I wanted to turn the field into access field and then put the
data into the data fields?

 

[Jeff Boyce]

Jeff

It sounds like (thanks for the "physical" description -- it helps me
understand) your table structure in Access could be a single table, but...
(!) Are there any of those fields that can change? If so, how frequently?
If so, do you care to retain what the old/former value was, plus record the
new/latest value?

Are there any of those fields that would be candidates to "pull out" into
lookup tables? Somehow, I very much doubt you have 3800 different "makes"
<g>!

Are there any "repeating fields" in your attributes? An example would be
fields for "2000_Mileage", "2001_Mileage", "2002_Mileage", "2003_Mileage",
.... Repeating fields are necessary in Excel, and very counterproductive in
Access.

Please note -- I have not forgotten that you wish to pull all the data in.
I'm just working on making sure that what you pull it into is the optimal
structure in Access before you start. This is a standard approach to
building a relational database -- notice that I didn't say a "common"
approach. Many folks just slam the data straight in from Excel and go, then
learn later that they've painted themselves into a corner.

 

[Dkline]

I have been in a similar situation - although it was only 3 workbooks each
with 8 worksheets which had to be combined into one file to be linked.

So once I examined the data that was to come over, I created an alias field
for each needed column in the Excel spreadsheet. I then wrote a macro in
Excel to walk through each workbook, determine how many records in each and
from which column I was pulling the data for that field, place in each
record in the combined sheet, go to the next workbook, etc.

It works. Code is available if you wish to see it.

Don