Find records without an associated record (1 Viewer)

[DenisCooper]

Hi,

I have the two tables in my access DB:

Property Information
Lease Information

i need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.

Property Info:
PropertyID
Address

Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)

I can figure out how to show expired / not current leases, but can't seem to show where there has never been a lease on a property.

Any help appreciated.

Thanks

 

[ypma]

I Hope i am understanding your requirement. I am assuming that if there has never been a lease on a property , then the lease Start date field would be blank. if that is so you could put that into the criteria of the query such " Is Null " hope that is of help

A fellow user

 

[DenisCooper]

I Hope i am understanding your requirement. I am assuming that if there has never been a lease on a property , then the lease Start date field would be blank. if that is so you could put that into the criteria of the query such " Is Null " hope that is of help

A fellow user

thanks for getting back to me....

i don't think this will work because some properties don't have a reference in the lease table...

i only create a lease for a property when a tenant moves in, so for example, if i buy a new property, and it's not got anyone ever having lived in there, there will not be a lease at all for the property - so the lease table wouldn't reference back to that property.

 

[nanscombe]

If you have two tables and are trying to find records in one table where there is no match in the other table I suggest you might want to look at ...

Compare two tables and find records without matches

Sometimes you may want to compare two tables and identify records in one of the tables that have no corresponding records in the other table. The easiest way to identify these records is by using the Find Unmatched Query Wizard ...

This assumes that no matching record has been created, which is what I believe you are after. If a record has been created, but is not current, then something slightly different will be required.

 

[DenisCooper]

Thats great - i managed to do it in two queries, one to show me properties that never had a lease using the unmatched query, and one to show me properties with expired leases using a date filter.

Thanks