Table

  • Thread starter Melissa needing help!!
  • Start date
M

Melissa needing help!!

I have fields called Event Date and Approx. number of people, along with more
fields, i want to insert another field called Approx. number of people per
month. Is it possible for me to get this field to add the number of people
per month. How will I do this?

Thanks
 
V

Van T. Dinh

Even if it is possible, you shouldn't add the "PerMonth" Field.

Why would you want to add a monthly value to the Record that store data
relevant to a particular date? This certainly violates the 2nd Normal Form
of Database Design.

Besides, the "PerMonth" Field sounds like a calculated value and calculated
values should not be stored in the Table, in general since storing
calculated values can lead to inconsistencies later.

Suggest you find some more info on Relational Database Design Theory. Most
libraries have books on RDDT.
 
M

Melissa needing help!!

Thank you

Van T. Dinh said:
Even if it is possible, you shouldn't add the "PerMonth" Field.

Why would you want to add a monthly value to the Record that store data
relevant to a particular date? This certainly violates the 2nd Normal Form
of Database Design.

Besides, the "PerMonth" Field sounds like a calculated value and calculated
values should not be stored in the Table, in general since storing
calculated values can lead to inconsistencies later.

Suggest you find some more info on Relational Database Design Theory. Most
libraries have books on RDDT.
Click to expand...
 
P

Paul Mason

Hi Melissa,

This Van Dinh dude needs to chill out...

Add year and month columns to your table (both integers). If you're using a
form use the event date controls "after update" event to calculate both.
Now you can write a query/report that you can group on the new year/month
columns and avg the number of people column. In sql this is :

select year, month, avg(number of people) as avg people from mytable group
by year, month.

Alternativelly you can write queries that calculate the year and/or month
from the event date column and base a second query on that. The relevant
functions are Year() and Month()...they both take a date as a parameter.

This should put you in the right direction at least.

Mr Van Dinh...There are times, and this is one of them, when it is wiser and
often easier to ignore the various, so called, rules of database design.
You might have at least attempted to answer the question, rather than
getting on your high horse.

Cheers...P
 
M

Melissa needing help!!

Thank you so much Paul. You have answered my question, that will work great
for me. I appreciate your help very much.

Thanks again,
Melissa
 
V

Van T. Dinh

If you look at my answer, I directly addressed the O.P.'s question wether to
add the field "Approx. people per month" or not.

My perception about Relational Database Design Theory is that everyone can
learn it and anyone doing database design should know about it. There needs
not be "high horse" since it is common knowledge for people who wants to
know and are willing to learn. I advise people how I do things in databases
I work on & earn my living from, not from toy databases.

Your first suggestion add nothing to the information content to the
database. It is simply a waste of storage space and additional code for
nothing.

If you use your second suggestion on a Table with 1 million Records, the
Year() and Month() functions have to be called a million times each while it
could have been done with 2 calls to another suitable function. Think of
the efficiency of the database(s) you create.

If you work in your database development area, ask your boss whether your
suggestions are the correct way to go ...

God helps clients that you develop databases for and good lucks with your
career as a database developer since you will need lots of lucks ...

BTW, the Avg you proposed will give the average people per Event Date, _not_
"per month" as per the O.P.'s question. This proved how much you know ...
 
Top