Property Management Database Design

[jbc]

Hello,

I'm designing a small database for 2 apartment buildings. There are 2
buildings and 26 tenants in one and 15 in the other.

I need to keep track of rental income, charges, credits, arrears, etc. I
have two tables:

tblTable: TenantID, Name, apt num, phone, rent, lease, etc.
tbleActivity: ActivityID, TenantID, Type(Drop down list of Rent, Charge,
Credit), Date
Relationship on TenantID

There can be multiple payments, charges, credits per month.

I want to produce a report that lists each tenant and all activity for
month. Columns for TenId, Apt, Name, Rent, Arrears, Charges, Credits,
Payments, Total Paid and Balance Due. The trouble i'm having is the rent is
being listed multiple times and the amounts are being included in the totals
at the bottom of the report.

What am I missing? I'm open to ideas on how to improve the design. Right
now I'm in the beginning stages.

Thanks.

jbc

 

[Wayne-I-M]

Hi

If it were me I would 1st remove the "drop-down" from the tblActivity.
You would be best crating a form to input data - the "drop-down" can be used
in this form (use the control source row in the properties box).

Next rename the 1st table to something other than "table".

Also in the relationships window look at the linking line between the 2
tables. It should show a 1 to many relationship from tblTable (which you are
going to rename) and tblActivity. Delete any relationships that are there
now and then drag the TenantID field from tblTable over to the tentantID
field in tblActivity – then enforce referential integrity (this means there
can not be an action in tblActivity without a corresponding record being in
tblTable)

This means that each TenantID in tblTable can have many linked records in
tblActivity. You have done the right thing by linking on the primary field
tenantID.

Most important – the payment of rent is (I think) an “Activity†so take this
out of the 1st tbl and put this field into the 2nd (tblActivity)

tblActivity
ActivityID AutoNumber
TenantID Number
Rent Currency
PaymentType Text
PaymentDate Date/Time


tblTable
TenantID AutoNumber
Name Text
AptNumber Text
LeaseRenewaleDate Date/Time

Note that AptNumber is text not a number field

Create a new query and bring the 2 tables into the top section of the page.
Bring in all the fields from tblTable and only the rent field and payment
type from tblActivity.

Look at the query in design view and at the linking line between then. (it
will be the same as in the relationship window) Right click the line and
change it to “Show all records from tblTable and only those records field
from tblActivity where the join fields are equal†- a left join.

If you click view and select SQL it should look something like this (oh this
is aircode so it may not be exactly what you see – but as near as makes no
sense “IF†you have changed the field names)

SELECT tblTable.TenantID, tblTable.Name, tblTable.AptNumber,
tblTable.LeaseRenewaleDate, tblActivity.Rent, tblActivity.PaymentType
FROM tblTable LEFT JOIN tblActivity ON tblTable.TenantID =
tblActivity.TenantID;

Note I have used the field names I gave above.

Create your report based on this query and not from the tables – and it
should be OK.

Have fun.

 

[Kerri]

Hi Wayne,
Would you consider emailing me a blank of your template? Thanks Kerri
(e-mail address removed)

 

[Steve]

If you need help .....

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

Shall I finish this for you? "Then do NOT contact PC DataSheet"

These newsgroups are for users to obtain and provide free help, not for
shills to prey on unsuspecting victims for profit.

John... Visio MVP