How to combine 1) Company with 2) Yearly financial data

[Heejoe]

Hello,

I am quite puzzled at the moment. I am doing an Internship where I am responsible for creating a database with about 150-200 companies in it. There are currently 6 tables in my database. One table holds the primary key (text= Company name, table name= Company details) and links to other tables holding specific information like; brands; location of production plants and financial data.

Now the challenge. I have to come up with a report with company details AND financial information for about three years. My idea was to create a 7th, 8th and 9th table with a primary key linked to the "company details", holding 2007 and 2008 year-end financial information.

After I leave somebody else with little access knowledge must be able to update this database with, for instance, 2010 data.

Any ideas on how to keep the design simple and easily updatable? I am sure I am not the first who would like financials for multiple years shown for 1 company in a form right?

Any suggestions are welcome.

Thanks a lot!

 

[PHV]

You don't want a table for each financial year, do you ?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Heejoe]

Dear PHV and other tek-tips users,

I have read the article.
Looking at the normalizing steps I now believe it would be usefull to indeed have a table holding financial information per year. Every company from the main table has one record in each "Financial year" table. This probably makes the most sense.

This does however leave me with an update problem. I am capable to fill the tables at this moment. When I leave, my seniors must be able to add financial information or change it for new ones. E.g.: In 2011 they need to update the 2008-2009 information with the 2010 results.

I just thought that this must have been done before and wondered if there is a process for this.

Ultimate solution would be a "button" which starts a code that can add a new table (year) based on previous years.

However this might be a topic for a different forum I believe.

Do you agree that indeed a table holding information ordered per year makes the most sense? It also makes it easier to update the financials in one go (new exchange rate for example).

Thanks a lot for your time.

Haio

P.S. I am new to forums. If I am inappropriate please let me know so I can learn. Have read the Faq's though.

 

[GKChesterton]

You are using the forum in exemplary good form. (Correct choice of forum "room", succinct accurate subject/topic, engaged and communicative.)

I am sure I am not the first who would like financials for multiple years shown for 1 company in a form right?

No. Witness the accounting software industry ... which incidentally explains why there aren't more people building their own such!

The rules of good relational design as cited by PHV are holy. Most developers (as myself) have paid a memorable price in do-over work when we flouted them.

It should be understood, though, that ongoing time-based data are problematic -- especially financial data. I never was satisfied with my "home checking account" app in Access.

Any ideas on how to keep the design simple and easily updatable?

For the Design, follow the best practices as cited. For the Data (as in "bosses will maintain the data"), you indicated the need for some serious form automation. Know this: form automation in Access is an increased magnitude or two in learning and work. With even minor complexity, using form automation to compensate for a user's complete ignorance of Access is impossible.

Do you agree that indeed a table holding information ordered per year makes the most sense?

No, not as described. If this were Excel you'd have separate worksheets. But it's a database, so you need to revise your mental model.

Excel may be the better choice for this project.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[Heejoe]

Thanks a lot GKChesterton.

This part of the database, thus, will require some more thinking by my superiors. The rest of the database is functioning correct.

Just this piece needs attention. If they insist I will link to excel OR set-up tables for the coming few years on trial and error. It is sloppy work (don't like that!) but I don't really see another option.

PHV and GKChesterton, thanks for your efforts.

Haio

 

[GKChesterton]

Good luck! I undoubtedly exaggerated with "With even minor complexity, using form automation to compensate for a user's complete ignorance of Access is impossible." I meant of course, " ... given GKChesteron's skill level." Ha ha. Really what I mean to communicate is that the dev. hours go up exponentially as one enables user convenience and fool-proof-ness.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​