J
JeffT
I have created a database in which we track jobs, cost & payments.
I created a query to calculate salesman commissions for reporting purposes.
However sometimes only partial commissions are paid and I need a way to track
partial payments. Also there may be more than one salesman on a job.
I may be overcomplicating this but I thought I could create a commissions
due table. The problem is how do I get the data from my query into the table.
I tried an append query but ended up with zeros in the amount due column.
I am wondering if a union query would solve the problem and how do I go
about doing this?
This is the query I created:
SELECT qryProfit.[WorkOrder#], qrySalesmanSplits.SalesmanID,
qrySalesmanSplits.SalesmanSplit, [Profit]*[Commission] AS [Total Commission],
[Total Commission]*[SalesmanSplit] AS [Commission Due],
qrySalesmanSplits.DatePaid
FROM qryProfit RIGHT JOIN qrySalesmanSplits ON qryProfit.[WorkOrder#] =
qrySalesmanSplits.[WorkOrder#]
ORDER BY qryProfit.[WorkOrder#];
I created a query to calculate salesman commissions for reporting purposes.
However sometimes only partial commissions are paid and I need a way to track
partial payments. Also there may be more than one salesman on a job.
I may be overcomplicating this but I thought I could create a commissions
due table. The problem is how do I get the data from my query into the table.
I tried an append query but ended up with zeros in the amount due column.
I am wondering if a union query would solve the problem and how do I go
about doing this?
This is the query I created:
SELECT qryProfit.[WorkOrder#], qrySalesmanSplits.SalesmanID,
qrySalesmanSplits.SalesmanSplit, [Profit]*[Commission] AS [Total Commission],
[Total Commission]*[SalesmanSplit] AS [Commission Due],
qrySalesmanSplits.DatePaid
FROM qryProfit RIGHT JOIN qrySalesmanSplits ON qryProfit.[WorkOrder#] =
qrySalesmanSplits.[WorkOrder#]
ORDER BY qryProfit.[WorkOrder#];