Newbie to databases, need advice (1 Viewer)

[isaac12345]

Hi all,

I am recent computer science and electronics engineering graduate. I recently got hired part time as an IT assistant for a small company that does sub-contracting for the construction industry.

Basically the company comprises of 7 people (including me) almost all of whom are not tech-savvy. The company has many contracts that it works on and is keeping track of all its data through LOTS of excel files. Each person, like the accountant, surveyor,etc., have their own excel files alongwith other data that is generated. All these files are stored on a shared drive.

Now the problem is that although excel is a powerful tool it is getting quite cumbersome to use for the organisation's purposes. The employees including the director spend quite a bit of time moving, organising and manipulating data around in excel sheets to derive what they need. And this is where I come in. The director would basically like me to streamline things as much as I can before the end of august (when my contract finishes).

My background has been programming in JAVA and C and some other electronics design stuff. I majored in my final year with a JAVA desktop application therefore have fairly decent experience with it. But because of the mix of my course I never developed databases.

Now I first thought that excel macros could do the trick but have now realised that they wont cut it. I have thus come to the conclusion that a small sized database would be more useful in streamlining things. My idea is that the database backend could serve as a repository for all the raw data that is used to generate all those excel files. The raw data would come from some 'source' excel sheets that represent data for different parts of the business, like labour, materials,sales,etc. This would save time inputting all the data back in. And then I could create front-ends specific to each job role that could help the user to interact with the database and get the information they need.

Considering my experience and that I have only till the end of August, I would really appreciate if the community could guide and help me on the following -

1) Which tool would be the best to use considering my skill level, time limit and requirement? I would prefer something that is powerful yet easy to work with, hence I am leaning towards Access.

2) I read that Access is not good with handling concurrent users. Is that so? I plan to distribute the front-end to about 7 people.

3) Would I be better off using an Access front-end with a more powerful backend like mySQL or MS SQL or something else?

4) Are there any good design and tutorial guides? At the moment, for access I am using youtube tutorial videos.

My plan at the moment is to first collect use case requirements from 2 people, director and commercial, and then import the raw data excel files into tables and 'clean' them up.

Any suggestions are most welcome

 

[Mihail]

Welcome to the forum !

1) Which tool would be the best to use considering my skill level, time limit and requirement? I would prefer something that is powerful yet easy to work with, hence I am leaning towards Access.

Based your limit term (end of August) I can say that is no "easy" tool for you.
Of course, because you post in an Access forum, we all are more comfortable with Access. THIS is the easy tool from our view point.

2) I read that Access is not good with handling concurrent users. Is that so? I plan to distribute the front-end to about 7 people.

False. Access can handle... 7 users .

3) Would I be better off using an Access front-end with a more powerful backend like mySQL or MS SQL or something else?

Maybe. But you need to learn two programs.

4) Are there any good design and tutorial guides? At the moment, for access I am using youtube tutorial videos.

A tutorial is good if you understand it.
My advice (and I'm sure that even the experts will recommend this) is to learn about databases normalization. Is nothing more important than this. Here is a possible start point.
Without understanding the concept of normalization, you will fail to design a database (not depend from the tool you use).

One more thing: Start NOW !

Good luck !

 

[pr2-eugin]

Hi all,

Hello isaac12345, Welcome to AWF.. Glad to have you on board.. I have or should I say had the exact similar background as yours, to start with.. On top I was not good at Excel either.. Dit not know what Macros were.. So I understand your dilemma..

1) Which tool would be the best to use considering my skill level, time limit and requirement? I would prefer something that is powerful yet easy to work with, hence I am leaning towards Access.

To be honest, MS Access provides a lot more than you think.. So many misconceptions about this great software has put it aside..

Well there are powerful tools like SQL Server, but simple and easy solution can be obtained with Access.. Atleast much more than what Excel can give you.. So I would ask you to go ahead with Access..

2) I read that Access is not good with handling concurrent users. Is that so? I plan to distribute the front-end to about 7 people.

Not completely TRUE.. Access can handle upto 255 concurrent users.. If the DB is properly Normalised and coded properly, there should not be a problem Accessing information..

3) Would I be better off using an Access front-end with a more powerful backend like mySQL or MS SQL or something else?

Sure, but are you ready for this dramatic switch given your admittance of technical expertise? I am not putting you down.. This involves a lot more learning.. So start with something simpl and when the time is right.. Or if your contract is extended you can learn a bit more and move to a better platform..

4) Are there any good design and tutorial guides? At the moment, for access I am using youtube tutorial videos.

There are plenty out there.. The main you need to look into is Normalization.. The important and fundamental concept of Databases..

