Income and Expense

[cyber27uk]

How do i create a income and expense database that will give me sequential numbering for both income and expenses. And a final balance. (income - expense). Sorry in advance for being a noob.

 

[Gasman]

Use one of the templates?
Do you want sequential numbering without any gaps?

 

[cyber27uk]

Without gaps. where do i find the templates?

 

[Gasman]

Templates are found when you go to create a new DB.
I believe you will need to store the last of each transaction type and increment it when you save the record.
Why do you believe you need a sequential number?, Quicken does not have them?, nor does Xero.

 

[XPS35]

Consider using Excel.

 

[GPGeorge]

How do i create a income and expense database that will give me sequential numbering for both income and expenses. And a final balance. (income - expense). Sorry in advance for being a noob.

These are, in a sense, two separate questions.

Creating an income and expense database

Sequential transaction numbering without gaps

One need not involve the other, but they do co-occur in many cases.

For the first question. There are options, including a vendor application like Quicken in the US and other, similar applications in other parts of the world. Why invest many hours of work to create an Access application when something like Quicken is available with many more features and refinements than most of us would have time to implement well, if at all.

You can find templates by searching the internet for database templates for bookkeeping or accounting. That'll get you a head-start on a do-it-yourself version.

The second question is also answered in many posts. Search for the terms in your question.

However, as has been suggested, the need for sequential numbering without gaps is probably subject to validation as a requirement, not a nice-to-have feature. In accounting, I suspect it's more likely to be required than in some situations, but do consider whether it will make any difference in the practicality of what you plan to create and use.

 

[Pat Hartman]

So, you never make a mistake and enter items out of order? Because if you do, recovery is not easy. I also question the requirement as stated and I agree with the others. Quicken gives you pretty good features that you will almost certainly never create for yourself with Access, especially as a newbee.

Here's a sample with two kinds of sequences.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

The one on the main form is a custom primary key. The one on the child form may be what you are looking for and it is just used to order items. Notice the increment by 10. This allows you to enter something between two other items and then regenerate the sequence number. Both sequences are "within" either a FK or "within" some other grouping field.

 

[gemma-the-husky]

Seriously. You do not want separate sequences for debit and credit values in the same table. Just a single sequence, using positive/negative values
Far easier. The total is the sum of everything, and in a double entry system should add to zero. Obviously you might want a sales receivable section on its own, but the total of outstanding balances should reconcile/match to an account in the main general ledger which DOES form part of the double entry, and is included in the Trial Balance which sums to zero.
.

 

[gemma-the-husky]

These are, in a sense, two separate questions.

Creating an income and expense database

Sequential transaction numbering without gaps

One need not involve the other, but they do co-occur in many cases.

For the first question. There are options, including a vendor application like Quicken in the US and other, similar applications in other parts of the world. Why invest many hours of work to create an Access application when something like Quicken is available with many more features and refinements than most of us would have time to implement well, if at all.

You can find templates by searching the internet for database templates for bookkeeping or accounting. That'll get you a head-start on a do-it-yourself version.

The second question is also answered in many posts. Search for the terms in your question.

However, as has been suggested, the need for sequential numbering without gaps is probably subject to validation as a requirement, not a nice-to-have feature. In accounting, I suspect it's more likely to be required than in some situations, but do consider whether it will make any difference in the practicality of what you plan to create and use.

Why not use quicken? Because a basic accounting system could be developed in a couple of weeks. You never have to pay for maintenance, and it does exactly what you want. Depends what books of prime entry you want, but I doubt very much if quicken offers anything much that is really needed. Maybe the issue is that you need the understanding of an accountant to know how to do it well. For instance, you really do need to understand what you need in your chart of general ledger accounts

In the UK I wouldn't try to develop a payroll system handling tax and social security deductions, just because of potential technical issues, but normal accounting stuff is no problem.
.

 

[GPGeorge]

