Results 1 to 2 of 2
  1. #1
    Beachbum is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012

    Problem with lookups


    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.

    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?


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Don't do data entry in queries that join tables. If you want to do data entry directly in tables, will be easier to manage if set up relationships in the Relationships builder first. This will establish table/subtable associations.

    Review this article about relationships
    This article about editing records
    And this

    Are you the only user of this db? Developers normally don't build databases that allow users to work directly with tables and queries. Use forms as data entry interface and reports for data output.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 09-06-2011, 08:00 PM
  2. Optimising lookups in a large fixed table
    By u38cg in forum Queries
    Replies: 4
    Last Post: 06-22-2011, 08:21 AM
  3. No Lookups in a Table
    By oleBucky in forum Database Design
    Replies: 9
    Last Post: 03-23-2011, 01:40 PM
  4. lookups and data selection help
    By benjammin in forum Access
    Replies: 3
    Last Post: 11-28-2010, 04:45 PM
  5. Access ADP & Lookups
    By sql_dan in forum Access
    Replies: 0
    Last Post: 06-09-2010, 04:25 AM

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 - Senior Forums