Create a Simple Checking Registry in Access?

Question

I need to create a simple, bare-bones checking registry in access.

I've created the fields for the data.

The table has 5 pertinent fields:

TransactionID (AutoNumber)

Payee (Text)

Payment (Currency)

Deposit (Currency)

Balance (Currency)

I'm not sure how to handle the expression for the Balance. field I simple want to deduct the amount in Payment and add the amount in Deposit.

Any help will be appreciated,

James

Answer 1

Returning the credit balance for an account is not just a case of subtracting two values, but the summation of all credit-debit values on or before the date of the each transaction.  You might like to take a look at Balances.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file does not include an example for an updatable query to be used as a form's RecordSource property, but a minor amendment to one of its queries as below would provide a model for you to follow:

SELECT TransactionID, CustomerID, TransactionDate, Credit, Debit ,

    CCur(DSum("[Credit]-[Debit]","TransactionsCD","CustomerID = " &

    [CustomerID] & " And (TransactionID <= " & [TransactionID] & "  

    OR TransactionDate <> #" & Format([TransactionDate],"yyyy-mm-dd") & "#)   

    AND TransactionDate <= #" & Format([TransactionDate],"yyyy-mm-dd") & "#")) AS Balance

FROM TransactionsCD

ORDER BY CustomerID, TransactionDate DESC , TransactionID DESC;

For a report's RecordSource the following example from the demo, using a JOIN, is the most efficient solution to return a non-updatable result table:

SELECT T1.[CustomerID], T1.TransactionDate, T1.Credit, T1.Debit,

SUM(T2.Credit-T2.Debit) AS Balance

FROM TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2

ON (T2.TransactionID<=T1.TransactionID Or T2.TransactionDate<>T1.TransactionDate)

AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.[CustomerID]=T1.[CustomerID])

GROUP BY T1.[CustomerID], T1.TransactionDate, T1.TransactionID, T1.Credit, T1.Debit

ORDER BY T1.[CustomerID], T1.TransactionDate DESC , T1.TransactionID DESC;

Note how in each case the TransactionID primary key column is used as the tie-breaker to cater for multiple transactions on the same date.  Note also the use of the ISO standard format for date notation of YYYY-MM-DD  for the date literals when calling the DSum function in the first query, to ensure that the query will return the correct results regardless of the local date format of the system.

You can of course return the rows in Ascending date order rather than descending date order if you prefer, though if using a query solely as a report's RecordSource, the ORDER BY clause should be omitted, and the report's internal sorting and grouping mechanism used to order the rows.

Answer 2

Balance should NOT be a field, it should be calculated in a query. Lookup Running sum in query.