Results 1 to 10 of 10

Thread: If Statements

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

    Unanswered: If Statements

    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. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Please can you post your table structure.

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    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
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #5
    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. #6
    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. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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$]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    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. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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