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