Running sum including Bal Fwd from subreport

[Gina Maylone]

Good morning all from the frozen tundra. I need to create a billing statement that includes a balance forward, if it exists, easy enough, did it with a subreport, but I also need to include that balance forward in the running sum (over all)….is this even possible or am I spinning my wheels? I don't have anything to show because I really don't know what to do! TIA brilliant minds!

[ranman256]

Q1 to pull the prev sum,
Q2 to pull current data,
Q3 a union query of Q1&2, then report on Q3.

[Gina Maylone]

Thanks RanMan, I'm not getting the results I need. Here are my queries.
For current accounts

SELECT AsmtsandAccounts.MemberID_FK, AsmtsandAccounts.UseDate,DatePart("m",[useDate]) AS M, DatePart("yyyy",[useDate]) ASY, AsmtsandAccounts.Note, AsmtsandAccounts.AsmtType, AsmtsandAccounts.DBAmount,AsmtsandAccounts.Details, AsmtsandAccounts.DBDate, AsmtsandAccounts.CRAmount,AsmtsandAccounts.CRDATE, AsmtsandAccounts.BAL, AsmtsandAccounts.EnteredBy,AsmtsandAccounts.CheckNumber, AsmtsandAccounts.Comments, AsmtsandAccounts.CText
FROM AsmtsandAccounts
WHERE(((DatePart("m",[useDate]))=DatePart("m",[Forms]![runassessments]![daterun]))AND((DatePart("yyyy",[useDate]))=DatePart("yyyy",[Forms]![runassessments]![DateRun])))OR (((DatePart("yyyy",[useDate]))=DatePart("yyyy",[Forms]![runassessments]![DateRun])));

For past due accounts (balance forward)

SELECT AccountsBalFWD2.MemberID_FK, Sum(AccountsBalFWD2.BALFWD) ASSumOfBALFWD
FROM AccountsBalFWD2
GROUP BY AccountsBalFWD2.MemberID_FK;

And the union query

SELECT AccountBalFWD3.MemberID_FK, First(AccountBalFWD3.SumOfBALFWD) ASFirstOfSumOfBALFWD, AsmtsandAccountsCurrentMonth.DBAmount,AsmtsandAccountsCurrentMonth.CRAmount, AsmtsandAccountsCurrentMonth.BAL
FROM AccountBalFWD3 INNER JOIN AsmtsandAccountsCurrentMonth ONAccountBalFWD3.MemberID_FK = AsmtsandAccountsCurrentMonth.MemberID_FK
GROUP BY AccountBalFWD3.MemberID_FK, AsmtsandAccountsCurrentMonth.DBAmount,AsmtsandAccountsCurrentMonth.CRAmount, AsmtsandAccountsCurrentMonth.BAL;

And the results for one member;

MemberID_FK	FirstOfSumOfBALFWD	DBAmount	CRAmount	BAL
10	$449.43	$0.00	$449.43	($449.43)
10	$449.43	$56.18	$0.00	$56.18
10	$449.43	$168.53	$0.00	$168.53

What am I missing? I am trying to get the First of the bal fwd with no luck. Thanks again!!

[Gina Maylone]

Ok, I figured out the union query and here it is:

SELECT AccountsBalFWD2.MemberID_FK, Sum(AccountsBalFWD2.BALFWD) AS SumOfBALFWD
FROM AccountsBalFWD2
GROUP BY MemberID_FK;
UNION SELECT AsmtsandAccounts.MemberID_FK, AsmtsandAccounts.BAL
FROM AsmtsandAccounts
WHERE (((DatePart("m",[useDate]))=DatePart("m",[Forms]![runassessments]![daterun])) AND ((DatePart("yyyy",[useDate]))=DatePart("yyyy",[Forms]![runassessments]![DateRun]))) OR (((DatePart("yyyy",[useDate]))=DatePart("yyyy",[Forms]![runassessments]![DateRun])));

That gives me the correct amounts, however, I need additional information in the report and when I link the union query to another query to get that information I get a bunch of duplicates. How do I get rid of them? I need to show all transactions for the current month as line items, the balance forward, and the grand total due (and of course the running sum). Thanks again!

MemberID_FK	Balance Forward	UseDate	Note	DBAmount	CRAmount
10	449.43	1/18/2019	PMT-#5937	$0.00	$449.43
10	168.53	1/18/2019	PMT-#5937	$0.00	$449.43
10	-449.43	1/18/2019	PMT-#5937	$0.00	$449.43
10	56.18	1/18/2019	PMT-#5937	$0.00	$449.43
10	449.43	1/28/2019	March 2019 Monthly Asmt Lot 1255	$56.18	$0.00
10	168.53	1/28/2019	March 2019 Monthly Asmt Lot 1255	$56.18	$0.00
10	-449.43	1/28/2019	March 2019 Monthly Asmt Lot 1255	$56.18	$0.00
10	56.18	1/28/2019	March 2019 Monthly Asmt Lot 1255	$56.18	$0.00
10	449.43	1/28/2019	March 2019 Monthly Asmt Lot 1283	$168.53	$0.00
10	168.53	1/28/2019	March 2019 Monthly Asmt Lot 1283	$168.53	$0.00
10	-449.43	1/28/2019	March 2019 Monthly Asmt Lot 1283	$168.53	$0.00
10	56.18	1/28/2019	March 2019 Monthly Asmt Lot 1283	$168.53	$0.00

The subreport needs to look like this: I probably should have lead with that.

[Gina Maylone]

I took a different route and am using a temp table. Thanks all for the look!