Debit and Credit Entries in Table

[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.

 

[antique3S13]

Hi Ken,

I really appreciate your work and regrettably I am not good in coding. So far, I have know that tables are used to store data, forms are used for data entry, queries are used to extract data and finally reports are used for meaningful representation of data. I have tried the attached database and found it difficult to absorb. I request you to explain in person by having sessions, please.
Best Regards, Atique

 

[Ken Sheridan]

I request you to explain in person by having sessions, please.

I'm afraid that's not possible. Replicating the basic functionality of my demo database is a simple matter, however:

Firstly let's assume for now that the database is for one account only. It can easily be adapted to cater for multiple accounts later if necessary. Start by creating a table which includes columns TransactionID (autonumber primary key), TransactionDate, Credit, and Debit. You can add any other columns to represent other attributes of each transaction if you wish. Next create and save a simple query which returns all columns from the table sorted by TransactionDate and TransactionID:

SELECT *
FROM Transactions
ORDER BY TransactionDate, TransactionID;

You can change the table name to one of your own choice if you wish. Create a form with the above query as its RecordSource property, in continuous forms or datasheet view. You can use the form wizard to help you create the form if necessary.

To return the balance per transaction in the form open the above query in query design view and, in the Field row of an empty column in the design grid enter the following:

Balance:Format(DSum("Credit-Debit","Transactions","TransactionDate<= #" & Format([TransactionDate],"yyyy-mm-dd") & "# And (TransactionID <= " & TransactionID & " Or TransactionDate <> #" & Format([TransactionDate],"yyyy-mm-dd") & "#)"),"£#,##0.00;(£#,##0.00)")

In the above expression change the table name if necessary, and change the currency symbol to that of your own local currency. Save the amended query. When you open it you should see the balance per transaction returned in the additional column.

Finally, open the form in design view and add a text box control bound to the new Balance column to the Detail section. Save the amended form design.

 

[antique3S13]

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

Hi GPGerorge,
Unfortunately, my concepts are very limited about creating database, so far, I know only that data is stored in tables, queries are used to retrieve data, forms are used to enter the data and finally reports are the representations of data. I have asked could you teach me how to create an ideal database, please.

 

[antique3S13]

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

Hi Isaac,
I have asked could you help me to how to create database, please.

 

[antique3S13]

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

View attachment 122256

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.

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.

Hi Ken,
I appreciate your contribution, your kind is requested to elaborate following points, please.
1. Why "Cleared" field is created in the table, is it necessary?
2. When posting each transaction account number is required? How to pass multiple transactions under one account avoiding each time entering account number?
3. What is the sequence of creating database i.e. first of all we have to create tables then either we should design forms for data entry or first we decide query criterion and then design forms on the basis of query. Later, report can also be created on the basis of query.???

I shall be grateful to you, please.

 

[GPGeorge]

Hi GPGerorge,
Unfortunately, my concepts are very limited about creating database, so far, I know only that data is stored in tables, queries are used to retrieve data, forms are used to enter the data and finally reports are the representations of data. I have asked could you teach me how to create an ideal database, please.

I'm afraid that it would be extremely difficult to help you create an "ideal database" without knowledge of the context and circumstances in which it will be used.

We can offer suggestions and guidance, as others have done in the previous 14 or 15 posts.

Here are some references to resources that can help you learn the principles on which your database should be built. The foundation is proper table design, following the principles of Data Normalization. These are formal statements describing how data should be stored in tables.
I like this YouTube introduction because it is an informal way to begin.

You need to identify all of the entities, or "things" you need to keep track of. In a financial tool, that will include Accounts, Transactions, and related entities. I can't list them all because the specific requirements you have determine that.

Look for examples, such as the one Ken shared. It has three related tables, but that's only a starting point. Note that in this specific instance, there are three entities, represented by three tables. TransactionTypes lists the kinds of transaction you can track. If there isn't a record for a transaction type in that table, it can't be used in CurrentAccounts. That rules is enforced by the relationship between the tables, shown graphically as a line with a 1 at one end and the sign at the other end. That indicates a couple of important points.

First, it means each transaction type can be used in one or more CurrentAccounts transactions.

It also means that only those transaction types in the table can be used.

That helps keep stray values out of the data. Unlike and Excel worksheet where you can pretty much enter anything you want, anywhere you want, Access allows you to keep your data clean and reliable.

AccountNumbers have the same kind of relationship with CurrentAccounts, i.e. each AccountNumber in that table can be part of one or more CurrentAccount transactions.
And, in order to enter an account number for a transaction, it must first be listed in the AccountNumber table.

Again, these relationships, and the others you'll need to identify for your specific database, help keep your data reliable and consistent.

No doubt you'll end up with other tables in addition to these.

It all begins with that.

Take a stab at creating the tables you need for your specific situation and share them. Others will then have something to work with in helping you.

 

[Ken Sheridan]

Hi Ken,
I appreciate your contribution, your kind is requested to elaborate following points, please.
1. Why "Cleared" field is created in the table, is it necessary?
2. When posting each transaction account number is required? How to pass multiple transactions under one account avoiding each time entering account number?
3. What is the sequence of creating database i.e. first of all we have to create tables then either we should design forms for data entry or first we decide query criterion and then design forms on the basis of query. Later, report can also be created on the basis of query.???

1. The Cleared column is not necessary. In the operational database I used as a basis for the simplified file I posted current account statements can be reconciled by comparing the statement with the rows from the table WHERE Cleared = TRUE.

2. A dialogue form for selecting an account number is automatically opened at start-up. The main form opens filtered to that account. The currently selected account number is automatically entered into each new row inserted via the form until another account is selected in the unbound combo box in the form header. Selecting another account filters the form to that account.

3. The first stage in the creation of a relational database is to determine the 'logical model' i.e. the entity types and relationships between them which make up that part of the real world which the database is modelling. This can be done on paper. Once you are satisfied that the model is a correct representation of the reality, you can then implement it as a set of related tables. Each table must be correctly normalized, with each column representing an attribute which is determined solely by the whole of the table's primary key. For example an addresses table with columns CityID and CountyID would not be legitimate, as CountyID is determined by CityID, which is not the table's key. Such a table would need to be decomposed so that the Addresses table contains only the CityID, and a Cities table then contains a CountyID column, which references the primary key of a Counties table. You might like to take a look at DatabaseBasics.zip my Dropbox public databases folder at:

Dropbox

www.dropbox.com

This little demo file provides a simple introduction to the design of relational databases in Access.

Once the tables and relationships between them ( the 'physical model') have been set up you can then design the user interface of forms and subforms. For data entry or editing a form is bound to a table, or more usually to a query on a table. A query for data entry can include, in addition to the referencing table into which data is being entered, referenced table, e.g. a form fro entering an address might be based on a query which joins the Addresses, Cities, and Counties table, but data should only be entered or edited in the Addresses table, bot into the referenced Cities and Counties tables.

A fully developed database application will also include unbound forms. These are used for such things as selecting or entering criteria in unbound controls, on the basis of which a bound form or a report might be opened. Sometimes a report will be opened from a bound form, e.g. an invoice form might include a button which opens a report of the current invoice, which can then be printed or emailed to a customer.

Designing a report is similar to designing a form, but with much greater functionality. A report can be grouped for instance, e.g an invoice report would be grouped by the invoice number, and include the number, invoice date and customer details in a group header. The individual invoice lines would then be returned in the report's detail section, one per item covered by the invoice. Sales and purchase order reports are designed similarly. A report will normally be based on a query, which can draw on multiple tables for its data. Unlike a query used as a form's RecordSource, a report's query need not return an updatable result table, so can be much more complex where necessary, and can return aggregated data using operators like SUM, COUNT, MAX, MIN etc. My DatabaseBasics demo illustrates a variety of different queries for performing common tasks.

 

[Isaac]

Hi Isaac,
I have asked could you help me to how to create database, please.

Sorry , but someone has to tell you this: That's too broad of a question, and too big of a favor to ask.

Start studying YOURSELF, and start doing the work. post back when you get stuck on one, specific , problem.

 

[Gasman]

Might be better looking for a template?
What is this dB for?