I need help adding some transactions to the vendor ledger.

[Khurramshaikh47]

I have a voucher form with the help of which I do voucher entries. Each entry is posted in a table called GeneralLedger. I then display the entries for specific heads of accounts from this table in a separate report, such as all transactions for a specific vendor. Let me share some images to show what is going on now:

I am using this voucher form for the entries (Below is an example of a vendor that provides dyeing services):

There are 4 heads of account used in the voucher above.

1) Dyeing-Charges (The service)

2) G.Sale Tax (The Sales Tax)

3) ABC Dyeing & Finishing (The vendor)

4) WHT Income Tax (The Withholding Tax).
I have a report that provides me the details for each head of accounts its working fine for Cash In Hand and Bank etc, like:

But when I want to get the report of each supplier like ABC Dyeing & Finishing:

It does not show dying charges, in this report. While I also want to show details of services vendor's ledger reports.

Let me show you which type of report I want for the vendors/suppliers:

Please gude me how can I do it.

Thank you in advance.

 

[plog]

When you want to convey data requests (request for a query or report) its best just to show us 2 sets of data---"I'm starting with this data and I want this result. Everything else just tends to confuse. " . Also that starting data needs to be from your tables, and not a screenshot of your form. To get to a report often you need to build a query and a form doesn't provide us enough information to build those queries.

That very last image of the report you want is perfect--it shows us what you want to end up with. Now we need to know where you are starting from. Please post images of all the tables that data will come from in making that report.

 

[Khurramshaikh47]

These are the tables and queries

I use Voucher Table and GeneralLedger Table for the entry of debit credit transactions. Let me show you both tables:
1) Voucher Table:

2) General Ledger Table:

GeneralLedger Table also has more columns, I have hidden them to show the necessary columns.
3) AccountLedger0 to use the WHERE Clause to get the ledger for a specific vendor from a specific date range.

4) AccountLedger to get the running balance of Debit & Credit entries from AccountLedger0

When I tried to create a report with the help of Account Ledger Query, instead of giving the total of the columns, it was giving an error.

I got help from arnelgp, A new query was created to get the report,
5) AccLedgerForReport:

I may have given too much detail and screenshots. Or maybe very little detail. If so, I apologize. I can also share the sample database file if you want. I will be grateful for your guidance in this project.

 

[plog]

Too much. Here's the only things I am looking at--the expected report from Post 1, and your tables from Post 3. The first thing I see with your tables is that none of them has a field which has just the value "ABC DYEING & FINISHING" that is the very first data element in your report. What table will that come from?

With that said, I see bigger issues with your tables:

1. Credits and Debits in seperate fields. Because you ultimately want to do math on them together, those values should be in the same field. Instead of [Credit] and [Debit] fields you should just have 1 [Amount] field that holds both---negative numbers for debits, positive numbers for credits.

2. Storing data in multiple places. GeneralLedger should not have VoucherDate nor Narration, Since it has VoucherNo, that's the only value you need in GeneralLedger. With it you can then JOIN to Voucher table and get those other fields.

 

[Khurramshaikh47]

Too much. Here's the only things I am looking at--the expected report from Post 1, and your tables from Post 3. The first thing I see with your tables is that none of them has a field which has just the value "ABC DYEING & FINISHING" that is the very first data element in your report. What table will that come from?

With that said, I see bigger issues with your tables:

1. Credits and Debits in seperate fields. Because you ultimately want to do math on them together, those values should be in the same field. Instead of [Credit] and [Debit] fields you should just have 1 [Amount] field that holds both---negative numbers for debits, positive numbers for credits.

2. Storing data in multiple places. GeneralLedger should not have VoucherDate nor Narration, Since it has VoucherNo, that's the only value you need in GeneralLedger. With it you can then JOIN to Voucher table and get those other fields.

Thank you so much plog. I apologize for the late reply. I tried rebuilding the entire database where I let the Narration and VoucherDate remain in the voucher table while trying to link to the voucher number. I failed because my voucher number field is a calculated field.

 

[Khurramshaikh47]

Actually, I can only use debit and credit fields separately as this is the requirement of my project.

Too much. Here's the only things I am looking at--the expected report from Post 1, and your tables from Post 3. The first thing I see with your tables is that none of them has a field which has just the value "ABC DYEING & FINISHING" that is the very first data element in your report. What table will that come from?

With that said, I see bigger issues with your tables:

1. Credits and Debits in seperate fields. Because you ultimately want to do math on them together, those values should be in the same field. Instead of [Credit] and [Debit] fields you should just have 1 [Amount] field that holds both---negative numbers for debits, positive numbers for credits.

2. Storing data in multiple places. GeneralLedger should not have VoucherDate nor Narration, Since it has VoucherNo, that's the only value you need in GeneralLedger. With it you can then JOIN to Voucher table and get those other fields.

 

[Khurramshaikh47]

I have looked up a filed VName from ChartOfAccount table to voucher and then used the VBA the following code:

Private Sub VName_AfterUpdate()
    DoCmd.RunSQL ("update GeneralLedger set VName= '" & Me.VName & "'  where VoucherNo like '" & Me.VoucherNo & "'")
End Sub

to post its value (Title of Account) in the GeneralLedger Table. But when I am trying to run this code instead of Name of Account its ID's are appearing.

Can anyone please guide me on how to change this ID (63) to the name (ABC DYEING & FINISHING).

 

[jdraw]

??? Are you using Lookup field(s) in your table(s)??
Can you show your tables and relationships(expand tables to show all fields)?

 

[Khurramshaikh47]

??? Are you using Lookup field(s) in your table(s)??
Can you show your tables and relationships(expand tables to show all fields)?

Thank you for your response jdraw, Yes sure,
Lookup VName from ChartOfAccount Table in Voucher Table.

Voucher Table

General Ledger Table: