question about Access

I'm pretty new at Access but I'm working on a project for a company that has a system set up in Excel where they keep track of all their employees' hours worked and other data and computes it to determine bonuses and pay and other things; basically standard stuff for Excel. They want me to redo do it so that it's either all in Access or uses both Access and Excel. They figure that way it will be more user friendly and better organized and so forth. However, it don't know that using both applications for this one purposes and going between them is realistic. Also, does Access have the capabilities to compute data like Excel can? It seems to me Excel has to be used because of the formulas and calculations and all that, but maybe I'm not aware of the extent of Access' capabilities. If any of you can reply and just tell me if something like this can sometimes be converted over to Access, or if I'm most likely stuck with Excel, or if maybe there is potential to use both of them, I'd be very grateful. Thanks.

Yes, access is a very intensive platform for carrying out such functions.

You can transfer the excel data into access by using the get external data function on the file menu in access.

Your decision on whether to use access is down to you, but I would consider a couple of things. e.g.
1)Time to complete project
2)Your understanding of relational databases and access

If you decide to go with access then I would suggest sitting down and writing a design/specification for your database.

because the hard work is in the planning of the database.

I hope this answers you question, and for your curiosity have a search of the forum of the ideas you have in mind, to guide through the planning process and to help you achieve your aim.

Andy

I think that Access has the advantage over Excel in this situation because you have multiple users. With the Excel spreadsheet even you have the individual who opens it (if on a shared source) and forgets about it locking everyone else from making any changes. With Access you can take advantage of Excel spreadsheets that already exist and will continue to be used by linking or importing them. For instance, your Hr department will continue to use an Excel spreadsheet to track employees in general (name, SSN, DOB, ect..). You can link to lthat spreadsheet so that you are always certain (within a degree at least) that you have all of the employee information available in the database.

Also, the query function is much more advanced in Access so that you can look at employees in many different way, use queries to remind you when employees are due for reviews, ect..

Although Access is daunting I agree with SpacePro that the planning is where you create a successful database.

I'm sure that if you posted your needs and availability of information sources that the forum would be happy to assist you in the planning.

Autoeng

I vote with the others that Access is the better choice, but with the added warning that it is very easy to get lost in the bowels of Access very quickly.

You surely can tie Access to external Excel spreadsheets, but I question whether that is really the way to go. People can do things to spreadsheets that they cannot do in a database context because Excel just flat-out doesn't care! You can put a header line in a spreadsheet. Excel likes it. But in Access, you cannot put a header line in a table. You put header lines in REPORTS and then populate the report from the table. See the difference? It is a concept thing.

I tried tying a spreadsheet into my Access database once. I couldn't get the folks who fed the spreadsheet disciplined enough to keep it uniform in format. So eventually I had to give up. I wanted to help them, but they didn't cooperate. Now, if I had the authority to force them to use Access, the problem would have been solved, but politics and user "comfort" levels are a valid element of any environment. You cannot forget these two factors when designing any system.

Many questions in this forum have come from the viewpoint of folks converting from spreadsheets to databases. This is NOT as trivial as it looks because there is a mindset issue that is hidden among the bullrushes here.

Basically, despite the fact that Excel can make cross-worksheet references, and relative references, and other such things, it is a flat file. Conceptually, you use it as though it were a flat file. You design it as a flat file. You print it as a flat file. With very few exceptions, you print rows and columns. Oh, sure, with pretty formats and fancy forumulas, but you print rows and columns.

Access allows more than two dimensions to exist, because that is the nature of the "relational" beast. You must start thinking in terms of Access methods, which for some people is a bigger leap than for others. When you print something, it might be rows and columns, but some of what you print can come from lookups or joins to other tables, or summations of other data through an implied query, or all sorts of other complex techniques.

For example, you mentioned "formulas" as an issue. You do not EVER put a formula in an Access data table. All formulas go into queries (or text boxes in a form or report). The idea is that if you have to compute it, you do so on the fly every time you run the query. You only store "concrete" items in the database. This is true whether we are talking display-only information or data used for updating other tables. If it CAN be computed from other data in the database, it MUST be computed - if you intend to maintain proper relational integrity and normalization.

So look before you leap. And don't take the big step if you can't handle the big splash that goes with it.

I concur with all of these folks. I assist Excel users in getting them to consider switching their work from Excel to Access. One of the best ways to teach them the benefits of Access is to have them sort their spreadsheet in some way and then copy it to another sheet and then sort it another way and then copy it again, etc. Then look at the file size. Then change one cell value in Excel and see how much work it is. Or exclude a record for one reason or another. What I have found is that the best Excel folks were very crafty at getting Excel to 'behave' like Access, and that ain't easy.