Aloha,
Rusty, non-programmer, trying to build an improved DB (from questionable memory) based on one that I developed, and worked well for me 10 years ago.
Tables:
Building, Unit (or Apartment), Management Contract Details, Owner, Tenant, Lease Contract Details, Parking Contracts, Vendors, Work Orders
Each table has an autonumber primary key ID field, such as Building ID.
Each building might have (one or) many Units.
Each Management Contract might be for only one, multiple, or all units in one building.
There might be multiple Management Contracts for one building (separate Units) Think Condo ownership.
Each Management Contract might have multiple Owners.
Each Owner might have multiple Management Contracts (separate Buildings or Units).
Each Unit might have multiple Tenants.
Each Tenant might have multiple Lease Contracts (same unit, or separate units).
Each Tenant might have multiple Parking Contracts.
The problem(s) I am having are with the use of lookups, and/or join issues I believe. Pretty sure it is a fairly simple issue, but I can't seem to nail it down without duplicate entries. I'm seeing the ID which is meaningless, instead of address and unit. Additionally, when I run a query, I get the maximum possible number of records...for example, every tenant with duplicate addresses for each tenant of a unit, rather than showing one address with each tenant name for that unit.
On data entry for each table, I need to select the proper address and unit, which is the only positive (and meaningful) identifying element for each records information. (I'm not trying to input the address into each table, but I need to reference it along with another field in each table to determine which selection to make.) My expectation is that new data needs to be input to tables in a particular sequence: Building, Unit, Mgmt Contr, Owner, Tenant, Lease, Parking. Each table builds on (or looks up) certain data from the previous, in other words if I try to input Mgmt Contr first, there would be no correct Building address available to select. Similarly, I could not input Tenant data first, as there would not be a correct Unit to select.
Does this make any sense?
Mahalo!