Missing Dates - Running Balance

[valglad]

Hello,

I have a table that looks like this:

Account_Number----------Currency-----------PostingDate--------Closing Balance
1234-------------------------USD--------------July 1, 2016----------$100
1234-------------------------USD--------------July 3, 2016----------$100
1234-------------------------USD--------------July 4, 2016----------$120
1234-------------------------USD--------------July 5, 2016----------$160
1234-------------------------USD--------------July 7, 2016----------$180

I need to have records for July 2, 2016 since bank was closed that day and the balance didn't change, so July 2, 2016 should have a record for $100 and July 6, 2016 should have a record for $160.

If anyone could help me with this - it would be great. I spent quite some time on this and nothing would work out. I've added an ALL_DATES table with all dates from Jan 1, 2016 onward. So, I can add an empty record for a missing date, but how do I assume the value. The date gaps could be up to one week, there are numerous accounts numbers with numerous currencies. The example I'm showing here is the simplest possible scenario.

Thank you,
Valeri

 

[ranman256]

Make a table to hold every day in the month, tMonthDates.
then make an OUTER join query, join your data table to the tMonthDates table.
dbl-click the join line, set to ALL records in tMonthDates, some in tData.
bring down the Date field from both
missing dates will lthen show.
append to a table to fill in missing balances.

here is code to build the dates for you.
put the code below into a module.
run it to create a table of dates for the 1 month.

usage: BuildDates(2,2017)

sub buildDates(pvMo, pvYr)
dim vDate, vStart, vEnd


docmd.setwarning false
vStart = pvMo & "/1/" & pvyr
vEnd = dateAdd("d",-1,dateAdd("m",1,vStart))
vDate = vStart

while vDate < vEnd
  sSql = "insert into tMonthDates ([Date]) values (#" & vDate & "#)"
  docmd.runSql sSql
  vDate = dateAdd("d",1,vDate)
wend 
end sub

 

[xenou]

What are you trying to accomplish overall? Not quite seeing your end goal here, especially if there's lot of other cases you aren't providing sample data for.

 

[valglad]

This is an Access database that gets updated automatically nightly based on csv reports banks sends us. There is a whole bunch of banks that send the reports. They are in different countries and work on different schedules. If a bank is closed that day, there is no report. The table holds bank name, bank account, currency, posting date, opening balance,the net of daily transactions and the closing balance

A spreadsheet connected to a query based on the table via pivot table services displays daily cash position. Since some banks were closed on a particular day, say July 2, 2016, the pivot displays NULL for that date. So, the report is not uniform for a given day.

The objective is to identify and enter the missing date record and populate its opening balance with the last existing date's closing balance. Again, the date gaps could be quite substantial - up to two weeks.

Thanks
Valeri

 

[valglad]

Thanks Ranman256. I understand how to identify the missing dates and thanks for the code to quickly create monthly dates. I still don't understand though how to assign the previous day's value to the missing date that I have identified. What's more, how do I do that if more than one day in a row is missing?

Thanks
Valeri

 

[xenou]

I'm curious what you are doing but oh well. For a company you'd use the company ledger to get balances, not bank statements. You'd use bank statements to reconcile to your ledger.

If you have lots of banks you need to create a solution that's going to be more than a simple sql query (i.e., just one single query for one of the missing values, such as you are looking for here). You need to first identify all the missing records, then update all the missing records. The update itself, however, is trivial - just the value of the max date that exists prior to the missing day.

One possibility is to load the records first, with every date. Then update them with the balances (which have matches). Then fill in the missing values.

The other is to load the data you have. Then find and add the missing dates (and/or the missing balances, at the same time or as a subsequent step).