Comparing data to the previous five days...

[Moxioron]

Hello there.

I have created a query that reflects a summary of the volume by account for a specific range of dates. This query works great.

I have a request to compare this summary to the previous five day average volume. I am not even sure where to begin.

Any suggestions on where to start on something like that? My current SQL is below. Thanks for your help.

SELECT [tbl_Incoming Returns_FORMATTED].CBAcct, [tbl_Incoming Returns_FORMATTED].CustomerName, [tbl_Incoming Returns_FORMATTED].CustomerName2, [tbl_Incoming Returns - Remotely Created Check Customers].RCC, Count([tbl_Incoming Returns_FORMATTED].Amount) AS CountOfAmount, Sum([tbl_Incoming Returns_FORMATTED].Amount) AS SumOfAmount, Sum(IIf(Left([tbl_Incoming Returns_FORMATTED].[Redeposit],2)="**",0,IIf(Left([tbl_Incoming Returns_FORMATTED].[Redeposit],1)="*",1,0))) AS CountOfRedeposit, Sum(IIf(Left([tbl_Incoming Returns_FORMATTED].[Redeposit],2)="*",[Amount],0)) AS SumOfRedeposit, Sum(IIf(Left([tbl_Incoming Returns_FORMATTED].[Redeposit],2)="**",1,0)) AS CountOfRedeposit1, Sum(IIf(Left([tbl_Incoming Returns_FORMATTED].[Redeposit],2)="**",[Amount],0)) AS SumOfRedeposit1, [CountOfAmount]-[CountOfRedeposit]-[CountOfRedeposit1] AS Expr1, [SumOfAmount]-[SumOfRedeposit]-[SumOfRedeposit1] AS Expr2
FROM [tbl_Incoming Returns - Remotely Created Check Customers] RIGHT JOIN [tbl_Incoming Returns_FORMATTED] ON [tbl_Incoming Returns - Remotely Created Check Customers].AccountNumber = [tbl_Incoming Returns_FORMATTED].CBAcct
WHERE ((([tbl_Incoming Returns_FORMATTED].Date) Between [Start Date] And [End Date]))
GROUP BY [tbl_Incoming Returns_FORMATTED].CBAcct, [tbl_Incoming Returns_FORMATTED].CustomerName, [tbl_Incoming Returns_FORMATTED].CustomerName2, [tbl_Incoming Returns - Remotely Created Check Customers].RCC;

 

[xenou]

It's hard to say. One thought is to create a temp table that is filled with the last 5 days average - basically, run a short set of queries to create the dataset you want. This can be simpler than trying to create one megaquery that does everything. I have scripted such processes with a form to run it (user clicks a button, queries run that create the dataset, and then show the result).

The alternative is to create a second query that pulls the five day average and then join them on whatever field appropriately links up the two figures of interest.

ξ