how can i make auto journal entries in access

[mobarak ahmed]

I am now make small data base for client department, invoicing and payment

all data exist when i enter the invoice or the payment so it will be nice if i can make query or report to make the journal for this invoice or payment

i have the data base ready if any one want to give it a try

 

[June7]

What do you mean by 'make the journal'? If you just want to open a report to display data, what is stopping you?

 

[theDBguy]

Hi. Welcome to AWF!

Are you trying to print the journal? Or just display it?

 

[mobarak ahmed]

i mean convert the invoice to Accounting entry account ,debit, and credit

 

[June7]

Invoice should already be based on data entered in table(s). This IS your 'accounting' entry. If you want to provide db for analysis, follow instructions at bottom of my post.

If you want a double entry bookkeeping system, maybe instead of reinventing the wheel, should look at OTS app like QuickBooks.

 

[theDBguy]

i mean convert the invoice to Accounting entry account ,debit, and credit

That would depend on your table structure, but something along the lines of the following might be what you're talking about.

SELECT TransactionDate, CompanyName, IIf(TransactionType="Debit",Amount,Null) AS Debit, IIf(TransactionType="Credit",Amount,Null) AS Credit
FROM TransactionTable
ORDER BY TransactionDate, CompanyName, TransactionID

Hope that helps...

 

[mobarak ahmed]

Invoice should already be based on data entered in table(s). This IS your 'accounting' entry. If you want to provide db for analysis, follow instructions at bottom of my post.

If you want a double entry bookkeeping system, maybe instead of reinventing the wheel, should look at OTS app like QuickBooks.

if you don't mind i want to chat with you for more information about this issues

 

[mobarak ahmed]

That would depend on your table structure, but something along the lines of the following might be what you're talking about.

SELECT TransactionDate, CompanyName, IIf(TransactionType="Debit",Amount,Null) AS Debit, IIf(TransactionType="Credit",Amount,Null) AS Credit
FROM TransactionTable
ORDER BY TransactionDate, CompanyName, TransactionID

Hope that helps...

i am brand new in access
so it would be nice if you give me this code in file in order to understand how it works

 

[theDBguy]

i am brand new in access
so it would be nice if you give me this code in file in order to understand how it works

Sorry, I don't have a file to give you, since I don't have an accounting database. Even if I were able to give you a file, I am not sure it will help you because it may not be the same as your file. The better option is for you to share with us your actual file (minus any sensitive data - just put in some dummy or test data), so we can show you how to create what you were asking for.

 

[mobarak ahmed]

thank you for your concern i will prepare the file and send it to you soon

 

[Pat Hartman]

i mean convert the invoice to Accounting entry account ,debit, and credit

Journal entries are not made after the fact. You make both journal entries in a temp table and if they reconcile, your code appends them to the permanent tables and removes the temp entry. This transfer from the temp tables to permanent and removal from temp MUST be done inside a transaction since all updates MUST happen in order to not throw the books out of balance.

I know just enough about double-entry bookkeeping to be dangerous so that is all I can give you. You make TWO sets of entries. If they reconcile, they become permanent. Depending on the type of transaction, you may have 1 credit, 1 debit or multiple credits, 1 debit or 1 credit , multiple debits or multiples of both.

 

[mobarak ahmed]

Journal entries are not made after the fact. You make both journal entries in a temp table and if they reconcile, your code appends them to the permanent tables and removes the temp entry. This transfer from the temp tables to permanent and removal from temp MUST be done inside a transaction since all updates MUST happen in order to not throw the books out of balance.

I know just enough about double-entry bookkeeping to be dangerous so that is all I can give you. You make TWO sets of entries. If they reconcile, they become permanent. Depending on the type of transaction, you may have 1 credit, 1 debit or multiple credits, 1 debit or 1 credit , multiple debits or multiples of both.

yes that it exactly what i need , do you have simple file doing this thing ?

 

[Pat Hartman]

Sorry. There is no such thing as a simple double entry bookkeeping application. The only ones I ever built were in COBOL for the mainframe. They took lots people months/years to build I have no Access samples.

As someone already mentioned, you are far better off spending a few hundred dollars for QuickBooks. It is audited and it works. It is not easy to use but it is pretty comprehensive from what I am told. It comes in multiple versions so you should be able to find something suitable for your particular type of business. i.e. service, manufacturing, sales, etc. The chart of accounts will be customized and further customizable. All required reports will be present and you will have the ability to add more. You can also purchase an ODBC driver so you can interface with Access more easily.

This is not the kind of app you can just whip up out of thin air. You need a comprehensive understanding of bookkeeping and a subject matter expert at your side. You don't seem to have either.

 

[CJ_London]

An invoice journal will typically credit a sales ledger account and either debit a sales debtor or cash.

there are multiple ways this can be done, which way is right for you depends on many factors but in all cases the two transactions must equal each other

one of the easier ways is simply to have a record with a date, the ledger account to be debited, the ledger account to be credited and the amount.

you may require additional transactions - you’ve sold something so in addition you need to credit stock and debit cost if sales

you may have other things to account for such as sales tax

Then you need to handle items returned, purchased, etc

and this method won’t handle an accounting journal where typically you debit and credit multiple ledger accounts where total debits=total credit

you only mention sales and receipts so presumably you don’t want a sales ledger just a way to see who owes you what. As others have said, this data is already in your tables so link them together on customer Id in an aggregate query