Unanswered: Data operations in Access and database set up
I'm creating a new database upgrading from what was begun with excel. As a background, the database will have multiple (~20) funds/organizations with information on income, expenditure and other financial data over 30 years. There will also be a section with who gave what on the income side (~200 countries/organizations). I've gone through a few tutorials and read advice on forums but I have two questions for before I actually set it up.
First I want to double check if Access is the right database for me to use. I believe so, but thinking about operationability, I realize I don't know how, or if there is a way, to take a column of currency data and multiply it by another (i.e. the inflation adjustment) to automatically enter a new column of data. I've gotten pretty good at working with my data in excel and am starting to worry that I won't have much of the same functionality in Access. Is there a way to manipulate data like above in Access?
The other issue I'm having is in organizing the database and relationships. I'm thinking the best thing to do is make separate tables for each fund and have a column specifying the year, but I don't know what the key column should be. Does anyone have an example of a good database set up which includes similar data over an extended period of time for several categories? I haven't managed to find any.
Any advice on either of these issues would be greatly appreciated!
Take a look at MS-Access sample database (Northwind.mdb) provided with Microsoft Access Software. If you are using MS-Access Version 2003 or earlier you will find this at C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.
Display the Relationships from Tools Menu and learn how the Tables are related to one another. You have plenty of examples on Forms, Reports, Queries to give you enough ideas to plan your own project.
In Microsoft Access 2007 there is a Template of this sample database. Select the Local Template or Sample from Office menu to create Northwind2007.mdb