Assume there are three personnel who process data, and this data is stored on their individual Excel workbooks. Each workbook is formatted exactly the same.
Each person has a different number of individual spreadsheets within their workbooks, but again: the spreadhseet format is uniform across every workbook.
So Person A has 15 spreadsheets, Person B has 25 spreadsheets, and Person C has 34 spreadsheets.
Is there a way that I can have Access pull ALL of the data from ALL of the spreadsheets?
The "unique key" for the primary data table in Access would be related to the spreadsheets: each spreadsheet contains data one ONE SPECIFIC item or location that has a unique serial code.
Here is my "OH DEAR GOD, I'D LOVE TO BE ABLE TO DO THIS!" statement:
My desired endstate:
o I will have a common database that all three can access and input data
o All three will be able to have the database open simultaneously
o Queries can be run from this information to produce reports indicating amount of data that was processed over an established time period
o Reports could be published (and here is a fun one) that would feed into Excel, which then in turn be utilized to produce .kml files and shape files
---
I have used Access in the past, but it was roughly 9 years ago. I was the only person in my office using it, as I was manipulating a mass amount of information (again, in Excel) and needed to be able to provide multiple reports and data analysis based on this information...and I was the only person in the office this information was given to. I was self taught, and found Access to be extremely useful...even though it was VERY unweildy for someone who had barely started using a computer two months prior. Yes. I was a very, VERY late starter to the world of automation.
I am now in an entirely DIFFERENT office, doing an entirely DIFFERENT job, and I am wondering if I can manuever my personnel from Excel to Access...but I will have to establish the database, create the front end, and develop the report formats first...not to mention the links, formula, setting it so that it produces reports on its own that overwrite existing reports.
However, if I can't import & export from multiple spreadhseets across multiple workbooks...well, at this point I am going to have to figure out a new way to approach the problem. And compiling all the data onto a single spreadsheet per workbook? That isn't going to work.
If this concept IS possible, well...I suppose I'll be looking for Access for Dummies as soon as I can!
(The number of personnel and number of spreadsheets is just there as an example; at any given time there will be SIX workbooks, used by one to five people per workbook, with workbooks containing between 18 and 45 spreadsheets. THOSE numbers are more realistic.)
Thanks in advance!