If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Advice on database structure to replace multiple spreadsheets
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 -- Excel 2000, Windows 2003 over Citrix PS4 |
#2
|
|||
|
|||
Advice on database structure to replace multiple spreadsheets
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. "WembleyBear" wrote: 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 -- Excel 2000, Windows 2003 over Citrix PS4 |
Thread Tools | |
Display Modes | |
|
|