S
Stuart McDonald
Guest
I have a sales performance against target calculation required.
I have produced a query which summarises for each 'Advisor' the amount of commission earned for a given year. This has been based on another query which sorts commission by product as only certain products sold are compared against the Advisor's target. I have included a criteria which prompts for the year to be input, resulting in only that years commissions for each Advisor are shown. The targeted year runs from 1 Jan.
I am now stumped as to how to compare the summed commissions in this query against the Advisor's target. I have an Advisor table which is related to an Advisor's target table which records the Advisor's target for each year. If I start to include these fields within my query Access gets grouchy.
As mentioned above the targeted year runs from 1 Jan so I would like a prompt to enter a year which applies to both the summed commission figures and the target year if possible. Otherwise I presume the user would be prompted to enter a year for commissions and a year for the target.
If anyone has time to consider my problem, and suggest some expressions/code I would be grateful.
Thanks.
My current query is:
SELECT DISTINCTROW [For ATQ PIR Comm Query].FAN, Format$([For ATQ PIR Comm Query].[PolWritCommDate],'yyyy') AS [PolWritCommDate By Year], Sum([For ATQ PIR Comm Query].PolWritCommission) AS [Sum Of PolWritCommission]
FROM [For ATQ PIR Comm Query]
GROUP BY [For ATQ PIR Comm Query].FAN, Format$([For ATQ PIR Comm Query].[PolWritCommDate],'yyyy'), Year([For ATQ PIR Comm Query].[PolWritCommDate])
HAVING (((Format$([For ATQ PIR Comm Query].[PolWritCommDate],'yyyy'))=[Year]));
I have produced a query which summarises for each 'Advisor' the amount of commission earned for a given year. This has been based on another query which sorts commission by product as only certain products sold are compared against the Advisor's target. I have included a criteria which prompts for the year to be input, resulting in only that years commissions for each Advisor are shown. The targeted year runs from 1 Jan.
I am now stumped as to how to compare the summed commissions in this query against the Advisor's target. I have an Advisor table which is related to an Advisor's target table which records the Advisor's target for each year. If I start to include these fields within my query Access gets grouchy.
As mentioned above the targeted year runs from 1 Jan so I would like a prompt to enter a year which applies to both the summed commission figures and the target year if possible. Otherwise I presume the user would be prompted to enter a year for commissions and a year for the target.
If anyone has time to consider my problem, and suggest some expressions/code I would be grateful.
Thanks.
My current query is:
SELECT DISTINCTROW [For ATQ PIR Comm Query].FAN, Format$([For ATQ PIR Comm Query].[PolWritCommDate],'yyyy') AS [PolWritCommDate By Year], Sum([For ATQ PIR Comm Query].PolWritCommission) AS [Sum Of PolWritCommission]
FROM [For ATQ PIR Comm Query]
GROUP BY [For ATQ PIR Comm Query].FAN, Format$([For ATQ PIR Comm Query].[PolWritCommDate],'yyyy'), Year([For ATQ PIR Comm Query].[PolWritCommDate])
HAVING (((Format$([For ATQ PIR Comm Query].[PolWritCommDate],'yyyy'))=[Year]));