iif query help

[mspicka]

Hello,


Fairly new to Access and I am not able to locate through google what I am looking for... I have a Commission query I am trying to manipulate.

Commission query - I have GrossCommission and Salesman columns. I have three different Salesman and need to split the commissions accordingly.

PB gets 100% of his GrossCommission, 50% of PB/MS GrossCommission and 30% of MS GrossCommission

MS gets 70% of his GrossCommission and 50% of PB/MS GrossCommission and 0% of PB GrossCommission

I will keep searching on google. But if anyone can point me in the right direction that would be helpful.

M

[aytee111]

That data needs to be stored on a table, maybe the salesmen table. Then each value will be a straight calculation in the query, you won't have to worry about what the values are or who the salesman is. You need to think of the future, new salesmen added, changes in the percentages, etc. Far easier to do thru tables.

[Micron]

...and the calculations done on a form - where each of 3 textboxes displays the commission calculated based on the commission percentages per salesman. I think it would be easier than doing so in a query, as long as you don't expect to be creating new commission types. IIF's are out of the question. For each new or additional salesman, all query expressions would have to be re-written, and would only get more complex.
Also, this may be one of the "rare" cases where it's ok to store calculations in a table.

[mspicka]

That data needs to be stored on a table, maybe the salesmen table. Then each value will be a straight calculation in the query, you won't have to worry about what the values are or who the salesman is. You need to think of the future, new salesmen added, changes in the percentages, etc. Far easier to do thru tables.

Thank you, I agree with you that I needed to add those numbers directly into the CommissionTable. Next question...

PB Sales = $1000
MS Sales = $500
PB/MS Sales = $5000

PB Total Sale = $1000 + $5000/2
MS TotalSales = $500 + $5000/2

Can I run this calculation from the Report or do I need to work it from the query side. These are Totals I want in the footer.

Thank you.

[Micron]

You should be able to put simple expressions like that into unbound textboxes in the report footer - as long as the values are part of the records. Without knowing the report design, you may have to play with it a bit. There are options you may need to invoke, such as sum/count over group (from the report grouping/sorting menu) or running sum (in property sheet).