Performance against target (1 Viewer)

[Stuart McDonald]

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]));

 

[The_Doc_Man]

You said,

If I start to include these fields within my query Access gets grouchy.

Grouchy means lots of things to lots of people. Do you get an error message? If so, which one?

The way I would approach this is to make two queries.

The first one is the raw sum computation for the qualified sales. It can group by your advisor names. But it has to be a totals query on its own.

The SECOND query is the one where you would do the compare. In it, you would JOIN the totals query to the advisor table based on the advisor's FAN (?)

It is in THIS query that you could do the comparison. In the totals query, doing anything else other than totals is a bit messy. But 'divide and conquer' works for this class of problem.