How do I get the balance due from each tenant?
I have two tables
TblTenantInfo and TblLedger

TblTenantInfo has two fields
TenantID
StartingBalanceAmount

TblLedger has 5 fields
TenantID
LedgerDate
ChargeAmount
PaymentAmount
Bill yes/no

The [TblTenantInfo] table has one record for every tenant.
The TblLedger has one record for each payment received from or item charged to each tenant.
I want to see the balance due from each tenant Where Bill=no
I want to group on the TenantID
the balance due for each tenant = the sum of all his ChargeAmount + the sum of all his PaymentAmount where Bill=No + his StartingBalanceAmount
the resulting query should have two columns TenantID and the balance due from each tenant.
And It should produce 1 row for each tenant.
Thank you
steven

THis would be much more straightforward in a properly normalized design.

The better approach is to have ONE field in the ledger table for "TransactionAmount" and a second field in the ledger table for "TransactionType". "TransactionTypes" would be identified as "Charge" or "Payment". "TransactionAmounts" entered as charges would be positive, adding to the orirginal starting balance amount. "TransactionAmounts" entered as payments would be negative, subtracting from the orirginal starting balance amount. This would allow you to simply sum the entries in that field for each tenant and get the balance which you could then add to or subtract from the orirginal starting balance amount.

I'm not sure I understand the use or meaning of the "Bill" field. Does that mean, for example, you've sent a bill to the tenant? Or that this transaction is billable to the tenant? Or possiblly that the amount is owed and you have to generate a bill for it? I think it's the latter, but it would be good to have confirmation of that.

Also, can you validate that the correct calcultion is Sum of Charges "PLUS" sum of Payments where bill = No "PLUS" starting account balance? That would, I believe, be correct if "ChargeAmounts" are negative as noted above.

Let's start by seeing if you can't correct the table design and then work on the query to get the right result.

George

On further thought, I realized that my initial response, while correct as far as it goes, is incomplete.

You need at least three tables, not two.

The first and last tables are the current tenant table and the revised transaction detail table I described. However you also need a table in which the transactions themselves are recorded. It will be built something like this.

tblTransaction
===========
TransactionID (Primary Key, Autonumber)
TenantID (Foreign Key from tblTenant, Long Integer)
TransactionTypeID (Foreign Key from a look up table listing all possible transaction types for tenants, e.g. rent?)
TransactionDescription
TransactionAmount
TransactionBillabletoClient (Yes/No field from your current transaction payments table)

In the TransactionPayments Table, you will replace the tenant ID with the appropriate transactionID

This allows you to create a transaction for each tenant, and track multiple amounts (charges and payments) against the transaction.

George

Bill = yes for the line items i print on the bill eg.
august 15 payment $445.00,
September 1 Rent $445.00
when bill = no i just want the sum to show the previous balance.
The calcultion is "Sum of Charges where bill = No "Minus" sum of Payments where bill = No "PLUS" StartingBalanceAmount

The StartingBalanceAmount is the balance befor we started keeping records on the computor.

I like having charges and payments in two seperate fields. do i have to change that?

I not sure what you mean by the third table. But I may already have it.
I have a lease table it contains the date, Item, and amount being charged to each tenant .
Item is always “RENT”
And on the first of every month I append the current charges for that month from my lease table and put it into the ledger table.

The lease table tells you what the tenant owes for rent. As I'm thinking about this more, and with the additional information you provided, it seems that the "Lease" table is actually the"transacation" table I'm describing.

However, in that table, you should have OTHER charges in addition to simply "rent". Some of them will be billable to the client and some won't.

Rent, of course, is always billable. Utilities for which the tenant is responsible under the lease are also billable. Repairs to the premises resulting from damage for which the tenant is responsible are also billable. Regular saftey inspections by the local Fire and Saftety authorities are probably not billable, unless your leases specify they are, and so on.

The more I think about this, I see no reason to have a separate "Charged" field in the payments table at all, which really only should record payments against the charges made.

The way I'd probably end up doing this is with four (or five tables), two of which are simple lookups to supply values for the charges levied against a tenant's account.

With regard to the question as to whether you have to change the table design. I think it is possible to work with a non-normalized design, and in some cases people do compromise in that regard to get other benefits. However, it is almost always harder to work with and maintain a non-normalized design. We're talking right now, in fact, about getting summary results because the current two-field design is more difficult to work with. So, my honest answer is "no, you don't have to." However, do so knowing that you're probably incurring more work and less flexiibility in your application as a result.

Let me think a bit more about a good design. Actually, there are probably some templates out there that might be good starters (although I've not seen one myself). Have you looked around to see if there are some that might give you some ideas to recycle into your project?

Here's a quick and dirty demo of some of the ideas I was describing.

It contains a mix of 2007 macros and VBA (I'm trying to do more demos with macros as self-discipline).

The entry form for tenant charges and payments may not be entirely intuitive. If you play with it a while, you should see what it's about, and of course you know the content already.

One of the things I tried to figure out is how to manage reports for billable and non-billable charges. In this quick sample, I only added a single report with both combined.

Hope this gives you some idesa to help you move forward.

George

Fixed minor query error in attachment
Edited by: GroverParkGeorge on Sun Aug 30 23:07:36 EDT 2009.

Edited by: GroverParkGeorge on Mon Aug 31 1:29:35 EDT 2009.

Edited by: GroverParkGeorge on Mon Aug 31 1:57:15 EDT 2009.

thanks
when i recive payments. i dont apply it to any spcific charge .
so i dont think i need another table.

You don't track what payment are for in any way? The system just credits payments to the tenant's account? I guess that works for your business and probably others as well. This seems, in a way, closer in concept to a loan account, where the initial dispersal is repaid in installments and all of the payments go aginst that initial amount. I(t's also one of the reasons why designing a "one size fits all template" is so hard, btw.)

Every set of business rules is potentially different.

Given that, to get what you seem to need for calculating balances due, you'll just need to create two queries: one that shows the sum of all charges and one that shows the sum of all payments. Then, combine them, as I did in the balance due query in the demo, so that you can get the difference between them. Since the accounts are only at the tenant level, and not by charge type, then all you need in the balance due query is the sum and the tenant ID. That should do it.
George

Here's a version with both types of account tables: one for tracking individual transactions with payments credited by transaction, and the other just basically an account, with all charges and payments credited by tenant account.

As you can see the queries are similar in some ways.

I didn't get around to creating the second report (payments and charges by account). I think we'll leave that to the folks who want to try it out to do for themselves.

George

Posted on 08/30/09 10:45 PM Attachment (5 downloads)
Posted by GroverParkGeorge - Utter Curmudgeon
Posts: 9733 - Loc: Gresham, OR

Forum: Access Queries
why does this post say 5 downloads -And i only see one access file?

Because there is only one file that has been downloaded five times. Quite intuitive isn't it.

Hmm. There should have been a new attachment.