There and Back Again (Excel->Access->Excel)

[Dugantrain]

I have an open question for those that have had a lot of experience with data migration back and forth from Excel and Access. I'm just getting started on a brand new project which will put data from several different spreadsheets into a custom-designed Access database. The problem is that whoever designed the spreadsheets knows nothing about db logic and the data is a nightmare (from a developer's stand-point, the spreadsheets actually look pretty nice). Many of the fields are completely denormalized. For example, one "Techs" column may have several techs in the same column, some written as "John Doe, Jane Doe", some as "Doe, John/Doe, Jane", some as "Tech #34", etc. Getting the Access table structures together was no problem at all, took me about 2 or so hours to do and I'm confident that the database will eliminate several hours A DAY's worth of manual effort. The problem is that these Spreadsheets have been around for a while now and I'm looking at a Parent table with about 10,000 records with the child tables having between 1-5 related records each and a third subtable with maybe 1-4 records. That's a lot of data, too much to start over with and too ugly for import. I know that you more experienced guys and gals have probably had experience with this sort of thing as Excel and Access are joined at the hips, so how do you approach such a daunting task?
Note: I know that we all hate our users, but just for giggles, "Tell the users to frag off" or an equivalent is not an acceptable answer

 

[Dugantrain]

As always, thank you for your comments, Pat. The only problem with getting a lot of external help with this project is that this is sort of a volunteer thing; I mean, of course I'm getting paid at work, but it was my idea to do this project as I was helping my friend out one day with her daily workload, saw the several Spreadsheets that she sifts through to get her needed data, and instantly knew how much a relational database system would help her as well as her co-workers that work with the same data. So, in essence, because this is my idea, I'm free to do what I want so long as nobody else has to touch it. The denormalization thing actually isn't quite as large an issue as I originally thought, I can just ship these fields to a text file and then ship them back to Excel in comma-delimited format, breaking apart any and every comma, space, "/", etc. That certainly won't be an end result, but after writing "Select Distinct" queries in Access, I'll be pretty close and the manual effort won't be nearly so over-whelming. My big concern is the output; the Project Managers won't want to see anything but the Spreadsheets that are already sent to them daily and to get to this from Access will require a whole lot more knowledge of Automation than me and my 6 months of professional development have. Such is the developer's lifestyle, I reckon

 

[Dugantrain]

OK, here's an odd situation: the users of this system divide their daily tasks into "Core Tasks" and "Non-core" tasks. Everything that is included in their Core Tasks don't get billed to the client and everything that is non-core does get billed. All of the Spreadsheet stuff that I'd be saving them time with by developing a database is billable to the customer, so I think that this db might end up COSTING the department money! But I can't stand seeing my coworkers drowning in Excel, so as long as noone complains, I'm going forward with it.