Ledger in MS Access Database

[junne]

hey guys please help me I'm practicing accounting on ms access and I'm creating a General ledger but I have know idea how to make it like this.

[twinnyfo]

Hi junne,

I would build your table very similarly to how it is displayed, except that instead of hainvg a separate field for Debit and Credit, I would have a field for transaction type and then a field for transaction amount (in all positive numbers). Then, depending upon the type of transaction, the transaction amount is rendered as either a debit or credit. One of your transaction types could be "Beginning Balance", which could either be 0 or an initial deposit (but such a thing would not be required).

I also would not store the "Balance" field, but calculate it real time, as there could be transactions which are reconciled at different dates/times.

Hope this hepps!

[Nauticalgent]

Is this something you are just doing to sharpen your skills or do you plan on using it?

If it's the former then others may be inclined to help you out. If it's the latter, then there are much better solutions available right of the shelf (Quicken, QuickBooks)! In this particular case, IMHO, even Excel (Access Gods forgive me) would be better suited for this task.

[junne]

hi @twinnyfo for the function Calculation part are you gonna use Query or a VBA code.

[junne]

hi @Nauticalgent

I'm sharpening my skills and i want to learn more about calculation in ms access :)

[Nauticalgent]

Then sharpen away! That whole running total thing you want to do is possible, I just don’t know how I would do it and give you the ledger-like appearance. My Kung-fu just isn’t that good!

[PhilOfWalton]

This may help. The table I use has a PaymentAmount which can be either >=0 (Credit) or <0 (Debit)

You also need an Autonumber (PaymentID) so that transactions are kept in order.

This is the SQL I use. Note that where I have got MemberID, you will need Invoice No

SELECT Payments.PaymentDate, Payments.MemberID, Payments.PaymentAmount, Payments.PaymentID, IIf([PaymentAmount]>=0,[PaymentAmount]) AS Credit, IIf([PaymentAmount]<0,[PaymentAmount]) AS Debit, DSum("[PaymentAmount]","Payments","[PaymentID]<=" & [PaymentID]) AS Balance
FROM Payments
ORDER BY Payments.PaymentID;
 

Hope this gets you started.

Phil

[twinnyfo]

Great start, Phil! That's where I was going to start, but you beat me to it!

[junne]

thanks for the help @PhilOfWalton,@twinnyfo and @Nauticalgent
it really helps a lot :)

[PhilOfWalton]

That's what we're here for. A pleasure.

Phil