My plan at the moment is to first collect use case requirements from each person and then from there work on further design issues.

Good start.. In DB your focus should be more on the Data.. So Table design is where you need to start rather than the front end.. Fornt end is the way you gain access to the Data... So designing a good table is the key.. Backend/Tables need to be structured properly and Front end can be flexible to be built around the solid structure..

Just focus on Design, if you are lost post back I am sure people here will be happy to help.. Good Luck..

 

[isaac12345]

Thank you for your replies and links. I have reading about normalization and has been very informative so far. I was looking for some kind of guide to help me through it as I was getting an intuitive feeling that I would somehow need to organise this particular raw data excel sheet, that I imported,appropriately.

One thing I am confused about is though to where to start, should I sit down and gather use-case requirements first OR start with backend design first?

 

[pr2-eugin]

Use Case analysis, concentrates more on the Interaction between the Actors.. More of a Front end design analysis.. Which I mentioned is important, but more importantly Backend or Table design should take priority when it comes to Database design.. Take the excel sheets, look how data is organised, see how it can be optimised.. How they can be related to each other..

 

[Mihail]

Unlike the experts that will explain you the theory, I'll try to show you my approach:

1) First, I think about requests:
At what questions should answer my database ?

2) Second, I think to the data:
What is the minimum set of data that I can use in order to answer to this questions ?

3) Finally I think to the methods:
How, manually, I can run from the data to the answers.

After I have a good understanding of all this, I am able to start to design the database.

 

[way2bord]

Hi all,

I am recent computer science and electronics engineering graduate. I recently got hired part time as an IT assistant for a small company that does sub-contracting for the construction industry.

Basically the company comprises of 7 people (including me) almost all of whom are not tech-savvy. The company has many contracts that it works on and is keeping track of all its data through LOTS of excel files. Each person, like the accountant, surveyor,etc., have their own excel files alongwith other data that is generated. All these files are stored on a shared drive.

Now the problem is that although excel is a powerful tool it is getting quite cumbersome to use for the organisation's purposes. The employees including the director spend quite a bit of time moving, organising and manipulating data around in excel sheets to derive what they need. And this is where I come in. The director would basically like me to streamline things as much as I can before the end of august (when my contract finishes).

My background has been programming in JAVA and C and some other electronics design stuff. I majored in my final year with a JAVA desktop application therefore have fairly decent experience with it. But because of the mix of my course I never developed databases.

Now I first thought that excel macros could do the trick but have now realised that they wont cut it. I have thus come to the conclusion that a small sized database would be more useful in streamlining things. My idea is that the database backend could serve as a repository for all the raw data that is used to generate all those excel files. The raw data would come from some 'source' excel sheets that represent data for different parts of the business, like labour, materials,sales,etc. This would save time inputting all the data back in. And then I could create front-ends specific to each job role that could help the user to interact with the database and get the information they need.

Considering my experience and that I have only till the end of August, I would really appreciate if the community could guide and help me on the following -

1) Which tool would be the best to use considering my skill level, time limit and requirement? I would prefer something that is powerful yet easy to work with, hence I am leaning towards Access.

2) I read that Access is not good with handling concurrent users. Is that so? I plan to distribute the front-end to about 7 people.

3) Would I be better off using an Access front-end with a more powerful backend like mySQL or MS SQL or something else?

4) Are there any good design and tutorial guides? At the moment, for access I am using youtube tutorial videos.

My plan at the moment is to first collect use case requirements from 2 people, director and commercial, and then import the raw data excel files into tables and 'clean' them up.

Any suggestions are most welcome

Fact: You have < 2 months. This is not a significant amount of time.

A few things to keep in mind, beyond the technical specs:

You are building for an audience that, more than likely, has less experience with MS Access than you do. Result: your GUI must be more user friendly - more robust. Thus a longer anticipated development time, or an inferior product. Do you have the time?

Additionally, you must get buy-in for your product if you expect others to use it. This means the visuals must meet everyones accepted level of comfort - and if no one is as tech savvy as you - why would they want to learn a brand new software (MS Access) when they are already comfortable using Excel? Would your time be better spent enhancing existing processes (macros, scripts, data normalization in excel, communication tools) or developing a new one?

What are your goals? If "Streamline current processes" is your objective, I think you'll have a very hard time creating a database with nothing more.

Before devoting your time and efforts to reinventing the wheel (albeit a BETTER wheel), be sure you're not better off just adding air to the tires you're already using.

Addendum: A simple robust database can easily be created in 2 months. However, if you are taking 7 complex processes, attempting to unify the processes, normalize the resulting data, create appropriate forms, queries, user friendly menus, report outs, etc. -- you're in for a world of hurt.

 

