SoonerJen said:
I have a field for monthly utility cost. This gets updated every
month. I want to be able to update this every month without
overwriting the previous month's amount. (This way I can look at the
end of the year to see the fluctuation.) Is there any way to do this?
You're going to need another table, to hold each month's cost. That
table would have a field for the cost, plus a compound primary key
consisting of the field that is the primary key of the main table (to
which it is related many-to-one), and a field or fields identifying the
month to which this cost applies (this will probably be a field for the
month number and a field for the year). Or else you might use a single
effective-date or bill-date field, instead of a month field and a year
field; it depends on the details of the system you're representing with
these tables.
With this arrangement, you don't really need a cost field in your main
table, since the current cost is stored, along with prior costs, in the
MonthlyCost table. A query can return the cost as of the current date.
That's what I would do. Querying can be a bit tricky, though, so if
you're not comfortable building the necessary queries, you may prefer to
keep a current-cost field in your main table and use the BeforeUpdate
event of your form to insert a new record in the MonthlyCost table.
That's not nearly as reliable as storing one datum in one place and
querying to get it, but it can make things a little easier to work with.
