How to show Debit and Credit amount based on account type (1 Viewer)

[shery1995]

Hi All

I have a table with following fields: MatterID, ClientID, TransactionID, Date, TransactionType, AccountType, Amount_debit, Amount_credit. What I want to achieve is if (AccountType is = Office Account) then Amount_debit and Amount_credit should show under the title of Office Account "Amount_debit and credit" and if (AccountType is = Client Account) then Amount_debit and Amount_credit should show under the title of Client Account "Amount_debit and credit" like

Office Account Client Account

Debit Credit Debit Credit

Any help and suggestion will be highly appreciated including change of table design to achieve the required result.

Kind regards
A Munir

 

[MajP]

Where do you want to see this? A form, a report, or a query? Continuous view or single form view? Also I do not do accounting type databases, but I think most people would recommend a single transaction field for debits and credits.

 

[shery1995]

Where do you want to see this? A form, a report, or a query? Continuous view or single form view? Also I do not do accounting type databases, but I think most people would recommend a single transaction field for debits and credits.

Thank you for your reply. I will use on all of them you mentioned above.

 

[gemma-the-husky]

You would be better using a number rather than text
So 1=Office Account, 2=Client Account, etc

The rest is just a matter of presentation. You can use a query to decide the column, based on the account type, and the value. Have a column called Office-Debit, and that corresponds to transactions satisfying "office Account", value > 0. Office-Credit is "office Account", value < 0, and so on.

You might only want office account transactions for some reports, and just client account for others, but your query can handle that by testing the transaction type. You will end up with multiple queries, one for each report variant. Finally, it's probably more normal nowadays to list all transactions, both debit and credit in a single column. Having separate columns is more the sort of thing you see in manual bookkeeping, but again, it's a matter of taste. It is harder to pick credits out of a single column, than to have them in a separate column .Your query can, of course, sort all the debits first, then the credits, if you prefer.

 

[MajP]

Here is one example See query1. You can also use iif functions on a calculated field or control.

But like I said transactions are normally in a single field and are (+/-) Much easier to get totals and running sums.

 

[gemma-the-husky]

You do need to keep office account and client account separate. A good example is a solicitor's practice. The office account is the solicitor's money. The client account is the money the solicitor is holding on behalf of the client which is completely different, and had to kept in a separate bank account.

 

[shery1995]

You do need to keep office account and client account separate. A good example is a solicitor's practice. The office account is the solicitor's money. The client account is the money the solicitor is holding on behalf of the client which is completely different, and had to kept in a separate bank account.

You are absolutely spot on. That is what I want to achieve.

 

[shery1995]

Here is one example See query1. You can also use iif functions on a calculated field or control.
View attachment 90171

But like I said transactions are normally in a single field and are (+/-) Much easier to get totals and running sums.

Thank you for your efforts. I will try and see if can get a result I wanted. Many thanks

 

[shery1995]

You would be better using a number rather than text
So 1=Office Account, 2=Client Account, etc

The rest is just a matter of presentation. You can use a query to decide the column, based on the account type, and the value. Have a column called Office-Debit, and that corresponds to transactions satisfying "office Account", value > 0. Office-Credit is "office Account", value < 0, and so on.

You might only want office account transactions for some reports, and just client account for others, but your query can handle that by testing the transaction type. You will end up with multiple queries, one for each report variant. Finally, it's probably more normal nowadays to list all transactions, both debit and credit in a single column. Having separate columns is more the sort of thing you see in manual bookkeeping, but again, it's a matter of taste. It is harder to pick credits out of a single column, than to have them in a separate column .Your query can, of course, sort all the debits first, then the credits, if you prefer.

Thank you very much for your reply. I am trying to work out what you have suggested, if not been successful then will get back to you for further help in detail.

 

[gemma-the-husky]

Have a look at this.

I loaded a few sample accounts, and a few dummy transactions.
There is a form called Accounts, and a subform for the transactions. You can navigate from account to account using the navigation buttons at the bottom. You can change account names or add new ones. You can edit transactions or add new ones at the bottom line of the transaction form. There is a little validation included.

The subform is automatically linked to the relevant accounts. Click the outside of the subform, and select the "data" tab, and you can see how the link works.

The database just shows the main form at the start. Press F11 to see the database window

I hope you find this useful.

 

[shery1995]

Have a look at this.

I loaded a few sample accounts, and a few dummy transactions.
There is a form called Accounts, and a subform for the transactions. You can navigate from account to account using the navigation buttons at the bottom. You can change account names or add new ones. You can edit transactions or add new ones at the bottom line of the transaction form. There is a little validation included.