[Pat Hartman]

You've had a lot of good advice so far. Here's some more
1. Never loose sight of the fact that Access is a RAD environment. Just because you know how to program, don't just automatically drop into code to solve a problem.
2. Use queries, methods and properties. If you still can't solve the problem, write code.
3. Access is intended to be used with bound forms and you can create a maintenance form with ZERO code. The code that you will add, should be for validation and should go primarily in the FORM's BeforeUpdate event.
4. Before you jump into forms/reports/code, validate your schema. Relational databases are very different from spreadsheets. In Excel, you add columns. With a relational database, you add rows.
5. Be on the lookout for "repeating groups". The obvious ones are things like months of the year, days of the week, or items that you need to assign a numeric suffix - Preference1, Preference2, Preference3, etc. More subtle groups are things that have different names but are the same type of data such as - Heat, Electric, Insurance, Cleaning, Water, Trash, Snow Removal, Lawn Maintenance. All of these things require their own tables. Some will have definition tables also such as the expenses.
6. Access is and always has been multi-user out of the box. Recommendations on the maximum number of concurrent users varies but 255 is the theoretical max. Seven won't present a problem. The best structure is to separate the tables into what we call the BE (back end) and everything else into the FE (front end). The BE is placed in a shared directory on a server. The FE is distributed so that each user has his own personal copy loaded on his C: drive. There are tools that will help with the distribution but with only 7 people, you can manage without them.
7. The bad press you read about "Access" isn't really about Access at all. It is about Jet or ACE, the underlying database engines. Because Access keeps its own objects in a database (Jet or ACE depending on the Access version), it has become inseparable in the minds of the uneducated. Access is a RAD tool and can use ANY database that supports ODBC. It is not locked into Jet/ACE although it needs them to hold its forms/reports/etc. By the same token, Jet and ACE have a life completely separate from Access. They are database engines and can be used by any application that can work with ODBC. So you will see Win forms, ASP, Java, C, etc all using Jet/ACE without even having Access installed on the computer. At least one tax preparation program out there uses Jet for data storage. It is Jet/ACE that have limitations, not Access and that is because they are file servers rather than true database servers. An Access FE to a SQL Server BE can be distributed to thousands of users and can have as many concurrent users as the SQL Server installation can support. For your purposes, Jet/ACE will be more than adequate. You have a small number of concurrent users and it doesn't sound like you would have enough data to present a challenge for years to come. Jet/ACE can support millions of rows but are limited to 2G in any single database.

 

[isaac12345]

Thank you for your advice guys.I am still quite confused about what exactly they want. They have basically provided me with an excel workbook, with lots of excel sheets. The main excel sheet, called as 'Input', with approximately 20,000 rows, apparently has all the raw data from which all the other sheets in that workbook are derived from. Also, this sheet drives payroll! *gulp*

So I think I'm going to be sitting down with one of the guys and basically ask the following questions -

About the excel sheet that you have provided me,

1) How do you use it?

2) What inputs go into the sheet to produce it?

3) Where do those inputs come from?

4) What outputs are produced from the sheet?

5) Who uses which output?

This should basically help me scope out and understand the problem better. And most importantly determine whether they even need a database right now.
Furthermore, I forgot to mention in my earlier post that my replacement, who is also a graduate, will be joining from the 20th. I am not sure about his/her capabilities but if they are as good or better than myself then there is scope for a longer development time.

Any thoughts?

 

[isaac12345]

So I talked to one of the employees who uses this sheet and understood what is being inputted and outputted from it.Here's what the situation is-

The company has another app online from where it gets data from its various sites about the work that the labour has done so far. This data is downloaded weekly and put in to the excel sheet to update it. Then the boss basically runs rather inconvenient pivot tables to find out what he wants from that excel sheet.

After finding this out, I started breaking down the excel sheet into tables to design into a database and realised that the database,regarding labour, is already on the app. So all what is needed is an easy-to-use user interface that could help query the excel sheet. So I am now thinking of simply linking the excel sheet to the database rather than importing it in, and then making queries through forms, as required. Another reason why I think this would be a better idea is that the converting the excel sheet into a database, would not only re-invent the wheel, but then I would also need to spend time(which I might not have) on making front-ends to input the data and the staff would have to learn this new system as well.

Any thoughts?

 

[Mihail]

The company has another app online from where it gets data from its various sites about the work that the labour has done so far. This data is downloaded weekly and put in to the excel sheet to update it. Then the boss basically runs rather inconvenient pivot tables to find out what he wants from that excel sheet.

