A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

new db with 25 items w/ different types of amounts



 
 
Thread Tools Display Modes
  #1  
Old November 26th, 2009, 12:18 AM posted to microsoft.public.access.tablesdbdesign
mary
external usenet poster
 
Posts: 1,946
Default new db with 25 items w/ different types of amounts

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.
  #2  
Old November 26th, 2009, 02:37 AM posted to microsoft.public.access.tablesdbdesign
John_G via AccessMonster.com
external usenet poster
 
Posts: 23
Default new db with 25 items w/ different types of amounts

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 wrote:
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 Goddard
E-Mail: jrgoddard AT cyberus DOT ca

Message posted via http://www.accessmonster.com

  #3  
Old November 26th, 2009, 04:46 AM posted to microsoft.public.access.tablesdbdesign
mary
external usenet poster
 
Posts: 1,946
Default new db with 25 items w/ different types of amounts

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" wrote:

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 wrote:
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 Goddard
E-Mail: jrgoddard AT cyberus DOT ca

Message posted via http://www.accessmonster.com

.

  #4  
Old November 26th, 2009, 05:46 AM posted to microsoft.public.access.tablesdbdesign
John_G via AccessMonster.com
external usenet poster
 
Posts: 23
Default new db with 25 items w/ different types of amounts

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



Mary wrote:
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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #5  
Old November 27th, 2009, 04:18 AM posted to microsoft.public.access.tablesdbdesign
mary
external usenet poster
 
Posts: 1,946
Default new db with 25 items w/ different types of amounts

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.

"John_G via AccessMonster.com" wrote:

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



Mary wrote:
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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.