designing a database, lots of questions.

T

trent2275

A few months ago, someone asked me to make a database for them. I have not done this before. I started working on it, not realizing the amount of time it could take. Fast forward to now, still not done (working on it on and off.)
I will give everyone the rundown on what it is about first. I am making a database for someone who owns real estate. I am not an expert. He wants what should be a fairly simple database.
I don't know exactly how to do this, so I just made a new database, and new tables with the info. I got almost done, and needed to do the SUM totals. I have not found the easy explanation of how to do this still. I bought the InsideOut book on Access 2002, and bought help software for it also.
I made the first table with these fields:
ID (name of apartment building)
Number of Units
Income (for each building)
Expenses (total for each building)

The first table had each building on it by the way.

I then linked the Number of Units fields to the next table which had each buildings info
ID (Unit Number)
Income (for each unit)
Expenses (for each unit)

The Expenses column was linked to the next table which had the expense details, such as Mortgage payment, Electrice, Taxes etc.
Then the expenses table had the "amounts paid" linked to the last table called 123BuildingChecks. (123Building of course substitutes building name)

By now the database had around 45 tables. This was just for one month of the year.
I know there must be a much better way to do this, but like I said, no experience in this field.

So for 2003, there were actually 12 databases (one for each month)
I just changed the info for the month's income, expenses, etc.

But when I got to doing the SUMS, I started having problems. I needed to basically add up the rows in the income column, then add that to the other 11 months data. I am having a lot of trouble with this. I want to finish this since it is now 2004.

So in summary, He wants the total yearly rental income for each building (about 15 buildings total.) He also wants the expenses added up (separately, like all 12 months of the mortgage payment for one building, but not the mortgage payment added to the electrice bill.)

Please help.
Thanks in advance for help.
 
V

Van T. Dinh

Sorry but I have to say that the Database Structure is badly designed and
implemented. I hope you don't rely on the income from this database!

Certainly, it should be ONE database for continual use, NOT 12 databases for
12 months!

My recommendation: Get a book on Relational Database Design Theory (RDDT).
Shut down the computer / Access and read the book until you understand the
RDD Concepts, RDD Principles and Database Normalisation.

I am not sure about Access 2002 Inside Out book but Access 2003 Inside Out
by John Viescas published by MS Press has Chapter 3 "Designing Your Database
Application" that should give you a start. Perhaps, you should also learn
Chptrs 1 & 2.

There are other books on RDDT. Some are often recommended in these
newsgroups are:

* Rebecca Riordan
Designing Relational Database Systems
Microsoft Press

* Michael Hernandez
Database Design For Mere Mortals

--
HTH
Van T. Dinh
MVP (Access)



trent2275 said:
A few months ago, someone asked me to make a database for them. I have
not done this before. I started working on it, not realizing the amount of
time it could take. Fast forward to now, still not done (working on it on
and off.)
I will give everyone the rundown on what it is about first. I am making a
database for someone who owns real estate. I am not an expert. He wants
what should be a fairly simple database.
I don't know exactly how to do this, so I just made a new database, and
new tables with the info. I got almost done, and needed to do the SUM
totals. I have not found the easy explanation of how to do this still. I
bought the InsideOut book on Access 2002, and bought help software for it
also.
I made the first table with these fields:
ID (name of apartment building)
Number of Units
Income (for each building)
Expenses (total for each building)

The first table had each building on it by the way.

I then linked the Number of Units fields to the next table which had each buildings info
ID (Unit Number)
Income (for each unit)
Expenses (for each unit)

The Expenses column was linked to the next table which had the expense
details, such as Mortgage payment, Electrice, Taxes etc.
Then the expenses table had the "amounts paid" linked to the last table
called 123BuildingChecks. (123Building of course substitutes building name)
By now the database had around 45 tables. This was just for one month of the year.
I know there must be a much better way to do this, but like I said, no experience in this field.

So for 2003, there were actually 12 databases (one for each month)
I just changed the info for the month's income, expenses, etc.

But when I got to doing the SUMS, I started having problems. I needed to
basically add up the rows in the income column, then add that to the other
11 months data. I am having a lot of trouble with this. I want to finish
this since it is now 2004.
So in summary, He wants the total yearly rental income for each building
(about 15 buildings total.) He also wants the expenses added up
(separately, like all 12 months of the mortgage payment for one building,
but not the mortgage payment added to the electrice bill.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top