a balance enquiry HAS to run from a known starting point (date of the most recent PRECEEDING opening balance), up to and including the specified closing point. you can make life easier by coericing the start point by only allowing the user to specify a start point that also has an opening balance. do that by say offering the start point as a choice in a combo/list box.
however your main problem is going to be how to identify what date rows are relevant, and that is primarily down to whether or not you have stored a time component as part of your transaction date. and that depends on your design. like most db systems Access/JET stores dates in a date time value (its actually a decimal number where the integer portion of the number is number of says since, IIRC 31 Dec 1899, and the decimal part id the proportion of the day.
so 1.0 is (I think) 01 Jan 1900 00:00:00
1.5 is 01 Jan 1900 12:00:00
7.75 is 07 Jan 1900 18:00:00
so when it comes to identifying your date bands you need to know if the time component is relevant (if you have only ever stored a date then you are fine), and if there is a risk that you may have some transactions for the same day but included in the opening balance of the same day, again if you don't then its not a problem
so say you want transactions for the month of Jan 2016, then
1) you need the opening balance for Jan 2016
2) all transctions from 01 Jan 2016 to 31 Jan 2016
..which equates to where mydatecolumn BETWEEN #2016/01/10# AND #2016/01/31#
but if you have specified a time element then you need to cater for any transaction that occurred on the 31st of jan
..which equates to where mydatecolumn BETWEEN #2016/01/10# AND dateadd("d",1,cdate("2016/01/31"))
where mydatecolumn >= #2016/01/10# AND mydatecolumn <=#2016/02/01"
you can always get the opening balance by using say a domain function such as dlookup
eg OpeningBal = dlookup("value", "mybalancetable", "AccountNo=''WISLO006' AND mydatecolumn = #2016/01/01#)
..assumign the table contining the opening balances is called mybalancetable
AND the openign balance column is called "value", it has a account number column called accountno and the date of the blance is in a column called mydatecolumn
the # delimits a date literal and the following text should be in either ISO date format yyyy/mm/dd or US format mm/dd/yyyy
you can probably use a join in the transactions query to get the opneign balance (take note of it for the first row processed, and ignore in all subequent rows being processd eg:-
select A.balance, T.txdate, T.Value from Accounts as A, Transacations as T
left join T as A.ACCOUNTID = T.ACCOUNTID
where A.BALANCEDATE = #2016/01/01# AND
T.txdate = between #2016/01/01# AND #2016/01/31# AND
it also depends on how you store the opening balance. this approach assumes that you store an opening balance for each period irrespective of whether there are any transactions ont he the account that period
if not then you have to find the moist recent openinhg balance on the account and then apply all transaction from that point forward.
what I tend to do in such situations is store everything in the transaction table with a time as well as a datestamp (remember Access/JET stores time and date values in the same column so they can ]and SHOULD] be the same column)
have a transaction ID that identifies the type of tracnsactionit is and how it affects the value either + or -1 and multiply by that
0 = opening balance, effect +
1 = payment received +
2 = invoice raised -
3 = credit note +
4 = debit note -
10 writeoff +
..and so on.
then the balance becomes
select account, sum(transaction_value * TT.affect) from transaction as T
join transactionTypes as TT on T.TransactionType = TT.TransactionType
where t.txdate between #2016/01/01# and #2016/01/31#
group by account
Last edited by healdem; 03-29-16 at 09:13.
I'd rather be riding on the Tiger 800 or the Norton