Something is beyond my understanding here.
Should Excel file to be updated ? Or this file is used only to extract information ?
In my opinion the approach is very different in this situations.

 

[Mihail]

On the other hand, find out from where they download this file.
Maybe they download in Excel format because they don't know a better method or they are not skilled to use other tool.
Maybe you are lucky and will find a database at the beginning of this chain (in fact I'm pretty sure) and, maybe, you are so lucky to link your Access interfaces directly to that database even as read only.

I've tried to explain you the possibilities and I hope you understand even I have not enough English words in my vocabulary for this situation.

Good luck !

 

[isaac12345]

Mihail,

The excel file is updated manually/by hand, every week from the data from the online app which is downloaded as an excel sheet. Then after updating this sheet, it is used locally to extract whatever information is needed, which at the moment is in other sheets(but same workbook) through pivot tables. Hope that makes more sense.

I think there is a database at the backend of the online app. Is it REALLY possible to link my access interfaces to it? If so, maybe I could create and update a local copy of a database that gets its data from the online app database.

 

[Mihail]

I think there is a database at the backend of the online app. Is it REALLY possible to link my access interfaces to it? If so, maybe I could create and update a local copy of a database that gets its data from the online app database.

In my opinion should be possible. I base this affirmation on the fact that they can extract a copy in Excel format.

There are a lot of very well skilled posters on this forum that can teach you everything you need.
Unfortunately I'm not one.

 

[stopher]

Isaac

Think about what you can sensibly achieve in the time remaining. You don't want to leave handing over a half finished project.

Moreover, if you decide to develop an app in say Access, who will maintain it after you've left? You can't possibly consider every requirement in the short time so someone will want to have some improvements made. If there is nobody versed in Access to do it then users will just fall back to what they can use i.e. Excel.

I can easily develop an Access app in a few days but I have many years of experience. If this is your first database then it will take you some time to get your head round the concepts let along producing a robust app.

What is the priority here? At the start you said it was about better management of the data in multiple spreadsheets. But later it seemed to switch to user interfaces (forgive me if I've mis-interpretted).

I'm still slightly confused as to whether the user input data or whether all the data is drawn from the online tool. If the pupose here is to only report data then you can create some quite elaborate "dashboard" interfaces in Excel.

Note that normalisation and relational design is not the only type of database structure. Relational design is good for databases involving data input as the design ensures good data integrity. However if you are doing a reporting only database then you can look at star schemas and research data warehouses and data mining depending on your needs. It's pretty each to create a data warehouse in Access (possibly just a single un-normalised table) and then just point your Excel (pivots) at the Access file to query it i.e. Your Access file becomes the hub. But of course you mention that there is already a database so maybe this is not necessary.

Chris

 

[isaac12345]

Hi Stopher,

Thanks for your reply. At the moment, my aim is to take this excel sheet and make it easier for the boss to query it. The querying being done right now is through complex pivot tables which will get cumbersome with more data being added in the future. So I thought I could put the raw data from this excel sheet into a database(another option is to simply link the sheet to access) and then provide GUIs to query it. But because I dont have any experience with databases apart from very basic SQL years ago back in school, I thought I could use access to easily create one and the GUIs, then deploy the database on the shared drive and the front ends to the boss.

Also, I am not that worried about handing it over, as I have a replacement coming in who has similar credentials.

The final aim, is to be able to compare the data from this sheet, to data from other different sheets from different workbooks to calculate the progress of the labour by week. I was thinking of importing the different sheets into the database as they are de-centralised accross many folder sorted by project. And then in the future this database could be used instead of many excel sheets. I know that this will take longer than 3 weeks, but I thought I could start getting a few things in place for my replacement to then continue.

What are your thoughts? I would appreciate the advice of someone more experienced at this.

 

[Pat Hartman]

This application belongs in a database. It makes my head hurt to think about comparing data in one sheet to that in another to calculate progress.

The other issue is that the users are taking data from one spreadsheet (downloaded) and rekeying it into another (analysis). You can make an immediate value add by automating this with Access. Then work on replacing the analysis spreadsheet entirely and doing all the analysis in Access. If all the data entry is simply copying from the download and updating the Analysis, your Access app does not need to allow data entry at all. It just needs to provide reports.

See if you can find the schema for the web database where the data is collected. It might give you a start. If the PTB will allow you to link to it and use it directly, all the better.

 

[isaac12345]

Thank you for your reply.

Are there any good references(Books, PDFs,etc) to design databases for beginners? Paper or electronic, either would be fine.

Also, what's PTB?

 

[nanscombe]

PTB - "Powers That Be" perhaps?

Your bosses, the web owners etc.