A Friend Suggested SQL...

[samsilverman]

We are using Excel for our client database to produce reports and invoices for environmental testing. A lot of technical formulas, lists, etc are in the background. But, the reports have to be easy for the average client to understand at a glance (do we pass? If not, what do we do? Etc.).

Frankly, the database is probably bloated and inefficient because I built it and I know enough about Excel to be just short of dangerous. But, this forum as been invaluable.

Anyhoo, a friend suggested that we might want to move to an online solution where techs could enter data in their laptop or mobile device and then upload it to a server. Those reports and raw data could then be accessed by the techs and the clients could even pull reports off of a website.

Does this make sense? Can anybody point me in a direction. I am talking about some consulting work here and I know this is a free forum. Hope I haven't violated any rules. Just looking for some advice. Thanks.

 

[Macropod]

Hi samsilverman,

A cost-benefit analysis would be need to inform a decision on which approach makes the most sense.

Ultimately, you may need to move to some form of database (eg Access), but making data entry and reporting available via the web takes this to a whole new level and could be far more expensive to implement and maintain than you could justify. Conversely, without them, your business might be unable to grow. I doubt anyone here could say what the answer is (due to a lack of detail, etc) - you need professional advice.

Whether you maintain your Excel-based system or move to a database, MS Office already includes plenty of tools to help with data entry and reporting.

 

[samsilverman]

Thanks for the quick and honest reply.

 

[SydneyGeek]

It's easier to do with Office 2007 / 2010 than with earlier versions.

As Macropod said, a web-based solution is likely to be costly. You may be able to do something like this, using 2007...

1. Build an Access database to hold the data. Also create the reports.
2. Using the built-in wizard, generate an Outlook email template for filling in the inspection data.
3. When the user sends the email it can be imported directly into Access.

4. At this stage you *may* be able to automate things so that a PDF report is emailed back to the user. At this stage they could add some commentary and forward it to the client.

Step 4 is the catch. If you (or a consultant) can find a way to do this you may have a system.

Denis

 

[samsilverman]

Thank you so much for your added input.

 

[Derek Brown]

In case it helps, the following describes (a simple view) the Data Warehouse process that I have set up. See http://en.wikipedia.org/wiki/Data_warehouse for a description of a Data Warehouse.
I have a Windows 7 PC running Office 2007 that is dedicated to managing the Data Warehouse.
In the early hours of the morning it runs a number of system tasks (using Windows 7 Task Scheduler) to create/update various workbooks and database tables that will be linked to a Microsoft Access database (Operational database layer) - with that database also having links to other Access databases and IBM iSeries systems. A task (Data access layer) will run queries, VBA code etc. to transfer data to other databases (Informational access layer).
Excel report workbooks have data links to the (Informational access layer) database(s) for speedy updating/refreshing of reports.
On the PC, Microsoft Outlook VBA code analyses emails received to either process reports being received or service requests to provide reports by email. Because the report data has been 'pre-processed' overnight, email replies to report requests are normally received within 6 seconds.
That is a very simplistic overview - it is Microsoft Excel VBA that does all the hard work, being called from VB.net code run by the Task Scheduler, to use automation to produce Excel workbooks, Access tables and queries/views, Microsoft MapPoint maps, Microsoft PowerPoint presentations etc. One of the Excel workbooks "self-documents" the Data Warehouse by creating searchable tables listing all Data Warehouse reports, tables, queries/views, column/field headings, connections etc.