Month and Year to Date on a Report

[mmitchell]

I need a Report that shows the Month To Date and the Year To Date per each Category at the top and along the left all of the individual people.

           Life      Annuities  Securities   Group         Total
Agent    MTD  YTD    MTD  YTD    MTD  YTD    MTD  YTD    MTD  YTD

Bob      25   50     0    0      10  30      0     0      35   80
Jim      0    50     10   10     15  45      100   110    125  215
Totals   25   100    10   10     25  75      100   110    160  295

I have attached my database as well as a jpg that shows exactly what report layout I want. The database will open the correct form at startup.

I have made two seperate queries, one for MTD and YTD but I have not been able to "merge" them to make the report that I need.

I even tried the Crosstab wizzard but that did not work for me either.

 

[stopher]

Here's one option...

Take a look at this query (lets call it qryMTDGROUP). If you run it you will see that it's the MTD for Group and an extra column (myColumn) has been added to signify this.

SELECT "aMTDGroup" AS myColumn, T_Split.AgentID AS Agent, Sum(([SplitPercentage]/100)*[CommissionAmount]) AS Amount
FROM T_Split INNER JOIN T_Transactions ON (T_Split.ClientID = T_Transactions.ClientID) AND (T_Split.CatID = T_Transactions.CatID)
WHERE (((T_Transactions.StatementDate) Between DateValue([Forms]![F_IPARevenueDatePickerForReport]![dpMonthFrom].[Value]) And DateValue([Forms]![F_IPARevenueDatePickerForReport]![dpMonthThru].[Value])))
GROUP BY "aMTDGroup", T_Split.AgentID, T_Transactions.CatID
HAVING (((T_Transactions.CatID)="Group"))
ORDER BY T_Split.AgentID;

Now another (lets call it qryYTDGROUP)...

SELECT "bYTDGroup" AS myColumn, T_Split.AgentID AS Agent, Sum(([SplitPercentage]/100)*[CommissionAmount]) AS Amount
FROM T_Split INNER JOIN T_Transactions ON (T_Split.ClientID = T_Transactions.ClientID) AND (T_Split.CatID = T_Transactions.CatID)
WHERE (((T_Transactions.StatementDate) Between DateValue([Forms]![F_IPARevenueDatePickerForReport]![dpYearFrom].[Value]) And DateValue([Forms]![F_IPARevenueDatePickerForReport]![dpYearThru].[Value])))
GROUP BY "bYTDGroup", T_Split.AgentID, T_Transactions.CatID
HAVING (((T_Transactions.CatID)="Group"))
ORDER BY T_Split.AgentID;

This is YTD for group with a different value for myColumn.

Now you can UNION these two queries together

SELECT * FROM QryMTDGroup
UNION
SELECT * FROM QryYTDGroup;

This creates a nice table that you can crosstab. Before you do that you will need to create a temporary table because crosstab can't reference forms. So do a make table based on the above union query.

Then your crosstab query is something like...

TRANSFORM Sum(Amount) AS SumOfAmount
SELECT Agent
FROM tbltempUnion
GROUP BY Agent
PIVOT myColumn;

This is the basis for your report and you can add you own column headings in line with what they are in the crosstab. You'll need to create the other Queries cMTDLife, dYTDLife etc. Note I have put a,b,c,d in front of the column reference. The reason is so that the crosstab orders the columns in the desired order. You'll also need to do a pair of queries for Total. Once you've done all the queries you can union them. In fact you can put them all in one union query for neatness (I don't think you'll exceed the script limit(?)).

Obviously creating a temp table introduces a stepped approach. However, you can use macros or code to make this appear as a single step.

hth
Stopher