Commissions Report

[Wheezy]

I'm creating a sales commissions report, where I need to be able to include a Bonus Summary at the bottom of the Detailed Report.

This Summary must include Amounts: Monthly, Quarterly and Annually.

I have the formulas written for the Bonuses in a Query by Sales Rep:
ex.) =IIf([NetSales]<=5000,([NetSales]*.02))

I can get the formulas to work if I imput the dates into the query manually, but I'd like to create a query that only queries the month of "January" for example for a specific year.
In addition, a query that only queries the Quarter for a specific year, and then the whole year.

I'm in a query nightmare and don't have the experience to get out. Your help is deeply appreciated.

 

[Galaxiom]

DatePart function. It can return derive many different aspects from a date including the quarter.
http://office.microsoft.com/en-au/access/HA012288121033.aspx

DateSerial function can reassemble a date from the pieces.
http://office.microsoft.com/en-au/access/HA012288131033.aspx

 

[Wheezy]

You are the master of all! That did the trick. Thank you kindly for your time and expertise. Merry Christmas.

 

[Galaxiom]

Glad I could point you in the right direction with so little. I wasn't really sure what you needed but I guessed you were just missing the small pieces of the puzzle.

Be sure not to spend Christmas working.

 

[Wheezy]

Okay, I have my query formula working to list the Quarters when each job was sold.
However, when I enter criteria so that only the "First" Quarter is queried,for instance, the commission formula ignores the filter completely.

IIf([NetSales]>=5000,([NetSales]*.02))

The above formula calculates the TOTAL net sales, not just the net sales from the Quarter.
Any tips on how to get it to listen?

 

[Galaxiom]

Need more detail. Can you explain the query and how the IFF gets involved.

 

[Wheezy]

So, the IIf statement I wrote is drawing its value from outerspace, because the Net Sales amount is not filtered or harnessed by criteria, and .01% of $757,000 is not $599,000.

I have attached "snagit" snapshots of what I am looking at.

So, The Reps earn a percentage of what the work that they bring in, if it meets a Quota. By any given time date, I can do a Between Criteria. But I want it to be fixed to a Month, Quarter and Year, so that I can put these running totals on the bottom of the detailed report.
Just like a pay check stub!