Filling in blank records

[Robbie G]

I have a table with 3 fields, txtAccount, dtDate and dblBalance. This has
account balances for a month. I have the opening balance for all accounts on
the 1st of the month but after then there are some balances missing.

I want to complete these blank records by checking if the field is blank
(null or "") and if it is then used the balance for the same account but from
the previous day. How do i do this?

thanks,
Rob

 

[Klatuu]

You can use a Group By or Totals query. Use Group By for txtAccount, Max for
dbDate, and Sum for dbBalance.

 

[Robbie G]

So how do i get the balance for the 2nd into the record for the 3rd, for
example? and i might also have a balance for the account on 5th, so the Max
function wouldn't work?

 

[Klatuu]

Maybe I did not understand your question. What I thought you wanted was if
the most current record has no balance, get the record with the most current
balance. To use your example:
Record for
2nd - has a balance
3rd - has no balance
5th - has a balance

You are running for the 3rd. If you use the group by with the max on the
date, filter to exclude records with no balance and <= the date you are
running, then you would get the record for the 2nd.

I don't know what you mean by getting the balance for the 2nd in the record
for the 3rd.

 

[John Spencer]

Do you mean you want to update the dblBalance field on those records where is
is null?

Do you have a record for every day for each txtAccount? Or are there gaps?
Acct : dtDate : dblBalance
1111 : 01/01/09 : 220
1111 : 01/02/09 : 220
1111 : 01/03/09 :
1111 : 01/04/09 : 240
1111 : 01/05/09 : 250

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

If there are no gaps then the following might work
UPDATE Table
SET dblBalance = DLookup("dblBalance","Table","txtAccount=""" & [txtAccount] &
""" AND dtDate=" & Format([dtDate]-1,"\#yyyy-mm-dd\#"))
WHERE IsNumeric(dblBalance) = False

If there are gaps, then the process will be more difficult.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Robbie G]

I was trying to complete any missing balances to simplify any further
analysis of this data. So for the example:
2nd has a balance
3rd has no balance
5th has a balance
i want to set the balance for 3rd = balance for 2nd

Robbie

 

[Robbie G]

That worked. thanks very much!

Do you mean you want to update the dblBalance field on those records where is
is null?

Do you have a record for every day for each txtAccount? Or are there gaps?
Acct : dtDate : dblBalance
1111 : 01/01/09 : 220
1111 : 01/02/09 : 220
1111 : 01/03/09 :
1111 : 01/04/09 : 240
1111 : 01/05/09 : 250

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

If there are no gaps then the following might work
UPDATE Table
SET dblBalance = DLookup("dblBalance","Table","txtAccount=""" & [txtAccount] &
""" AND dtDate=" & Format([dtDate]-1,"\#yyyy-mm-dd\#"))
WHERE IsNumeric(dblBalance) = False

If there are gaps, then the process will be more difficult.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table with 3 fields, txtAccount, dtDate and dblBalance. This has
account balances for a month. I have the opening balance for all accounts on
the 1st of the month but after then there are some balances missing.

I want to complete these blank records by checking if the field is blank
(null or "") and if it is then used the balance for the same account but from
the previous day. How do i do this?

thanks,
Rob