Ambiguous outer joins

[Stephen Glynn]

I'm always confused by these. Maybe someone could give an idiot's
guide to where I'm going wrong.

I'm trying to rough out a database to track tenancy agreements. It's
obviously going to be lot more complicated than this but I'm trying to
get the basics right first.

At present I've got three relevant tables: tblTenant, tblProperty; and
TblAgreement.

Each has got an autonumber primary key. The information stored in the
Tenant and the Property table are obvious. The agreement table stores
the agreement number (the primary key), the property number, the tenant
number (both secondary keys), the agreement type and the date of
signature.

In my system all tenants are listed on the same tenacy agreement -- if
someone acquires or loses a roomate or partner I cancel the old
agreement and set up a new one.

Consequently, I've potentially got several tenants occupying the same
property, so that's a one-to-many join. (One property, many tenants)

I've potentially got several agreements relating to the same property
(though only one will be valid at a time). One to many again.

How do I put together an updatable query to add new tenants and create a
new agreement? I want to use this to build a form for adding new
tenants. The idea is the operator will select a property from a
drop-down list, enter the details of the tenant(s) and generate a new
agreement number automatically.

If I try to buld a query using all three tables I get an ambiguous outer
joins error message.

If I try writing two separate queries:

qryPropertiesAndTenants

SELECT [Properties].[PropertyID], [Properties].[Address],
[Tenants].[Title], [Tenants].[FirstName], [Tenants].[LastName]
FROM Properties
LEFT JOIN Tenants ON [Properties].[PropertyID]=[Tenants].[PropertyID];

and

qryPropertiesAndAgreements

SELECT Properties.PropertyID, Agreements.AgreementNumber,
Properties.Address, Agreements.PropertyNumber, Agreements.TenantNumber
FROM Properties
LEFT JOIN Agreements ON Properties.PropertyID =
Agreements.PropertyNumber
ORDER BY Agreements.AgreementNumber;

and then try to combine them as


SELECT qryTenantsAndProperty.PropertyID, qryTenantsAndProperty.Address,
qryTenantsAndProperty.FirstName, qryTenantsAndProperty.LastName,
qryPropertyAndAgreement.AgreementNumber,
qryPropertyAndAgreement.PropertyID
FROM qryTenantsAndProperty
INNER JOIN qryPropertyAndAgreement ON qryTenantsAndProperty.PropertyID =
qryPropertyAndAgreement.PropertyID;


my query results are not updatable. And I've got no obvious way I can
see of automatically linking my tenants to the agreements.

Clearly I'm going badly wrong somewhere, but where and why?

 

[Rolls]

Create a main form to select the property. Within this form add two
subforms a) tenant and b) agreement. You have a parent-child relationship
between:

property < tenant
property < agreement

or maybe it's:

property < agreement < tenant

This will let you select a property, associate the current agreement with
the property, and then associate the tenants with the agreement OR property
whichever applies. Suggest that you use a checkbox yes/no field to indicate
the active agreement.

The frmMain : frmSub relationship will associate the tables * without * the
need to build your troublesome queries!