Debit and Credit Entries in Table (1 Viewer)

[antique3S13]

Greetings All,
I want to create MS Excel Table to perform debit and credit entries in a single table. I have tried but works only in range but creates lot of problems that I can only perform entries in a chronological order if I insert a row or apply sorting it fails. Moreover, when I convert the range into table it doesn’t work. I shall appreciate your help, please.

 

[Gasman]

Not enough to go on.
Why not upload an example and state what you need and where?

 

[GPGeorge]

Greetings All,
I want to create MS Excel Table to perform debit and credit entries in a single table. I have tried but works only in range but creates lot of problems that I can only perform entries in a chronological order if I insert a row or apply sorting it fails. Moreover, when I convert the range into table it doesn’t work. I shall appreciate your help, please.

Interestingly enough, the limitations you have encountered are some of the strongest arguments for using a database, not a spreadsheet, for tasks like this.

 

[Gasman]

Well, when I have had to insert a row, I found excel adjusted the formulae to suit, so without seeing a workbook, hard to say. Plus 'lots of problems' is hardly descriptive.

 

[Isaac]

Greetings All,
I want to create MS Excel Table to perform debit and credit entries in a single table. I have tried but works only in range but creates lot of problems that I can only perform entries in a chronological order if I insert a row or apply sorting it fails. Moreover, when I convert the range into table it doesn’t work. I shall appreciate your help, please.

You may need a database instead, as implied in George's observation. Don't try to make Excel a database. 'Table' was the silliest thing Microsoft could've named those annoying fixed ranges, but they did, so here we are with people thinking they're database tables

 

[Ken Sheridan]

If you do decide to use Access rather than Excel you can then base a form on a query like the following:

SELECT
    Format(
        DSum (
            "Credit-Debit",
            "CurrentAccounts",
            "AccountNumber = ""12345678""
            And TransactionDate<= #" & Format([TransactionDate], "yyyy-mm-dd") & "#
            And (TransactionID <= " & TransactionID & " Or TransactionDate <> #" & Format([TransactionDate], "yyyy-mm-dd") & "#)"
        ),
        "£#,##0.00;(£#,##0.00)"
    ) AS Balance,
    *
FROM
    CurrentAccounts
WHERE
    AccountNumber = "12345678"
ORDER BY
    TransactionDate,
    TransactionID;

You can insert rows into the table via the form in any order, and they will be ordered by TransactionDate. In the event of two or more transactions taking place on the same date the table's TransactionID primary key is brought into play as the tie breaker when computing the running balance

 

[Trevor G]

An example of your current layout may help with providing you with a solution and if you can let us know what version your using as the latest versions have plenty of new formulas and functionality.

If you could attach a copy but remove any sensitive data that would help.

 

[antique3S13]

If you do decide to use Access rather than Excel you can then base a form on a query like the following:

SELECT
    Format(
        DSum (
            "Credit-Debit",
            "CurrentAccounts",
            "AccountNumber = ""12345678""
            And TransactionDate<= #" & Format([TransactionDate], "yyyy-mm-dd") & "#
            And (TransactionID <= " & TransactionID & " Or TransactionDate <> #" & Format([TransactionDate], "yyyy-mm-dd") & "#)"
        ),
        "£#,##0.00;(£#,##0.00)"
    ) AS Balance,
    *
FROM
    CurrentAccounts
WHERE
    AccountNumber = "12345678"
ORDER BY
    TransactionDate,
    TransactionID;

You can insert rows into the table via the form in any order, and they will be ordered by TransactionDate. In the event of two or more transactions taking place on the same date the table's TransactionID primary key is brought into play as the tie breaker when computing the running balance

Hi Ken,
Much appreciated your profound response, I have asked could you help me to try this by using MS Access. I shall be grateful for your kind cooperation, please.
Regards,
Antique

 

[Ken Sheridan]

I have asked could you help me to try this by using MS Access. I shall be grateful for your kind cooperation, please.

To record transactions in one or more current accounts you'd just need the one table, whose definition is as in the following image:

Be sure to set the DefaultValue property of the Credit and Debit columns to 0.

You'd then create a form whose RecordSource property is the name of the query whose SQL statement I posted in my last reply. You'd need to change the two references to the AccountNumber column in the query to your actual account number, and the currency symbols from £ to your own currency of course. If you are not familiar with building a query in SQL you'd firstly need to copy the SQL statement from my last post. Next select SQL Query from the Create ribbon. In the SQL window you'll see 1. SELECT. Delete the SELECT keyword, and then paste in the SQL statement you copied to the clipboard. Save the query under a suitable name of your choice. Close the query.

Next select the query in the Navigation Pane and select Form from the Create ribbon. This will build a form based on the query. You can switch to form design view if you wish and make whatever alterations to the design of the form you might wish. Then save the form under a name of your choice and close it.

To enter data you now simply need to open the form and enter values into the relevant controls. The Balance control is bound to a computed column in the query, so that will update automatically as you enter transactions. You can navigate between transactions, or to an empty new transaction using the navigation buttons at the bottom of the form.

You might also want to create a report to print transactions. The report wizard can help you with this.

 

[Ken Sheridan]

You might find the attached file helpful. It contains a simplified version of the original form whose RecordSource query I posted earlier. I've amended the query slightly so that it references an unbound text box control in the form header as a parameter to restrict the rows returned to an account selected in a dialogue form. As many accounts as necessary can be added to the database simply by typing a new account number into the dialogue from which opens at start-up or after pressing the Change Account button.

 

[Ken Sheridan]

In the file attached to my last reply I'd omitted to amend the form's query so that the DSum function call was restricted to the the currently selected account. I've corrected this and attach the amended file to this post. I've included a couple of dummy accounts, one with a credit transaction, the other with a debit transaction. You can add some more transactions in these to see how it works.