Results 1 to 4 of 4
  1. #1
    kennyritch
    Guest

    SQL Query (AND/OR) (2000)

    Hi Folks

    I'm currently trying to create a tricky commission query/report and I really need some help.

    I need to calculate two commission rates for the sales in my DB - the first calculates 50% commission on the current month's sales and the second calculates 10% commission on all sales prior to the current month.

    I have a date field in my DB called dateBank which, if Not Null means that payment has been made into our bank account. I need to be able to ...

    1) Pull out all the records that have a dateBank value between the 1st day and last day of the current month
    e.g. >= 01/07/01 And <= 31/07/01. 50% commission will be calculated on these records.
    2) Pull out all the records that have a dateBank value prior to the current month
    e.g. < 01/07/01. 10% commission will be calculated on these records.

    How do I do this? I have tried using a single query but I've had no success. Do I need to use two separate queries and then somehow join them together into a single report?

    Any ideas?

    Cheers,
    Kenny.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Query (AND/OR) (2000)

    I tend to use multiple queries and then merge them using a UNION query (see SQL specific)
    In your case I'd suggest 1st query to select any records in this month (get current month using Now() function), another query to get any previous month, then third query to union together results.
    Make sure the fields are the same in each

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Query (AND/OR) (2000)

    The multiple query idea is the the easiest way to do what you want. Copy and paste of similar queries then adjusting the criteria saves me a lot of design time.Then join the two queries using a relationship as long as you have one common field.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Query (AND/OR) (2000)

    Actually, I'd consider a different approach. Create a single query and make the calculation distinction in the report itself. In the calculated control for commission use IIf such as: =IIf([dateBank] < #01-07-01#, [Sales]*.10,[Sales]*.50).

Posting Permissions

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