Solved ledger help

[ZEEq]

hello everyone!
my question is regarding general ledger i have student fee invoice table and student payment table if i want to make a ledger of invoice and payment transactions do i need a balance column in ledger table? should i use same amount field for invoice and payments? i want to show running totals or instead of this ledger table approach can i achieve this by any other method this is going to be very large database i need to consider speed and reliability as well please guide me to right direction
regards

 

[pbaldy]

Do not try to maintain a balance field, just calculate it from transactions. Personally I would use the same field for invoices and payments, with payments being negative amounts. A running total can be calculated on the form/report you're presenting to the user.

 

[ZEEq]

thanks @pbaldy for swift reply, Please tell me should i go for ledger table approach? every month invoices are inserted into student fee invoice table through append query how will i copy invoices into ledger table?

 

[pbaldy]

I'm not clear on your table structure, perhaps you can post a pic? If you're already putting the invoices in the student fee table, I don't get the purpose of the ledger table. I'd have a single table for invoices and payments, but you could have a payments table in addition to your fee table. You'd have to join them together to get balances, so in my mind less efficient.

 

[ZEEq]

I'm not clear on your table structure, perhaps you can post a pic? If you're already putting the invoices in the student fee table, I don't get the purpose of the ledger table. I'd have a single table for invoices and payments, but you could have a payments table in addition to your fee table. You'd have to join them together to get balances, so in my mind less efficient.

i have separate table for invoices and payments
Do i need a ledger table for balances or should i go for query approach?

 

[pbaldy]

No, I wouldn't have another table. I'd create a union query joining those two tables, and then calculate balances and such from that. Without knowing your table structure I'd guess you'd be pulling student ID, payment/invoice amount, and date from each table. You'd want payments/credits as a negative, so if they aren't in the payments table just multiply times -1 in your query.

 

[pbaldy]

More here:

Use a union query to combine multiple queries into a single result - Microsoft Support

In Access get a combined view of multiple select queries with a union query.

support.microsoft.com

 

[ZEEq]

do i need a union query if i have invoiceID in payments table?

 

[pbaldy]

You could try a regular query that joins the two tables, with a LEFT or RIGHT join on the invoices table, so invoices with no payments show up. Can you attach the db here?

 

[arnelgp]

this is same as proposed.
create a query that will total the invoices (TotalInvoiceQ).
next create another query that will create a running total of all payments (InvoicePaymentsQ)

 

[ZEEq]

this is same as proposed.
create a query that will total the invoices (TotalInvoiceQ).
next create another query that will create a running total of all payments (InvoicePaymentsQ)

perfect thank you @arnelgp

 

[ZEEq]

thanks a lot @pbaldy