Link to home
Start Free TrialLog in
Avatar of toalexsandr
toalexsandrFlag for United States of America

asked on

Create a database to manage our spreadsheets among 6 inspectors

I am in need of advice. I need to create a database where we can manage our several spreadsheets among 6 inspectors.

Our quality department is using excel spreadsheets to track our inspections and after inspection we update the same spreadsheets with inspection results.
We receive information by email from another department via excel spreadsheet as well, which they download from their management database that we have no access to and never will. We take their info filter down to what we need to look at and paste it into our spreadsheet.

As a result our spreadsheet grows exponentially daily. We are pasting about 300-500 line items ready for inspection but we are only able to randomly inspect 20 percent of that list. Afterwards we record our inspections results for those 20 percent on the same spreadsheet. At the end of the month we will have 5000 or more line items that we have pasted for our spreadsheet. Our spreadsheet also contains various conditional formatting, code, Pivot Tables and charts for various reports that we need to issue daily, weekly and monthly.

As you may have realized this spreadsheet becomes very slow after 2-3 months of this type of work and we need to start with clean template the forth month.

I need to somehow incorporate the spreadsheets we are using into one, but at the same time be able to update them with inspections results by 6 inspectors at the same time.

I have been using MS Access before, but not on this project, and I am not sure of what limitations exist when using excel as db in access. I am not too familiar with FileMaker, but I am not sure if it can have multiple users accessing it at once. I will be coding this database myself, and I will have a limited budget when starting.

My dilemma is what database options I have or need to look at where each inspector can import their newly received data for inspections and after inspections everyone can update their results in database.
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Agree whole-heartedly with Scott.

When doing this type of thing, I like to put the unprocessed Excel spreadsheets in a particular folder on my server, then, after the file is processed into Access, I move that file into a "processed" folder.

I generally link the Excel worksheet into my Access application, run a number of acceptance tests to validate the data (data type, values, ...) before importing it into my production tables.
It certainly sounds like FileMaker would be an appropriate tool to work with. In answer to your question is does support multiple users as well as automation of imports such as spreadsheets. FM outsells and is consistently ranked over Access and other databases for a number of reasons but it's ability to do rapid development is one of the main features. Excellent interface development tools and the new WebDirect technology also allows you to work in FM using the built-in interface tools (buttons, scripting, conditional formatting, various CSS type button states, etc.) and then publish directly to the web without writing HTML or Javascript.

To use FileMaker or any other tool, you'll need to get a handle on the basics of relationship database design. I think there are some good tutorials online.
FM outsells and is consistently ranked over Access
Curious where you got this bit of information. Care to share a link or two?
Avatar of toalexsandr

ASKER

Thanks for your help. I have thought about importing excel file as well, but wasn't sure.

We receive spreadsheet with many columns and we hide most of the columns and only use 12 that identify our inspection areas. The columns we leave are item category, item code, item location, item description, completion date, service group. and several more, which I don't remember right now. Then we filter the results by completion date of items completed in the last 2 working days. Then we paste into our inspection spreadsheet filtered results and remove duplicates items where item code is highlighted.

So what ever the import function will be written will need to do 3 things, import the whole spreadsheet, filter by last 2 working days and remove duplicates that already would exist in the database.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your help. I will be here again, with more questions.