Solved

How do I design this Access database

Posted on 2013-06-28
16
355 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
  • 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
 

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 48

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Outlook Free & Paid Tools
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now