1. Registered User
Join Date
Nov 2005
Location
Live Oak, California
Posts
32

Again thanks for the help on my last post. Now I have another question. I have a querry I need to figures amount to bill. If a client has one child the rate is \$16, if two the rate is \$20, if three the rate is \$24. These amounts are multiplied by the number of hours worked.

What I have for fields in the querry is:

Hours (number hours worked)
Hours\$ (this will be the amount billed; if one client the amount is \$16, if 2 clients the rate is \$20 and 3 clients the rate is \$24. clt2 and clt3 has an "X" indicating how should be billed, this info is in a table named Ract)

Hope this make since. Thanks, ahead of time

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi

Cheers

3. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
Since the base rate for 1 child is \$16.00 and each additional child is \$4.00, the Formula is.........

Total = 16 + (Me.FormTextBoxWhereNumberOfKidsIsEntered - 1) * 4
.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you could do your calculation in a query

you would need a table containing the applicable. if say that was defined as
<NoChildren><Description><Rate>
-i'd guess the description would be optional

It would probably be "smart" to defien a relationship aswell

If you then do a join to your customer table where the number of children = <NoChildren> in the new table.

YTOur query can then pull the applicable rate for the number of children.

HTH

5. Registered User
Join Date
Nov 2005
Location
Live Oak, California
Posts
32
Here's my table:

Client Hours CLt2 Clt3
John Doe 3 X
Jane Doe 4 X

This is my Querry:

Client Hours Hours\$ Miles Miles

Hours\$ is Hours * 16 or if clt2 or clt3 is "X" would be 20 or 24

Does that help

6. Registered User
Join Date
Nov 2005
Location
Live Oak, California
Posts
32
Lets try this again.

Table

Client: John Doe
Hours: 3
CLT2: X
CLT3:

Querry:

Client: John Doe
Hours: 3
Hours\$: \$60 (to come from Hours *20, unless CLT3 is "X" (24), if not "X" then 16
Miles: 100
Miles\$: \$34.00
Total: Hours\$+Miles\$

7. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
You can use an iif() statement for your Hours\$ value. I don't quite understand how you're building it. But this is the general idea:

Hours * iif([CLT3] = "X", 24, 16)

I don't know how Miles\$ is calculated either, I assume you're compensating 34 cents/mile which could be calculated as [Miles] * .34

The overall total has to use the full calculation again. You can't use field names that don't exist yet:

(Hours * iif([CLT3] = "X", 24, 16)) + ([Miles] * .34)

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by RBURRELL
Lets try this again.

Table

Client: John Doe
Hours: 3
CLT2: X
CLT3:

Querry:

Client: John Doe
Hours: 3
Hours\$: \$60 (to come from Hours *20, unless CLT3 is "X" (24), if not "X" then 16
Miles: 100
Miles\$: \$34.00
Total: Hours\$+Miles\$
Coo - looks like a contender for a little normalisation. But anyhoo another method for hours:
Code:
`Hours * SWITCH(CLT3 = 'X', 24, CLT2 = 'X', 20, Client LIKE "*", 16) AS [Hour\$]`

9. Registered User
Join Date
Nov 2005
Location
Live Oak, California
Posts
32
This is what I ended up using. You guys are awesome. Thanks for the help.

([Hours] * SWITCH([CLT3] = 'X', 24, [CLT2] = 'X', 20, [ract.Client] LIKE "*", 16)

10. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by RBURRELL
This is what I ended up using. You guys are awesome. Thanks for the help.

([Hours] * SWITCH([CLT3] = 'X', 24, [CLT2] = 'X', 20, [ract.Client] LIKE "*", 16)
Aw - thanks.

If your record alway has a client then I imagine
Code:
`([Hours] * SWITCH([CLT3] = 'X', 24, [CLT2] = 'X', 20, 1=1, 16)`
would be ever so slightly more efficent. Probably only noticable if the table has a lot of records really.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•