Excel to do Ledger

Selamat pagi sythong,

This is easy via a query table. No formulas - uses database functionality.

I've done a sample file and will try and email it to you.

Fazza

Fazza

I think you have a winner on your hands.
After tinkering with it I found that it is indeed possible
to generate a ledger from the database.

Unfortunately since I do not have any knowledge of writing codes
I am unable to structure for what I am to achieve. I do need to
put in additional columns and also the drop down list needs to
be expanded to include all items in the debit credit column.

Regards.

Thong,

I have emailed you an updated file. As mentioned in the email, additional fields were added by modifying the query tables' SQL string. Which will not mean much if you are not familiar with Query Tables and SQL.

By way of explanation, I called the source data table MD and had the following parameterized SQL - linked to the single cell where the debtor is selected from the data validation list. The single cell has a header field and the two cell table/range is called Db. Note, this is to suit the example posted above, not the extra fields subsequently added. The data validation list feeding into the single cell is generated by another query table - a list of distinct entries in the data table fields of [Debit] and [Credit]. The query table refreshes on changes to the single cell and this use of query tables offers in a deceptively simple way powerful database functionality.

HTH
Fazza

SELECT MD.Date, MD.Doc, MD.`Dr Amt`,0 AS [Cr Amt]
FROM `H:\file`.MD MD,`H:\file`.Db Db
WHERE (MD.Debit=?) AND (MD.Debit=Db.Debtor)
UNION ALL
SELECT MD.Date, MD.Doc, 0 AS [Dr Amt], MD.`Cr Amt`
FROM `H:\file`.MD MD,`H:\file`.Db Db
WHERE (MD.Credit=?) AND (MD.Credit=Db.Debtor)
ORDER BY MD.Date

Fazza

I am really grateful to you for pointing me in the
right direction. Will look up MS Query and SQL to
familarise myself so that I can expand upon your
existing structure.

This would really cut down a lot of work as all incoming
XL files such as sales, purchase, cash and bank can
be cut and paste and voila the GL can be generated.
I am also looking into creating the P&L and Balance Sheet
once I reached the level of understanding required to
formulate these.

Once again many many thanks for your time and expertise
in coming up with such a pratical and amazing solution.

Regards.


Thong

Thanks, Thong.
Regards, Fazza

Thong,

Some recent threads


http://www.mrexcel.com/board2/viewtopic.php?t=213876&start=0

http://www.mrexcel.com/board2/viewtopic.php?t=214277&start=0

http://www.mrexcel.com/board2/viewtopic.php?t=214038&start=0

http://www.mrexcel.com/board2/viewtopic.php?t=214558

Fazza

Thanks. You are extremely kind and helpful.
Just what I needed. Additional info to steer me in the
right direction.

Thong