calculation of fields

[mark]

I am building a management database system for my company. I am using ms
access 2007. For the problem I am receiving is calculating fields for my
billing database. The following fields are part of the structure.

Hours
Hourly Rate
Days
Daily Rate

Sub Total

What I want to do is calculate the fields to give a total in the field Sub
Total.

So
(IF [Hours] = true) then times the [Hours] Field and [Hourly Rate] Field to
Give the Sub Total,
this will also apply to the [Days] field.

Do I have to create a query to run this command or can I enter in the string
via the properties fields.

Please help as I have not used access much before and this is battling me
and it is pretty important that I get this done asap.


Many thanks



Mark Bray

 

[Allen Browne]

This is confusing. You say:
IF [Hours] = true then times the [Hours] Field and [Hourly Rate] Field
The first half of that sentence sounds like Hours is a yes/no field, but the
second half sounds like it is a quantity (number of hours.)

It would really be better to use fields like this:
Qty Number
Rate Currency
RateType Text either "hours" or "days"

Now you can get the total as simply:
[Qty] * [Rate]

If you need the hourly total, it would be:
IIf([RateType] = 'hours', [Qty] * [Rate], 0)

 

[Ken Sheridan]

Mark:

Firstly you should delete the Sub Total column from the table. Its
redundant and as such leaves the database at risk of inconsistent data being
entered. You can, and should, compute the value on the fly in a query or in
an unbound computed control on a form or report.

I'd endorse what Allen has said about the design, but with the columns as at
present (less the redundant Sub Total column) the ControlSource for a
computed control would be:

=(Nz([Hours],0) * [HourlyRate])+(Nz([Days],0) * [DailyRate])

In a query you'd enter the following in the 'field' row of a blank column in
query design view:

Sub Total: (Nz([Hours],0) * [HourlyRate])+(Nz([Days],0) * [DailyRate])

The Nz function returns a zero for Hours or Days if Null. Multiplying zero
by the rate dives zero of course, so the end result is a number + zero, i.e.
the number.

Ken Sheridan
Stafford, England