Results 1 to 2 of 2
  1. #1
    BethHedges is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    1

    Is it possible for a field to pull data from two different fields in other tables?

    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.


    Click image for larger version. 

Name:	db relationships.jpg 
Views:	26 
Size:	75.4 KB 
ID:	31043
    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?

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I believe tables should be tblClients, tblProperties, tblLease

    tblClient would have all your clients regardless if they live in owned or rented property.
    ClientID(autonumber)
    ClientName
    etc.

    tblProperty has all your properties you own or rent including a field to select either "Rental" or "Owned".
    PropertyID(autonumber)
    Property Address
    RentOwn (combo box contains "Own" and "Rent"
    etc.

    tblLease combines the Client to the Property.
    LeaseID(autonumber)
    ClientID
    PropertyID
    LeaseStartDate
    LeaseEndDate
    Etc.

    So in a query, you link tblLease both tables: tblClients by ClientID and tblProperty by PropertyID

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-07-2014, 07:31 AM
  2. Query doesn't pull all data from tables
    By CARANJ in forum Queries
    Replies: 5
    Last Post: 02-12-2014, 02:02 PM
  3. Replies: 2
    Last Post: 03-07-2013, 04:50 PM
  4. Replies: 1
    Last Post: 01-14-2013, 11:28 AM
  5. Replies: 5
    Last Post: 12-22-2011, 11:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums