Table: Financial Statements, Multiples Years, Multiple Clients

[askajan]

I am trying to create a table which would include financial data, by year, by client. For example:

Client #1

2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500


Client #2

2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500

Etc.

Should I have headers as such:

Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome

The problem is that there will be several expenses which could make this cumbersome. Before I create this table which will drive my entire analytical framework, I would appreciate input for the gurus on this site. Many thanks.

Alex

 

[YNWA]

No you want a Client table with all your client info (name, address etc..), then you want a table say Revenue.

In Revenue you would have

revID
clientID
Year
RevenueAmount
ExpensesAmount

For Year, on your form you would have a drop down, to select a year which is then stored in the Year field. Then Revenue field on form to store that info and Expenses field to store that.

Then in a query you would do the calculation of Revenue-Expenses. You dont store calculated fields.

You do not want multiple columns for each year as it will generate a huge table and is bad practice not to mention a host of problems that could occur at a later date.

 

[askajan]

Great. Thanks so much for the guidance.

Lastly, could you point me in the direction of reading materials or topics related to creating a dynamic form where the drop down for the year changes the inputs of the form. I had created a drop down for client number and it only changed the client number on the form (i.e., did not change the other fields to match the client).

I just picked up Microsoft's Access 2010 Step by Step. Any other books or sites you would recommend?

 

[askajan]

And RevID would be a primary key, correct?

 

[jdraw]

You need to design your tables.

The first few topics at this site are a great starting point.
http://www.rogersaccesslibrary.com/forum/topic238.html

Here are a number of free video tutorials related to database design concepts.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

 

[YNWA]

And RevID would be a primary key, correct?

Yes it would be your PK, then in relationships you PK in client tbl links to FK clientID in Revenue table.

For the dynamic set up you are asking for might require you to use unbound controls or vba code.

Use a search form instead search Access search form on here, theres loads.

 

[jdraw]

Can you show us your tables and relationships as a jpg?
Here is a free existing data model that may be helpful -- just for concepts.
http://www.databaseanswers.org/data_models/accounting_systems/index.htm