I am a brand new Access user, working on my first database. I work for a non-profit organization providing housing for homeless individuals with disabilities. We serve about 150 clients - some living in apartments that we own, and others living in apartments that we rent from landlords in the community using grant funds. Right now I have two tables with client data: Tenants for those living in our properties, and Clients for those living in the community.
What I want to do is to create a Lease table with the names from both the Tenants table and the Clients table with data about their lease - when it started, when it ends, when inspections are due, etc. I don't want to have to enter the client/tenants names and address data in the new table (if I understand the concept of normalization) but have that information drawn from the existing tables. Access created the one to many relationship shown in the picture above. I don't know if that's right, or if I'm even on the right track.
Is there an easier way to do this?