Access & Customized Forms

[jscranton]

First time poster and I apologize for the length of this.


Here are the basics:

I am working on a side-project at work with no hard deadline.

I am comfortable with Excel, Access, VBA, Java, PHP and SQL. By no means an "expert", but I am industrious enough to find solutions for a variety of problems.

Here is the project:

--My office uses excel spreadsheets to track the Quality of our employees during a given project. The spreadsheets track hours worked, quantity of work, quality of work, etc.
--These spreadsheets are created by "Quality Controllers" who oversee a project. Most of the sheets are created from using an old sheet as a template for a new one.
--Each quality controller oversees about 12-14 employees at a time.
--These spreadsheets are not linked to a database at all. They are simply saved as individual .XLS files either on SharePoint (or sometimes locally).
--Generally speaking, the employee base (including the quality control personnel) is not tech savvy and not open to change.

--The Problem is that the data on these sheets is specific only to a given project and does not allow for quality tracking across projects.

--Management is interested in trying to create a form that will link to a central database.
--In order for this to be implemented seamlessly, the end product will need to look very similar to the xls sheets we are currenly using.
--I have researched using excel as a frontend to Access but it doesn't seem advisable.
--Every Quality Controller would have Access installed at their workstation, but very few of them are familiar with the program.
--I am open to a web based solution if easier.

The goal is to create some form of template that a Quality Controller will launch, create a "quality tracking sheet" for a given project but that will link the data back to a centralized database. The more that sheet looks like what we are currently using the better.

I am mostly looking for suggestions of where to start on something like this. Again, I don't have a hard deadline, so I have the luxury to explore various options (and learn skills if needed) to implement a working solution.

Anyone have ideas where to start?

I appreciate any help.

 

[jreedich]

The most important thing to remember is to design the tables in your database properly. Normalize the tables.

I guess some of the questions I would have is
1: Are all the spreadshets the same format?
2: Do you want your application to run in Access, Excel or it's own executable?
3: Do you care if all people involved have access to all other data?
4: Do you want to keep it simple and write a macro for the spreadsheets that will do nothing more than transfer data to the data base?

These are only a few of the questions I would have to start. I know before I begin I talk to the end users. See how they are using the spreadsheets? What they like about the process? What they don't like about the process? I could go on and on but maybe to start you could post one of the sheets.

I don't know if any of this helps but as I said right up front. THE MOST IMPORTANT THING IS THE TABLES DESIGN.

 

[jscranton]

The most important thing to remember is to design the tables in your database properly. Normalize the tables.

This isn't a problem. I am very comfortable with the database design (especially because the design in this case is relatively straightforward).

I guess some of the questions I would have is
1: Are all the spreadshets the same format?

For the most part, the answer is "yes." There are some minor differences but they are not significant.

2: Do you want your application to run in Access, Excel or it's own executable?

This is at the heart of my question. The Quality Controllers are familiar with Excel. I have looked into using Excel as a front-end to Access but the limitations have convinced me to look elsewhere. I have looked into Access' custom forms but they would (a) require training our staff and (b) be met with resistance.

3: Do you care if all people involved have access to all other data?

No. The data is not sensitive. In fact, the ultimate goal is to create reporting functionality so that management can view cross-project productivity and quality.

4: Do you want to keep it simple and write a macro for the spreadsheets that will do nothing more than transfer data to the data base?

I have looked into this but I have not found viable solutions. I would love to keep it simple and use a macro enabled spreadsheet. Do you have suggestions of where to start?

 

[ravi4ever]

Instead of a DB..

Condition : the format of your worksheets should be same all over..
If your main motive is to collate data from all those spreadsheets into one file.. then you can gather all files in a folder and use a macro which loops through all files and while that macro opens the file perform whatever you want to like basically you would be copying data from the file to a master file..

Use this macro..
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1042

use this to open file

Workbooks.Open (xDirect$ & xFname$), UpdateLinks:=False

and this to close file

Windows(xFname$).Activate
ActiveWindow.Close savechanges:=False

like I have used them here
http://www.mrexcel.com/forum/showthread.php?p=2337376

 

[jscranton]

Thanks Ravi.

Your code is very helpful. If seems that if the Excel sheets were standardized then I could use a macro similar to yours to transfer the data to an Access DB rather than a "master" Excel Sheet. This was one of JReedich ideas.

The added wrinkle will be the location of the Access DB since it will have to be on sharepoint.

I will research this solution and report back what I find.

If anyone has already crossed this bridge, I will gladly take suggestions.