R
rmaano
Guest
Here's a brief run-down of my situation:
I work for a company which cleans and repairs rail cars and I've already set up a table and corresponding form to collect the number of cars we ship out and the number of repeat shoppers we get each day. We have an incentive plan where for each car we ship out, we are creditted $30. The incentive plan also states that every year, we are allowed to have up to five repeat shoppers without being penalized; we are then charged $300 for every repeat shopper beyond that point (ie - the sixth repeat shopper and so on for that year). The formula I've been using to calculate the running sum for the repeat shopper penalty in my queries is: "([Sum Of Sum Of Repeat Shoppers]-5)*-300".
I'm having difficulty setting up a query for a report which will:
- list the number of cars shipped and the number of repeat shopper for each month based on a starting date and end date I specify
- give me the amount of money generated each month based on the above-mentioned incentive plan guidelines
- give me a final total of the profit/loss generated
I've tried setting this up several times already but haven't been successful. One of the problems I'm running into is that if I enter date parameters where fewer than five repeat shoppers are counted, we are creditted $300 per car less than five total. How do I set up a formula that subtracts $300 from our profits only if there are more that 5 repeat shoppers within the date parameters entered?
I work for a company which cleans and repairs rail cars and I've already set up a table and corresponding form to collect the number of cars we ship out and the number of repeat shoppers we get each day. We have an incentive plan where for each car we ship out, we are creditted $30. The incentive plan also states that every year, we are allowed to have up to five repeat shoppers without being penalized; we are then charged $300 for every repeat shopper beyond that point (ie - the sixth repeat shopper and so on for that year). The formula I've been using to calculate the running sum for the repeat shopper penalty in my queries is: "([Sum Of Sum Of Repeat Shoppers]-5)*-300".
I'm having difficulty setting up a query for a report which will:
- list the number of cars shipped and the number of repeat shopper for each month based on a starting date and end date I specify
- give me the amount of money generated each month based on the above-mentioned incentive plan guidelines
- give me a final total of the profit/loss generated
I've tried setting this up several times already but haven't been successful. One of the problems I'm running into is that if I enter date parameters where fewer than five repeat shoppers are counted, we are creditted $300 per car less than five total. How do I set up a formula that subtracts $300 from our profits only if there are more that 5 repeat shoppers within the date parameters entered?