The subform is automatically linked to the relevant accounts. Click the outside of the subform, and select the "data" tab, and you can see how the link works.

The database just shows the main form at the start. Press F11 to see the database window

I hope you find this useful.

Thank you very much, you have been so kind and helpful.

 

[shery1995]

Have a look at this.

I loaded a few sample accounts, and a few dummy transactions.
There is a form called Accounts, and a subform for the transactions. You can navigate from account to account using the navigation buttons at the bottom. You can change account names or add new ones. You can edit transactions or add new ones at the bottom line of the transaction form. There is a little validation included.

The subform is automatically linked to the relevant accounts. Click the outside of the subform, and select the "data" tab, and you can see how the link works.

The database just shows the main form at the start. Press F11 to see the database window

I hope you find this useful.

Thank you for your efforts, I have noticed that when I insert the value in amount field if amount is credit then I have to enter the amount with - (minus sign), otherwise it will be stored in debit. Is there a way out to just enter the amount and select the amount is debit or credit?

 

[gemma-the-husky]

This was really to give you some ideas.

But that's the thing - a debit is positive, and a credit is negative. If you don't enter the sign you have to have 2 columns, one for debits and one for credits. (in your case 4 columns). If you then store the values in separate columns, in order to get a total you then have to sum the debits and credits separately, and then add those figures together. Because you have to use these sums, some queries become non-updateable, and everything you do is much more complicated anyway. Its far easier to store everything in a single column. I can see you might want to store office account and client account in two separate columns, but you don't have to. Presentation and data management are two different things.

I don't think you would want a debit/credit dropdown. The best way would be to have a debit/credit entry, and try to get it working that way. If you have just an amount in the table, you would need that in a hidden field on the form, and then update the amount based on whether you enter in the debit or credit column but not allow both. So it's still actually stored in a single database column. It's just far easier (and certainly for this demo) to have a single column. Personally I would rather see blanks than zeroes in the non-relevant columns, but you can get issues with totals when you sum values, some of which are blanks (nulls), so it will be a lot of trial and error. You also use more valuable screen real estate with multiple columns. You might want a larger notes field, or you might need to enter additional information regarding each transaction, and the more compact you can keep it. the eaier it is to use.

That's why the query that shows the transaction decides which column to use on the form based on both the transaction type, and whether it's greater or less than zero, and then the form is bound to the query, you don't get blanks, and the totals at the bottom update smoothly. So the table itself only has one amount column. Changing the data storage in order to change the presentation might be a lot of work. It's OK if you build it in from the beginning, but even so you might end up giving yourself a lot of work.

Do you have any contacts you could ask to see what their systems do?

[Edit - out of interest I actually just tried to change it to use a separate debit and credit column to enter the amount, and it proved surprisingly tricky with a continuous subform and unbound fields to distinguish debits and credits. - I just couldn't see an easy solution]

 

[shery1995]

This was really to give you some ideas.

But that's the thing - a debit is positive, and a credit is negative. If you don't enter the sign you have to have 2 columns, one for debits and one for credits. (in your case 4 columns). If you then store the values in separate columns, in order to get a total you then have to sum the debits and credits separately, and then add those figures together. Because you have to use these sums, some queries become non-updateable, and everything you do is much more complicated anyway. Its far easier to store everything in a single column. I can see you might want to store office account and client account in two separate columns, but you don't have to. Presentation and data management are two different things.

I don't think you would want a debit/credit dropdown. The best way would be to have a debit/credit entry, and try to get it working that way. If you have just an amount in the table, you would need that in a hidden field on the form, and then update the amount based on whether you enter in the debit or credit column but not allow both. So it's still actually stored in a single database column. It's just far easier (and certainly for this demo) to have a single column. Personally I would rather see blanks than zeroes in the non-relevant columns, but you can get issues with totals when you sum values, some of which are blanks (nulls), so it will be a lot of trial and error. You also use more valuable screen real estate with multiple columns. You might want a larger notes field, or you might need to enter additional information regarding each transaction, and the more compact you can keep it. the eaier it is to use.

That's why the query that shows the transaction decides which column to use on the form based on both the transaction type, and whether it's greater or less than zero, and then the form is bound to the query, you don't get blanks, and the totals at the bottom update smoothly. So the table itself only has one amount column. Changing the data storage in order to change the presentation might be a lot of work. It's OK if you build it in from the beginning, but even so you might end up giving yourself a lot of work.

Do you have any contacts you could ask to see what their systems do?

Thank you very much, got your point. Sorry I don't have any. I had a Dos based solicitors accounts somewhere, I purchased in 2006-7 if I found its CD I will share it with you, provided it work with current windows operating system.