1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded education professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Database query/report issue

Discussion in 'Computing and ICT' started by scruffycat, Feb 9, 2011.

  1. scruffycat

    scruffycat New commenter

    Right Trying to explain this one as best I can. This is a mock up of the situation

    A query takes all order id's for a product and multiplies by the number ordered and displays sorted by customer e.g.
    surname product name cost quantity total for order
    bloggs pen £5 4 £20
    bloggs paper £10 8 £80
    Smith pencil £1 100 £100
    Smith rubber £1 20 £20
    On the report duplicate fields are hidden and a total per customer is displayed
    e.g. Bloggs Total £80
    Smith Total £ 120
    But now I want to tell the report to only display a particular customer (there is a primary id field) IF their total order is more than £100. As the student will produce a loyalty bonus card scheme for high spending customers
    So I can advise the easy option to sort by Total on the report and put in an unbound IF to display a GIVE LOYALTY CARD statement, but for the life of me cannot see how to define an IF statement to only display a customer whose total order value is more than 100 based in the custmer ID or is there some SQL or vba I should be using?
    Any ideas? Does this make sense?

  2. autismuk

    autismuk New commenter

    Something like

    Select customer.Name, sum(order.quantity*order.cost) as total
    from order, customer
    where customer.customerID = order.customerID
    group by customer ID
    having total > 100

    Having was introduced because you can't put WHERE in a GROUP BY, which is what you are trying to do.

  3. DEmsley

    DEmsley New commenter

    @autismuk - cheers this is a new one on me and very, very useful.

Share This Page