Invoice populate previous total if date empty...

[MackMan]

Hi all. I've done a search for what I'm trying to achieve below, but there's nothing to give me an idea how I'd go about tackling this.

Basically, I have a couple of clients who've asked to show all invoice activity on a daily basis, when I'm sending out monthly invoices.

I can do the running sum, by group, no problem . but...

I'm struggling to get the sum carried over if the record is empty on a particular day, however, I'd rather Access handle this, rather than have my colleague exporting to Excel and have that handle what I'm after.

The attached img will give you an idea of what I'm trying to achieve, but basically, when there is no record for that particular day (highlighted green) I'd like Access to carry over the previous outstanding invoice amount. Am I right in thinking I'd need a Cartesian query for dates, using the query that's generating the running sum? or am I over complicating things a little? I have tried this, but the information returned is far from correct. Or is it simply a matter of making a couple of adjustments to the formula that's creating the running sum in the first place? If so, I honestly would have no idea how I'd go about it.

As always, any advice (I'm not looking for an answer but a point in the right direction) is greatly appreciated.

 

[Ranman256]

In the query, be sure to make an outer join to show those who had no invoice.
And covert the null to zero....

Nz(TotalField)

 

[Minty]

Why include a date when there is no activity ? By not including the date you avoid your problem.

 

[MackMan]

Why include a date when there is no activity ? By not including the date you avoid your problem.

Customer wants a monthly invoice by all dates to show any activity or not... They pay the bills... so...

 

[MackMan]

Thanks Ranman, will give it go as soon as I'm back in the office!

 

[MackMan]

So I've been working on this for the last couple of days, and it's really bugging me... I know it's something really simple but...

I can get the running sum of the opening balance and all account activity, but where there are days with no transactions, it's showing #Error.

for instance... the BalanceU for 1st, 2nd and 3rd Jan would be -£11676.02 (balance outstanding from customer)

and the 5th would be -£11680.02 and so on...
So the expression in the query (BalanceU) is basically the opening balance from the previous statement, plus any activity.

forgive me. I find this difficult to explain due to autism, and I know some people here get frustrated with me, but I genuinely do appreciate your help

here is my query in sql (I'm using test queries)

SELECT TESTDATESONLYCanbedeleted.Dates, TESTRUNNIGTotalwithEmptyDates.SortOrder, TESTRUNNIGTotalwithEmptyDates.AccountID_FK, TESTRUNNIGTotalwithEmptyDates.EntryID_PK, TESTRUNNIGTotalwithEmptyDates.PayeeName, TESTRUNNIGTotalwithEmptyDates.CatSub, TESTRUNNIGTotalwithEmptyDates.Credit, TESTRUNNIGTotalwithEmptyDates.Debit, TESTRUNNIGTotalwithEmptyDates.Amount, Nz([Forms]![TESTFRunningBalanceCANBEDELETEDIFNECESSARY]![txtOpenBal],0)+Nz(DSum("nz([Amount],0)","[tblTopLines]","[AccountID_FK]=" & [Forms]![TESTFRunningBalanceCANBEDELETEDIFNECESSARY]![cboAccountSelect] & " AND [UserID] = " & [TempVars]![temploginID] & " AND [SortORder]<=" & [SortOrder] & " AND EntryType = 'T'"),0) AS BalanceU
FROM TESTDATESONLYCanbedeleted LEFT JOIN TESTRUNNIGTotalwithEmptyDates ON TESTDATESONLYCanbedeleted.Dates = TESTRUNNIGTotalwithEmptyDates.TransDate
ORDER BY TESTDATESONLYCanbedeleted.Dates, TESTRUNNIGTotalwithEmptyDates.SortOrder;

picture of query so far...

 

[Minty]

I would be tempted to do this by creating a temporary table and simply populating the fields by looping through a recordset of your data, if there isn't any data put in the zero values for the transactions and leave the running balance as it was.
Then run your report on the temp table.