Why not use quicken? Because a basic accounting system could be developed in a couple of weeks. You never have to pay for maintenance, and it does exactly what you want. Depends what books of prime entry you want, but I doubt very much if quicken offers anything much that is really needed. Maybe the issue is that you need the understanding of an accountant to know how to do it well. For instance, you really do need to understand what you need in your chart of general ledger accounts

In the UK I wouldn't try to develop a payroll system handling tax and social security deductions, just because of potential technical issues, but normal accounting stuff is no problem.
.

" Maybe the issue is that you need the understanding of an accountant to know how to do it well. "

It took all of twenty minutes to understand how Quicken works. Anyone who has ever balanced a checkbook in the old days before computers can do the same.

"...I doubt very much if quicken offers anything much that is really needed..."

And that is a head-scratcher, indeed. But if you don't need the ability to track income by source and expenses by category, I guess then, yeah, there's nothing in Quicken (or any other financial software) that anyone really needs. A spreadsheet works fine. No reason to even put two weeks into an Access application.

Yes, there is a license cost involved. No, it's nowhere near the cost of developing a multi-faceted financial application on your own.

I get that some people want to do it themselves and are willing to invest twenty to forty hours to do it. It can even be a learning experience worthwhile on its own.

There may be many reasons to invest a couple of weeks creating yet another Access based checkbook application, of course.

 

[Gasman]

Xero is a fully fledged accountancy system, and that does not use sequential numbering, at least none that a user can see?

 

[gemma-the-husky]

Xero is a fully fledged accountancy system, and that does not use sequential numbering, at least none that a user can see?

I expect there are ID numbers in there, then.

 

[gemma-the-husky]

" Maybe the issue is that you need the understanding of an accountant to know how to do it well. "

It took all of twenty minutes to understand how Quicken works. Anyone who has ever balanced a checkbook in the old days before computers can do the same.

"...I doubt very much if quicken offers anything much that is really needed..."

And that is a head-scratcher, indeed. But if you don't need the ability to track income by source and expenses by category, I guess then, yeah, there's nothing in Quicken (or any other financial software) that anyone really needs. A spreadsheet works fine. No reason to even put two weeks into an Access application.

Yes, there is a license cost involved. No, it's nowhere near the cost of developing a multi-faceted financial application on your own.

I get that some people want to do it themselves and are willing to invest twenty to forty hours to do it. It can even be a learning experience worthwhile on its own.

There may be many reasons to invest a couple of weeks creating yet another Access based checkbook application, of course.

I don't really know what quicken is. I thought it might be a multi user accounting system for SMEs. If it's more like an analysed expense record for an individual where you don't need to produce an enterprise balance sheet, then I assume it's much easier to build. The bells and whistles might be fancy graphical presentations.

I might like an investment tracker, especially where you can automatically download daily investment prices and so on. It's a bit of a chore to manually update on a daily basis.

 

[Pat Hartman]

Quicken interfaces with your bank accounts (for a price) and maybe your investment accounts also. I don't use it so I'm not sure.

 

[GPGeorge]

Xero is a fully fledged accountancy system, and that does not use sequential numbering, at least none that a user can see?

Internally, no doubt, records would be tracked with a unique primary key. Externally, anyone

I don't really know what quicken is. I thought it might be a multi user accounting system for SMEs. If it's more like an analysed expense record for an individual where you don't need to produce an enterprise balance sheet, then I assume it's much easier to build. The bells and whistles might be fancy graphical presentations.

I might like an investment tracker, especially where you can automatically download daily investment prices and so on. It's a bit of a chore to manually update on a daily basis.

Perhaps now would be a good time to inform yourself about the actual software, then. I understand there are other, similar applications, like Xero which is aimed at businesses as well as individuals. It appears to be online, whereas Quicken is a Windows application.

@Pat. It is possible to download csv files from most banks for no additional cost. It is also possible to pay for the more automated interface by signing up with the financial institution, e.g. Bank of America. That fee is charged by the financial institution for direct access, as I understand it.

My investment company does allow online updates for retirement and investment accounts with no additional cost.

