~I need some help designing my first Access Database~

[news.west.earthlink.net]

HI,

I'm trying to design a simple database, and I'm not sure about how to
achieve this next step.

I'm trying to track some projects and billing at work. Some projects are
billed by the hour, and others are billed flat rate.

I have a table for the project info, that has the following columns:

Project ID- Project Title- Hours Spent- Cost Per Hour-

I've created a query to multiply the "Hours Spent" by the "Cost Per Hour" to
give me the total bill for the project. That's no problem.

Now, I need a more "permanent" place to keep this total, so I don't have to
run the query every time I need a total. I also need to account for the
projects that have a flat rate instead of an hourly bill.

For the flat rate, I am going to add a "Flat Rate" field to the table, and
have the query only calculate a total if the flat rate is empty.

But what do I do to store the total cost? I was thinking of having a
seperate table that stores only the Project ID and Total Bill and then uses
an update query to calculate the Total Bill.

I'm doing this on the assumption that I can't just have a calculated field
in my original table. I also need to make it so changes in how we bill in
the future (say, the cost per hour) don't change the existing totals for
past projects. Once it's entered, it should never change, unless I manually
go in and change it.

Hopefully this makes sense! If there's a better way to do this, please let
me know. There's obviously a lot I don't know about Access.

 

[Ken Snell]

Don't store a calculated value in your table when you can just recreate it.
That is what queries are for! And it avoids the need for you to constantly
have to update the tables' data whenever something changes (and believe me,
you usually overlook at least one record/table!).

The time to save such a value is when it is dependent on other values that
might change, and you don't want it to change when those other values change
(e.g., if a cost is based on a price that is effective today, but that might
change tomorrow and you want the cost to be what it was yesterday).

Otherwise, if you really want to save the value, then create a field in the
table to hold it, and then run an update query that will put the calculated
value from the query into that field.