Advice on database structure to replace multiple spreadsheets

[WembleyBear]

I have been tasked with replacing our Budget & Management Accounts sets of
multiple spreadsheets with an Access database and I'm concerned about getting
the database structure right in the first instance.

On the Budget side, we have a sheet for each branch, and each spreadsheet
has several sheets for various departments, plus an input sheet. Once the
Budget data has been input & agreed for the year, no other data is added to
these sheets. My initial questions relate to the best way to set the tables
up in Access. For example, the spreadsheet for our Brackmills branch, Service
department looks like this to begin with:

Jan Feb Mar etc Total
Days 21 20 21 253
Hours 8 8 8
Holidays 4 2 0
Sick 0.5 0.5 0.5
Training 1 1 1
Techs 11 11 11
Efficiency 95% << Not related to a month column, but appears under
one on spreadsheet


In addition, the spreadsheets contain other values in just one cell that are
input and then used in calculations further down. What would be the best way
of accomodating all this data in tables; having seperate tables for each
branch; for each department of a branch?

Also, how easy is it to refer to the data stored in tables in this manner
for the more complex calculations that will be required later on? e.g. To
show the hours in a given month that we have available to sell the calc would
be (Techs * Days * Hours * Efficiency %) - all straightforward but where best
to store Efficiency - in it's own field?

Some brief advice would be appreciated to help me get a start.

Thanks
Martyn

 

[Golfinray]

One thing you can do in Acces is link Excel files. You can have an Access
table that is linked to an excel file, so it is basically a "copy" of the
Excel file. You would then want to Build your tables as "Normalized" as
posible, that is without duplicating data and in the proper formats, which
Excel sheets rarely are. For example, in Access, you want to be sure that
currency is a currency datatype or you will run into problems later trying to
do calculations. A lot of times in Excel, people just leave currency in text.
The next thing is, unless you will be the only user, you need to split the
database into two parts, a Backend (BE) that resides on your LAN, and a Front
End (FE) that you put on each user's computer so that everyone has access to
the files. I would want to have tables for each branch as that would make
your tables smaller and simpler. As far as ease of use, it is VERY easy to
use if set up properly and worthless if not but it is certainly far better
than Excel, especially for data integrity. I would probably take on look on
google at some "database normalization" sites, and take a look at how to
properly build your tables.