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.
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?
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
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
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.
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
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.
> 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
|
