Solved

How do I design this Access database

Posted on 2013-06-28
16
360 Views
Last Modified: 2013-07-05
I have been asked to design a database based on the attached.  My problem is how to identify what should be fields in the database.  This database will be joined to another table called department numbers.  I know that the $Variance and % Variance columns are not fields but calculated fields.

The point is each department number will have the Other sources section, Uses section and the other Uses section in a report.  Therefore my question is, should each of the items in the sections be fields?  That's where I am confused.  Could someone help?

Thanks
Kofi
budget.jpg
0
Comment
Question by:Kdankwah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 21
ID: 39285992
Your  budget table would look something  like this:

DepartID
FiscalYear
AccountCode
BudgetAmount  - currency

 
You will have a separate record for each year.  This woudl allow as many years as needed.
0
 

Author Comment

by:Kdankwah
ID: 39286030
How do you seperate them by Other sources, Uses and Other uses on a report like the one I attached.  Should I give easch record a code to be able to seperate them.

Thanks
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39286060
You would handle that with the Account Code.

The Account Codes table would have a field for the "Category" You would have a Category look up table that has records like "Like Other sources", "Uses" and "Other uses". I also added a field for sort order. This way you can control the print/display order.

So far you will have these tables:

1) Departments
2) Budget
3) Account Codes
4) Account Categories
5) Fiscal Years
0
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

Author Comment

by:Kdankwah
ID: 39286064
Can all this be in one table?
0
 
LVL 21
ID: 39286194
Not if you want a properly design database. .All in a single table would make it a spreadsheet not a database.

This advise is based on making a database to maintain the data for many years into the future and/or past history.

When designing a database it helps to understand how it will be used. Would you mind sharing some more details about how it will be used?

Are you designing a database for data entry or  will you be importing the data just to print the report?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39286208
>>Can all this be in one table?
shudder
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them.
0
 

Author Comment

by:Kdankwah
ID: 39286625
For Data entry which will be done once every year.  I understand normalization now.

Thanks
0
 

Author Comment

by:Kdankwah
ID: 39287065
I created the tables and the lookup table.  

To TheHiTechCoach, should I have one master table to join all the tables together?  Which of these tables below will be my master table.

Departments
2) Budget
3) Account Codes
4) Account Categories
5) Fiscal Years

Thanks,
0
 
LVL 21
ID: 39288670
I think in terms of master list tables (look up tables) and transaction (activity)  tables.  The transaction tables show activity for the master list tables.

Transaction table
- Budget

The Transaction table above will use all the following look up tables

- Departments  
- Account Codes
- Fiscal Years

Account Codes with use this look up table:
- Account Categories - Look up table
0
 

Author Comment

by:Kdankwah
ID: 39288674
Thanks for all your help I will set it up and get back to you. I dont want to close the thread now because I may have other questions along the way.  Once again thank you.

K
0
 

Author Comment

by:Kdankwah
ID: 39288689
Attached is the relationship report  of the relationships I just created.  Please take a quick look at for me if I am on the right track.

Thanks
Relationships-report.pdf
0
 
LVL 21
ID: 39288867
You are getting closer.

The Relationship on the one side should be with the primary key. I would expect that to the the ID field in each table.

Normally the Primary key is an autonumber data type. You use this internally for all your relationships. I regularly hid this from the user.

It have always believed that you should never change a primary key. In 35+ years of maintaining 1000+ databases I have never needed to do it when the database was properly designed.

A good example would be departments. What if you need to change the depart name for any reason. Id the Depart name is not the primary key then it is safe to change it. The system will automatically pick up the name change the next time the department name is looked up.

See attached image:
 relationships
0
 

Author Comment

by:Kdankwah
ID: 39291977
Hi,

I did the relationship but I am stuck with one thing, should the data fields in the budget table be txt or number.  

Thanks
0
 

Author Comment

by:Kdankwah
ID: 39291996
I am getting a type mismatch when I change the fields in the tblbudget to text.
0
 
LVL 21
ID: 39292255
I would expect all the foreign key field to be Long.  

You need to use the same data type for the fields used in a relationship.  If the Primary key is an Autonumber then you will use a Long to store the value in a related table. The relationship will be between a autonumber (1 side)  and a Long (many side).
0
 

Author Closing Comment

by:Kdankwah
ID: 39303124
TheHitechcoach is one of the best coaches in this forum.  One is lucky when he responds to your inquiry.  He is very patient and will work with you all the way.  I really, really recommend him.

Thanks
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question