What are the ideal Relationships for my MS Access database?
July 10, 2011 6:26 AM Subscribe
MS Access Database Relationship Help. I am relatively new user of MS Access and need to figure out the ideal or proper relationship between the tables I have created for multiple company's financial statements that will provide the most flexibility when viewing and comparing the financial statements.
Please suggest the ideal "one to one"; "many to one"; "one to many" and "many to many" relationships since you are an expert and I am confused. These are the tables I have. (If I need to add or remove a table, feel free to make a suggestion.)
Table1(All Companies)
-CompanyX
-CompanyY
-CompanyZ
----Each Company has the following 3 Financial Statements
Table2(Statements)
-Income_Statement
-Balance_Sheet
-Cash_Flow
-----Each Financial Statement has the following 3 Years of data.
Table3(Years)
-2008
-2009
-2010
*I think my goal is to be able to run a query that shows this all at the same time:
All Companies ______ Statements________2008__2009__2010
Company X__________ -Income_Statement____ Data___Data___Data
Company X___________-Balance_Sheet________Data___Data___Data
Company X___________-Cash_Flow___________ Data___Data___Data
CompanyY =same as above
CompanyZ =same as above
Please suggest the ideal "one to one"; "many to one"; "one to many" and "many to many" relationships since you are an expert and I am confused. These are the tables I have. (If I need to add or remove a table, feel free to make a suggestion.)
Table1(All Companies)
-CompanyX
-CompanyY
-CompanyZ
----Each Company has the following 3 Financial Statements
Table2(Statements)
-Income_Statement
-Balance_Sheet
-Cash_Flow
-----Each Financial Statement has the following 3 Years of data.
Table3(Years)
-2008
-2009
-2010
*I think my goal is to be able to run a query that shows this all at the same time:
All Companies ______ Statements________2008__2009__2010
Company X__________ -Income_Statement____ Data___Data___Data
Company X___________-Balance_Sheet________Data___Data___Data
Company X___________-Cash_Flow___________ Data___Data___Data
CompanyY =same as above
CompanyZ =same as above
Okay, let me give you one serious piece of advice before I answer the question:
Install MS SQL Express (free!) and use it. Access lives in this horrible place between Excel and SQL, without the convenience of the former or the power of the latter. Once Excel alone won't do the job, you would do well to skip clear over Access and take the plunge in to the world of "real" SQL, because you'll soon find Access limiting you rather than helping you.
That said, to solve the problem as given (and jeather makes a good point regarding what "Data" means)...
Your first table will have company_id (identity column) and company_name.
You can skip the second table unless you plan to add a more statement types in the future. If so, then do the same thing as for the first table - statement_id as an identity, and statement_name as the value. Enumerations like that cost nothing except a trivial join later on.
For the third table, you want an identity column, company_id, statement_type, year, value. Notice how I always put an identity column everywhere? For the tiny amount of space this wastes, you will never regret having it there later when suddenly you need to link against something you didn't previously expect.
The "value", in that third table, may contain an actual value, or point to an id in another table (or multiple tables), depending on what you meant by a statement.
posted by pla at 7:33 AM on July 10, 2011
Install MS SQL Express (free!) and use it. Access lives in this horrible place between Excel and SQL, without the convenience of the former or the power of the latter. Once Excel alone won't do the job, you would do well to skip clear over Access and take the plunge in to the world of "real" SQL, because you'll soon find Access limiting you rather than helping you.
That said, to solve the problem as given (and jeather makes a good point regarding what "Data" means)...
Your first table will have company_id (identity column) and company_name.
You can skip the second table unless you plan to add a more statement types in the future. If so, then do the same thing as for the first table - statement_id as an identity, and statement_name as the value. Enumerations like that cost nothing except a trivial join later on.
For the third table, you want an identity column, company_id, statement_type, year, value. Notice how I always put an identity column everywhere? For the tiny amount of space this wastes, you will never regret having it there later when suddenly you need to link against something you didn't previously expect.
The "value", in that third table, may contain an actual value, or point to an id in another table (or multiple tables), depending on what you meant by a statement.
posted by pla at 7:33 AM on July 10, 2011
I started typing a response but pla covered what I was going to say. The most important thing is to have a unique ID for each record. Even if no two companies have the same name, you will rue the day that they don't have unique ID numbers.
posted by desjardins at 7:38 AM on July 10, 2011
posted by desjardins at 7:38 AM on July 10, 2011
Also, in future, search online for message boards for help. I'm too far out of the game to know where they are now, but I became known as a wizard while knowing nothing by submitting questions and using the answers. They had to hire a real guy after I left.
posted by Ironmouth at 7:52 AM on July 10, 2011
posted by Ironmouth at 7:52 AM on July 10, 2011
SQL is great, but if you're a beginner, I'd suggest you stay away from it. You can do a lot with Access. The wizards and the template databases that are included with it can help you a lot.
First, make sure you assign a unique ID for each company, as noted above. That's called a primary key, and you'll use it to link between tables. It's nice if it has some meaning (e.g., Joe's Burgers in Georgia = JBG), but not essential - you can just use numbers, and when you use Access wizards it'll ask if you want it to assign a primary key and if you say yes, it'll use an autonumber field.
The big thing in relational database design is normalization, which, roughly speaking refers to techniques to ensure that the data is distributed between tables and that there is little redundancy between tables.
I don't quite understand what you're trying to do, but I would guess that Table 1 would have the unique id, the company name, and certain attributes about the companies, e.g. address, CEO name, whatever. Things that are relatively constant over time.
Not sure you need both Table 2 and Table 3, but that's because I don't understand business relationships. If you have a table with financial data, you can have all that data of one type in it's own table, with the year that applies to that data as one field in the table. So if there are income data, just have it in one (or more) fields and also have the year that that data is from in a different column.
Were you planning to only have annualized data in the database? Or was this going to be transactional data, such that you'd have many different transactions and your query would sum them, grouping years together?
Can cash flow and balance data also be in that same table? Or do they need to be in their own tables? Or can these be derived by combining transactional data? In any event, this is where your one-to-many relationship comes in. One company (in the company table) has many transactions (in the transaction table).
You might want to check out the Access Bible book. It has a series of exercises to construct a database for a veterinary practice, and you can see how the relations are defined (e.g., one owner can have many pets...one pet can have many visits...One visit can have many procedures, etc.)
posted by jasper411 at 8:22 AM on July 10, 2011
First, make sure you assign a unique ID for each company, as noted above. That's called a primary key, and you'll use it to link between tables. It's nice if it has some meaning (e.g., Joe's Burgers in Georgia = JBG), but not essential - you can just use numbers, and when you use Access wizards it'll ask if you want it to assign a primary key and if you say yes, it'll use an autonumber field.
The big thing in relational database design is normalization, which, roughly speaking refers to techniques to ensure that the data is distributed between tables and that there is little redundancy between tables.
I don't quite understand what you're trying to do, but I would guess that Table 1 would have the unique id, the company name, and certain attributes about the companies, e.g. address, CEO name, whatever. Things that are relatively constant over time.
Not sure you need both Table 2 and Table 3, but that's because I don't understand business relationships. If you have a table with financial data, you can have all that data of one type in it's own table, with the year that applies to that data as one field in the table. So if there are income data, just have it in one (or more) fields and also have the year that that data is from in a different column.
Were you planning to only have annualized data in the database? Or was this going to be transactional data, such that you'd have many different transactions and your query would sum them, grouping years together?
Can cash flow and balance data also be in that same table? Or do they need to be in their own tables? Or can these be derived by combining transactional data? In any event, this is where your one-to-many relationship comes in. One company (in the company table) has many transactions (in the transaction table).
You might want to check out the Access Bible book. It has a series of exercises to construct a database for a veterinary practice, and you can see how the relations are defined (e.g., one owner can have many pets...one pet can have many visits...One visit can have many procedures, etc.)
posted by jasper411 at 8:22 AM on July 10, 2011
Also, in future, search online for message boards for help
Access World Forums have provided me with great answers, if not especially timely ones.
posted by desjardins at 8:23 AM on July 10, 2011
Access World Forums have provided me with great answers, if not especially timely ones.
posted by desjardins at 8:23 AM on July 10, 2011
I have to disagree about jumping from Excel to Access. Access is much easier, is perfectly convenient, and unless you really need the power, sufficient. If you do not even understand how to set up a database (as you do not, yet), it's easier to get your feet wet in this environment. Also, if anyone else has to use it, it's easier if they're not terribly comfortable with databases. I set up something for a family business, and it's great, because it's more than powerful enough for basic needs, and it's easy for people who are not terribly technically advanced to use, because I just have a bunch of forms and reports associated with various buttons. I mostly taught it all to myself by googling stuff and making lots of backups.
But really is cash flow just something like:
Company X; 2008; 3,000,000
Company Y; 2008; (500,000)
or do you want all the accounting details? Because if you want the actual accounting details, then do not use Access, and do not use some homebrew SQL, use an accounting package.
Assuming that each piece of accounting information is just a single number, then you need tables as follows:
tblCompany
CompanyID, CompanyName, CompanyDetail
where CompanyID is your autonumber field/primary key, CompanyDetail is some number of fields with things like addresses and phone numbers
tblStatement
StatementID, StatementType
where StatementID is as above, StatementType is Balance Sheet or whatever.
tblData
CompanyID, StatementID, Year, Amt
CompanyID and StatementID are the appropriate numbers from the other tables, and linked to those tables one:many where the many side is always on tblData
Year is just the year of the relevant statement
Amt is the number you want in there
This setup allows room for adding new companies, adding new statement types, etc, and it's pretty simple. You can then create a form to add data, a report to display it, etc. Depending on what version of Access you are using, I can help you with some of this, memail me.
posted by jeather at 8:51 AM on July 10, 2011
But really is cash flow just something like:
Company X; 2008; 3,000,000
Company Y; 2008; (500,000)
or do you want all the accounting details? Because if you want the actual accounting details, then do not use Access, and do not use some homebrew SQL, use an accounting package.
Assuming that each piece of accounting information is just a single number, then you need tables as follows:
tblCompany
CompanyID, CompanyName, CompanyDetail
where CompanyID is your autonumber field/primary key, CompanyDetail is some number of fields with things like addresses and phone numbers
tblStatement
StatementID, StatementType
where StatementID is as above, StatementType is Balance Sheet or whatever.
tblData
CompanyID, StatementID, Year, Amt
CompanyID and StatementID are the appropriate numbers from the other tables, and linked to those tables one:many where the many side is always on tblData
Year is just the year of the relevant statement
Amt is the number you want in there
This setup allows room for adding new companies, adding new statement types, etc, and it's pretty simple. You can then create a form to add data, a report to display it, etc. Depending on what version of Access you are using, I can help you with some of this, memail me.
posted by jeather at 8:51 AM on July 10, 2011
Response by poster: Thanks for all the help. I think I have the idea and will experiment for awhile.
I have been using Excel for these types of tables for years and I was hoping that Access would help me automate the process and ultimately add more flexibility. I have put together a pretty quick system to import data to Access from various Excel files but I was having trouble making sense of it once it was in Access. All of these comments and suggestions will be helpful.
posted by otto42 at 5:41 PM on July 10, 2011
I have been using Excel for these types of tables for years and I was hoping that Access would help me automate the process and ultimately add more flexibility. I have put together a pretty quick system to import data to Access from various Excel files but I was having trouble making sense of it once it was in Access. All of these comments and suggestions will be helpful.
posted by otto42 at 5:41 PM on July 10, 2011
This thread is closed to new comments.
I'm not clear what information is in your financial statements, can you be clearer?
posted by jeather at 7:13 AM on July 10, 2011