Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    1

    Exclamation Unanswered: SQL Statement - Column not returning a value

    Lang: ASP
    Database: Access 2000
    Server: W2K/IIS 5

    Overview of the Statements Purpose:

    Calculate price of item by doing quanity*price, if the price is more than 100 then make 100 and that is the sub_total. The total due is sub_total + 40. Other code just displays them correctly on the page.

    Current Statement:

    select user_name,
    iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
    sum(sub_total+40) as total
    from items, orders , users
    where orders.item_id=items.item_id
    and orders.member_id = users.user_id
    group by user_name

    Problem:

    The total column is not returning a value

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Probably has to be

    select user_name,
    iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
    iif (sum(quantity*price)>100,100,sum(quantity*price)) + 40 as total
    from items, orders , users
    where orders.item_id=items.item_id
    and orders.member_id = users.user_id
    group by user_name

Posting Permissions

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