Counting Changes

[Amigo9]

Hi
Each month I take a snapshot of my membership database and import this table into access in order to produce a summary report. These tables have fields "Membership No", "Branch", "Name" etc etc etc
I have been asked to include the last 3 columns below the net change in membership of a branch caused by members changing branches. For example:

Branch Total Members New Members Resignations Moved Out Moved In Net
Branch A 500 10 5 1 2 +1
Branch B 200 5 12 5 4 -1
Branch C 150 3 3 2 2 0



I have attached a screenshot of part of the design view of the existing query. The code in the "Moved Out" column works ok but I haven't been able to figure out how to calculate the "Moved To" or "Net"

I hope this makes sense. Any advice and or assistance gratefully received

[Perceptus]

Move In could just be the swap of the logic from <> to =

Moved In: Sum(IIF([This MOnth2].[Branch Desc]=[lastmonth2].[branch desc],1,0))

Net looks like you could count the rows returned in the query.

Net: Count([This Month].[branch desc])

[Amigo9]

I tried this but Moved In: Sum(IIF([This MOnth2].[Branch Desc]=[lastmonth2].[branch desc],1,0)) returned the total number of members of the branch not just the movement destinations

[June7]

Think for net need to do two aggregate queries then join them.

Query1
SELECT [Branch Desc], Sum(IIf(Lapsed Is Null, 1, 0)) AS ThisMonthCount FROM ThisMonth GROUP BY [Branch Desc];

Query2
SELECT [Branch Desc], Sum(IIf(Lapsed Is Null, 1, 0)) AS LastMonthCount FROM LastMonth GROUP BY [Branch Desc];

Query3
SELECT Query1.[Branch Desc], ThisMonthCount - LastMonthCount AS NetChange FROM Query1 INNER JOIN Query2 ON Query1.Branch = Query2.Branch;

Query4 shows MoveOut - where ThisMonth.Memb is null, is a loss from last month:
SELECT ThisMonth.Memb, LastMonth.Memb, LastMonth.BranchDesc FROM ThisMonth RIGHT JOIN LastMonth ON (ThisMonth.BranchDesc = LastMonth.BranchDesc) AND (ThisMonth.Memb = LastMonth.Memb);

Query5 shows MoveIn - where LastMonth.Memb is null is a gain from last month:
SELECT ThisMonth.Memb, LastMonth.Memb, ThisMonth.BranchDesc FROM ThisMonth LEFT JOIN LastMonth ON (ThisMonth.Memb = LastMonth.Memb) AND (ThisMonth.BranchDesc = LastMonth.BranchDesc);


Query4 as an aggregate:
SELECT Count(LastMonth.Memb) AS CountOut, LastMonth.BranchDesc
FROM ThisMonth RIGHT JOIN LastMonth ON (ThisMonth.Memb = LastMonth.Memb) AND (ThisMonth.BranchDesc = LastMonth.BranchDesc)
WHERE (((ThisMonth.Memb) Is Null))
GROUP BY LastMonth.BranchDesc;

Query5 as an aggregate:
SELECT Count(ThisMonth.Memb) AS CountIn, ThisMonth.BranchDesc
FROM ThisMonth LEFT JOIN LastMonth ON (ThisMonth.BranchDesc = LastMonth.BranchDesc) AND (ThisMonth.Memb = LastMonth.Memb)
WHERE (((LastMonth.Memb) Is Null))
GROUP BY ThisMonth.BranchDesc;

[Amigo9]

Hi

Thanks June7. There is quite a bit here to take in and to be honest I haven't quite been able to make it work. I suspect this is in part at least because of my data not being completely clean and the example you used was a bit too simple. Over the next couple of days I will clean up my raw data such that the simple model actually fits. Currently your model does not handle new and resigned members independently of members who just change branches. I will remove any new and resigned members from ThisMonth and LastMonth tables and I should be good to go. I will get back to you soon and thank you very much again
Cheers
Amigo9

[Amigo9]

Thanks again. Although your queries did not do exactly what I needed, they were still hugely helpful to me so I could develop the final query myself.

The final code was:

SELECT IIf(Len([ThisMonth].[Branch Desc])>14,Left([ThisMonth].[Branch Desc],14) & "...",[ThisMonth].[Branch Desc]) AS [Branch Desc], Query11.[Total Members] AS [Last Month], Sum(IIf([ThisMonth].[Lapsed] Is Null,1,0)) AS [This Month], IIf([This Month]-[Last Month]>0,"+" & [This Month]-[Last Month],[This Month]-[Last Month]) AS Diff, Sum(IIf([ThisMonth].[Join Date]>=startdate() And [ThisMonth].[Join Date]<=enddate() And [ThisMonth].[lapsed] Is Null,1,0)) AS [New Members], Sum(IIf([ThisMonth].[Lapsed Date] Is Not Null And [ThisMonth].[join date]<startdate(),1,0)) AS Resignations, IIf(Nz([query7].[CountIn],0)-Nz([query6].[countout],0)>0,"+" & Nz([query7.countin],0)-Nz([query6.countout],0),Nz([query7.countin],0)-Nz([query6.countout],0)) AS Moves, Sum(IIf([ThisMonth].[Financial]="Financial" And [ThisMonth].[Lapsed] Is Null,1,0)) AS Financials, Sum(IIf([ThisMonth].[Financial]="Unfinancial" And [ThisMonth].[lapsed] Is Null,1,0)) AS Unfinancials, Sum(IIf([ThisMonth].[Financial]="Pending" And [ThisMonth].[lapsed] Is Null,1,0)) AS Pending, Sum(IIf([ThisMonth].[Membership Type Desc]="Suspended Member" And [ThisMonth].[lapsed] Is Null,1,0)) AS Suspended, Round(([Resignations]*100)/([Total members]),2) AS [Churn %]
FROM ((ThisMonth LEFT JOIN Query6 ON ThisMonth.[Branch Desc] = Query6.[Branch Desc]) LEFT JOIN Query7 ON ThisMonth.[Branch Desc] = Query7.[Branch Desc]) INNER JOIN Query11 ON ThisMonth.[Branch Desc] = Query11.[Branch Desc]
GROUP BY IIf(Len([ThisMonth].[Branch Desc])>14,Left([ThisMonth].[Branch Desc],14) & "...",[ThisMonth].[Branch Desc]), Query11.[Total Members], IIf(Nz([query7].[CountIn],0)-Nz([query6].[countout],0)>0,"+" & Nz([query7.countin],0)-Nz([query6.countout],0),Nz([query7.countin],0)-Nz([query6.countout],0));