Starting Process of a Database

[DrJenniges86]

I am starting to work on a cash database and I am in looking for some guidance on the best way to start this task. The goal of the database is to take the transactions from an excel version bank statement (Payroll & Operating) and run them through Access to give them a specific AU/Account and debit/credit for each bank transaction and export it to our accounting software to complete the journal entry.

A hypothetical example of a transaction that would be on a bank statement would be a Bank Service Charge. On the bank statement is shows up as a Debit for $56.31. There would be a lot more transactions on the statement but this is an example of one of the transactions.

The end result that we want would be the following:

Debit: AU 77500821 Account: 69066 for $56.31
Credit: AU 77500000 Account: 10040 for ($56.31)

This is something that every month, we would to complete. Is this something feasible to set up in Access? I am looking for in general the process to set something like this up.

Thanks!

 

[pbaldy]

I think you would need a cross-reference table (for lack of a better term). It would have fields for the value to look for in the spreadsheet ("Service Charge"), and the accounts to create debit/credit entries for. Code would use that to create your output. It's certainly doable, but probably not simple.

 

[plog]

Kinda confused. You want to use 3 different pieces of software (Excel, Access and Accounting Software) to manage your data? What does each do that the others don't? Specifically, what role does Access accomplish that you can't do in the other two?

Also, better explain how you expect to interact wiith Access. Will it be just one person loading data from Excel, using Access to manipulate it all at once and produce a file you can import into your accounting software? Or will multiple users interact with it updating just one record at a time?

 

[DrJenniges86]

Kinda confused. You want to use 3 different pieces of software (Excel, Access and Accounting Software) to manage your data? What does each do that the others don't? Specifically, what role does Access accomplish that you can't do in the other two?

Also, better explain how you expect to interact wiith Access. Will it be just one person loading data from Excel, using Access to manipulate it all at once and produce a file you can import into your accounting software? Or will multiple users interact with it updating just one record at a time?

Yes, I would want to use all 3 different softwares. Right now, we only use excel and our accounting software. In excel, we have a spreadsheet for the upload. It isn't ideal, we are copying/pasting the bank statement to the spreadsheet and have 4 columns of IF/Then Statements to label the transactions. We pivot table it and from there we add the transactions to an upload for our accounting software. We want a database to import the statement so we can just import it run a query to get the necessary information for an upload. Perhaps this isn't something feasible for Access. I am not familiar with Access.

It will be only one person who uses it each month. More than likely it will be the same person.

Thanks!

 

[plog]

Sounds like Access would work. I would put the whole thing there and scrap Excel--just incorporate its functions into Access.

If you can get the data from statement to Excel to Accounting, then you will be able to get it from statement to Access to Accounting. Just a matter of what you are getting and what the accounting software wants.

 

[MarkK]

Good responses already.

The structure of a basic accounting solution looks like this...

tAccount
AccountID
Name

tTransaction
TransactionID
Num
Date
Payee

tPost
PostID
JournalID
AccountID
Amount

...so you have a list of accounts. You also have transactions, and each transaction has a number of posts, minimally two, and the amounts in the posts per transaction need to balance to zero. In this way you record the movement of money from one or more accounts to one or more other accounts, and that is the essence of double-entry accounting.
hth
Mark

 

[DrJenniges86]

Sounds like Access would work. I would put the whole thing there and scrap Excel--just incorporate its functions into Access.

If you can get the data from statement to Excel to Accounting, then you will be able to get it from statement to Access to Accounting. Just a matter of what you are getting and what the accounting software wants.

The only two reasons we would need to use Excel is to import it to Access because that's how we download the bank statement and when we are finished with Access and export it to Excel to import it to our accounting software. Excel is the only program that is compatible to our accounting software for an upload.

 

[Cronk]

DrJenniges86
Firstly, the obvious first issue is that there's no point in importing data into Access if your accounting software cannot import a CSV file or similar.



I have not encountered a financial institution which does not provide a CSV download file of transactions. This can be imported into a holding table in Access where you could assign an account code (from a lookup table in Access) to each transaction and using (Paul's suggestion) a translation table if the bank description is recorded, to automatically assign accounts.


The data could then be exported to a CSV file which in turn can be imported into your accounting system.


I see this as a relatively simple database.

 

[arnelgp]

why making extra effort when you can all do that in excel. it has more powerful financial functiin than access.

 

[The_Doc_Man]

DrJenniges86,

You are seeing differing opinions from different folks and ALL of them are correct -from their viewpoint. What you need to ask is this: What do YOU want to do and WHY do you think that what you are doing is inadequate? In essence, your problem will not be of a technical nature. Access can handle this. But it won't be a TRIVIAL operation. There will be some snags because of a simple concept in software engineering. Every time you "touch" something you have an extra chance to break it. So having a two-step process and replacing it with a three-step process is an issue. On the other hand, if what you have is somehow causing trouble, I could see why you would want to change it. But these are questions YOU must answer first.

Let me reiterate: Your inquiry of "Is it possible?" Resoundingly, yes. Access can be extremely flexible, powerful, and programmable. But the IMPLIED inquiry of "Should I do this?" is an echoing chorus of "Damned if I know. That's your call."

I'll add the opinion - clearly to be understood as such - that you need a motive for this if you are going to do it for a business operation. If you were doing it for the fun of it, my answer is "Damn the torpedoes, full speed ahead!" But if your business depends on this, you will have to plan your work to avoid down-time and that means you would be looking at continuing to do what you do until this project cranks up to life. We welcome you here and hope you can find technical answers when you need them. But I think we would all be remiss if we didn't also advise of the practicalities of such a process.

 

[RogerCooper]

Yes, I would want to use all 3 different softwares. Right now, we only use excel and our accounting software. In excel, we have a spreadsheet for the upload. It isn't ideal, we are copying/pasting the bank statement to the spreadsheet and have 4 columns of IF/Then Statements to label the transactions. We pivot table it and from there we add the transactions to an upload for our accounting software. We want a database to import the statement so we can just import it run a query to get the necessary information for an upload. Perhaps this isn't something feasible for Access. I am not familiar with Access.

If you get an csv download from your accounting software, you can make it an Excel data source eliminating the copy & paste step.

In general, if you are trying to create a single elaborate report, you are probably better off in Excel. The advantage of Access is the ability to handle large volumes of data and reports efficiently.