If all you want is a digital check book, I can see why the numerous advanced features are of no interest. Again, a simple worksheet would handle posting income and expenses, no problem. Or a table in an Access accdb, for that matter.

 

[Pat Hartman]

It is possible to download csv files from most banks for no additional cost.

I do that now but the formats are not consistent. Every bank has something different. My current bank's download is a PITA since it does not include check number as a separate column. Nor does it include anything identifiable if I use their payment feature. So, due to consolidations over the years, I ended up with 3 CITI credit cards. Do you think they could include the last four digits of the account, no, that would be too user friendly. So I'm left with matching by paid amount.

Also, and this really irks me, when I send a check to Citi Bank, they IMMEDIATELY debit my account. They don't delay for a second!! AND they don't send my bank the check number so I have to match those entries by paid amount also.

 

[GPGeorge]

I do that now but the formats are not consistent. Every bank has something different. My current bank's download is a PITA since it does not include check number as a separate column. Nor does it include anything identifiable if I use their payment feature. So, due to consolidations over the years, I ended up with 3 CITI credit cards. Do you think they could include the last four digits of the account, no, that would be too user friendly. So I'm left with matching by paid amount.

Also, and this really irks me, when I send a check to Citi Bank, they IMMEDIATELY debit my account. They don't delay for a second!! AND they don't send my bank the check number so I have to match those entries by paid amount also.

I don't want to make it sound like I'm an expert here. All I can talk about is my experience. Actually I misspoke anyway. I don't use the generic csv file format, but a proprietary file format with a .qfx extension that Quicken recognizes and the bank supports. I'm sure it's because of the ubiquitous status of Quicken in the market. I have to make the selection of the format in the bank's download section. They offer the comma delimited, or csv, for general use though. It's become a habit for me to click that option without even thinking about the version, I guess.

I agree on the practices of the banking industry. There's no way to justify it other than that they can get away with it.

Still, if the options are a spreadsheet, a simple income and expense database, and something like a third party application like this one, the value is definitely with the last choice, IMO.

 

[Gasman]

I expect there are ID numbers in there, then.

Oh, I do as well, but as I can click on the headers and sort asc/desc, they do not make much sense other than for the purpose they are for.

 

[Pat Hartman]

Neither my last bank nor the one that took them over offers any download for Quicken or Quickbooks. I guess that's what you get when you use small, local banks. They do have branches in the local Stop & Shop's so they are very convenient and have extended hours compared to normal banks.

I agree on the practices of the banking industry. There's no way to justify it other than that they can get away with it.

You'd think with the advent of computers we could have gotten away from "banker's hours" and their anti-small customer practices but they spend lots of money lobbying Congress. So I still have to wait three business days for a deposit to "clear" but CitiBank doesn't have to wait 3 seconds.

 

[LarryE]

Neither my last bank nor the one that took them over offers any download for Quicken or Quickbooks. I guess that's what you get when you use small, local banks. They do have branches in the local Stop & Shop's so they are very convenient and have extended hours compared to normal banks.

You'd think with the advent of computers we could have gotten away from "banker's hours" and their anti-small customer practices but they spend lots of money lobbying Congress. So I still have to wait three business days for a deposit to "clear" but CitiBank doesn't have to wait 3 seconds.

Decades ago, Citibank was designated the clearinghouse for ALL funds transfers for all banks. So, if you have a transfer occurring in Wells Fargo for example, that involves a different bank, that transaction needs to be "cleared" through Citi first. Transactions occurring within Citibank do not need "clearing". In fact, transactions occurring worldwide go through Citi as well if they involve a US bank. The funds transfer department at Citi is a very busy place.

If you look at the Balance Sheet for any bank, there is a Due To/Due From account on it. That is an account that holds those transactions until they are "cleared". At any time, they could have a Debit Balance (Asset) or Credit Balance (Liability). It literally depends on the time of day.

I was a bank auditor for many years for a large regional bank in Seattle. When it came time to audit our funds transfer operations, newbie auditors in our department were always assigned to reconcile our Due To/Due From account....just for fun.