new db with 25 items w/ different types of amounts

[Mary]

I have a database I'm trying to setup, it shouldn't be that difficult, but
I'm having a problem figuring out the best way to set it up.

The fields are Item, ItemType, Amount, and Year.

For me the problem is the Amount field. The Amount values differ, they
include types of number, percent, hours, and currency. I initially set this
up with just an amount column set as a double and I thought I could use vba
to display the values based on Item ID number. So Item 1 the amount in the
amount column would be percent, Item 2 the amount would be currency, Item 3
the amount would be a number. But this doesn't seem like it's the best way.

The output needed includes a user friendly form to enter future amounts.
A crosstab query with Year as the column header.
Bar charts for each item showing the amounts per year for that item.
Plus there will be some additional reports.

Thank you for any advice you can give.

 

[John_G via AccessMonster.com]

From the sounds of it, you are using the same table to store different type
of data, and this is not usually a good setup. (This is particularly true if
you are dealing with currency - you generally want to use the "Currency" data
type. ). It is never good design when the type or meaning of data in one
field is dependant on the value in another field.

You really need a separate table for each item type.

Can you give a better idea of what exactly you are wanting to do?

John

 

[Mary]

Thank you John,
It is really a small db and should be simple, but I haven't had much
experience setting up a db and appreciate an help you can give me.
The amount values that will be entered into the db are the totals for the
year (going forward we might include quarters, but for now it's just yearly
totals).
Type Item 2009 2008 2007 Average
N Num. attend 234 210 198 214
N Total hours 10.25 20.45 17.65 16.12
P % completed 0.101% 0.035% 0.0125% .049%
C $ spent $500.00 $480.71 $450.90 $477.20
C $ per day $91.93 $89.98 $81.23 $87.71
I would like a simple form that shows each Item, year and amount that the
user can enter new data into.
I also need to create a crosstab query that would display data similar to
how I have it shown above.
Is it possible to create an "average" column in the crosstab query?
Joann

 

[John_G via AccessMonster.com]

Hi -

From the example data you have shown it looks as if the 5 "items" all refer
to (or are properties of) the same thing - but what what is that "thing" or
in database-ese that entity? A meeting? a project?

When designing a database, the first thing you need to do is determine what
your "entities" are (which will eventually become tables), and what the
properties of those entities are - these "properties" will eventually become
fields in your tables.

Some examples of entities: Cars, Investments, Employees, Stars, CD's .....
this list is endless.

Examples of properties of an entity, for example Cars:
Make
Model
Model year
Colour
Engine power
Purchase Date
Purchase Price
Dealer**
etc, etc

About "dealer" - it too is an entity, with various properties of its own.

Since entities will correspond to tables, each record in that table will
correspond to one occurance of that entity, in this case a car.

Now, you have to be careful sometimes in thinking about what a property of an
entity is. Take for example Employees. Is "Age" a property of an employee?
Well, no it isn't - because "Age" is dependant on (and can be calculated from)
an employee's date of birth. So, the property you would record is DOB, from
which you could calculate Age when you needed to.

So, take a bit of time to look at your data and see what you come up with.
You might be surprised at how many entities you actually have.

Let us know how you do.

Cheers!

John

Thank you John,
It is really a small db and should be simple, but I haven't had much
experience setting up a db and appreciate an help you can give me.
The amount values that will be entered into the db are the totals for the
year (going forward we might include quarters, but for now it's just yearly
totals).
Type Item 2009 2008 2007 Average
N Num. attend 234 210 198 214
N Total hours 10.25 20.45 17.65 16.12
P % completed 0.101% 0.035% 0.0125% .049%
C $ spent $500.00 $480.71 $450.90 $477.20
C $ per day $91.93 $89.98 $81.23 $87.71
I would like a simple form that shows each Item, year and amount that the
user can enter new data into.
I also need to create a crosstab query that would display data similar to
how I have it shown above.
Is it possible to create an "average" column in the crosstab query?
Joann

From the sounds of it, you are using the same table to store different type
of data, and this is not usually a good setup. (This is particularly true if

[quoted text clipped - 26 lines]

 

[Mary]

Thank you John,
This helps. I have re-look at my data, although I feel I'm not setting this
up correctly. I will continue testing some more and hope to come up with a
workable db soon.
Thanks again.

Hi -

From the example data you have shown it looks as if the 5 "items" all refer
to (or are properties of) the same thing - but what what is that "thing" or
in database-ese that entity? A meeting? a project?

When designing a database, the first thing you need to do is determine what
your "entities" are (which will eventually become tables), and what the
properties of those entities are - these "properties" will eventually become
fields in your tables.

Some examples of entities: Cars, Investments, Employees, Stars, CD's .....
this list is endless.

Examples of properties of an entity, for example Cars:
Make
Model
Model year
Colour
Engine power
Purchase Date
Purchase Price
Dealer**
etc, etc

About "dealer" - it too is an entity, with various properties of its own.

Since entities will correspond to tables, each record in that table will
correspond to one occurance of that entity, in this case a car.

Now, you have to be careful sometimes in thinking about what a property of an
entity is. Take for example Employees. Is "Age" a property of an employee?
Well, no it isn't - because "Age" is dependant on (and can be calculated from)
an employee's date of birth. So, the property you would record is DOB, from
which you could calculate Age when you needed to.

So, take a bit of time to look at your data and see what you come up with.
You might be surprised at how many entities you actually have.

Let us know how you do.

Cheers!

John

Thank you John,
It is really a small db and should be simple, but I haven't had much
experience setting up a db and appreciate an help you can give me.
The amount values that will be entered into the db are the totals for the
year (going forward we might include quarters, but for now it's just yearly
totals).
Type Item 2009 2008 2007 Average
N Num. attend 234 210 198 214
N Total hours 10.25 20.45 17.65 16.12
P % completed 0.101% 0.035% 0.0125% .049%
C $ spent $500.00 $480.71 $450.90 $477.20
C $ per day $91.93 $89.98 $81.23 $87.71
I would like a simple form that shows each Item, year and amount that the
user can enter new data into.
I also need to create a crosstab query that would display data similar to
how I have it shown above.
Is it possible to create an "average" column in the crosstab query?
Joann

From the sounds of it, you are using the same table to store different type
of data, and this is not usually a good setup. (This is particularly true if

[quoted text clipped - 26 lines]

Thank you for any advice you can give.

--
John Goddard
E-Mail: jrgoddard AT cyberus DOT ca




.