selecting specific records from table into sub form

[Terry Sumpter]

first post guys so please be 'gentle' ... i think i have a fairly basic problem, ... I have a number of rental units and am trying to create a small application that will allow me to pull each specific property, tenant, income and expenses for each property together on one form.



I have utilized a main form and with records from 2 tables 'Table1 UNIT ADDRESS' and 'Table1 TENANTS', on that form I have included two subforms 'RENT RECEIVED' and 'UNIT INVOICES'

(There are a couple of unused fields on the form yet to be eliminated these are self apparent and not interfering with anything they are a 'roundtoit' task )

My issue is how do I select just the records indicated by the unit address shown on the main form called 'FORM1 PROPERTIES' for a specific unit from the 'Table1 RENT RECEIVED' table.

Once solved I assume I can tailor that remedy for the second subform 'UNIT INVOICES'

2014 RENTAL DB forum.zip

[June7]

"Table 1 result of query" troubles me. Is this a table to save calculated aggregate data? Saving calculated data, especially aggregate data, is usually a bad idea.

I am not certain about the table relationships. Should probably associate invoices and payments with tenants, not the properties, after all, tenants - not the properties - are responsible for the rent. Each tenant is an account and accounts should have invoices and payments applied to calculate account balance.
You have UnitID set as primary key in Properties but you save the address in Received and Invoices. Either save the UnitID or set the address as primary key.

If you want the subforms to synchronize with the main form then set the subform container control Master/Child Links properties.

Synchronizing a subform to another subform requires a special trick. Review http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

A form can enter/edit records for a single table. So the main form "Form1 Properties" is not appropriate for enter/edit CurrentTenants records.

Advise no spaces or special characters/punctuation (underscore is exception) in names. All upper case is also not recommended. Better would be UnitID or Unit_ID, AmtPaid or Amt_Paid (not [$ Paid])

Why do you have Table1 as a prefix for all tables?

[Terry Sumpter]

Hi June thanks for looking, yup 'Table 1 result of query' is a bit of a white elephant, in fact it is of no use whatever, part of my struggles trying to get it to sum up rents paid re a selected unit, it is now redundant just not removed.
The 'Table1' issue, I had originally decided to prefix with 'Table , form or whatever, the '1' was an early 'typo' but then just kept it ... nothing more than that, but you are right it certainly could lead to even more confusion.
As you see I am a complete novice, so some of what you are suggesting went straight past me, ... but if I understand correctly you are saying that the primary key should ideally be the unit address rather than the 'access allocated' ID incremental digit, I guess I can amend that fairly easily but I do not grasp the advantage, would you be good enough to briefly explain please.
I decided to use PROPERTIES for the relationships because I wanted to elicit the annual income records for the unit rather than the tenant as often there are more than one tenant in a particular unit during the course of the year, the tenant details are more for contact, lease or contractual issues.
I do wish to sync the income and invoice to the main form and will read the master/child functionality tonight, thanks again.
Terry

[June7]

Many would say that 'ideally' the primary key should be the autonumber ID because numbers index faster but you are saving the text address as foreign key.

Whichever, the primary key should be the value saved as foreign key.

Should still be able to get annual aggregate data by unit even with the receipts and payments tied to the tenant accounts.

How do you plan to show tenant account balances or because there are no partial payments this is not an issue? Do you need historical data on tenants or do you delete them when they move out?