Access - display data from related table

Posted on 2012-08-21
Last Modified: 2012-08-23
This is probably super simple to people who are good with Access and spend time working on databases, but I am not either of those, so sorry if I am explaining this poorly or if I have "designed" this poorly. It is outside of my comfort zone, but It was asked of me to build it anyway. What I have is a really simple access db that has 2 tables:
House (has  HouseName = arbitrary name/address, ID = unique ID)
HouseInspection (ID=unique Id, HouseNumber=the unique Id from Hydrant table, and various other fields for keeping data)
So there is a 1 to many relationship of House to HouseInspection. The problem with that is when users pull up the data from HouseInspection, they get an ID, which is a unique ID that they don't care about, and HouseNumber, which is another unique ID that they don't care about. They only care about the name/address. So I would like to somehow display (not copy, if possible) the HouseName field somehow so that when users pull up the HouseInspection datasheet/table, they can see the HouseName (from the House table) without having to switch back and forth between the two tables looking up HouseName based on unique IDs.

This was built with Access 2007, if that matters.
Question by:ITHastings
    LVL 84
    How are users "pulling up data"? Are they doing so through a Form, Report, Query, etc?
    LVL 74

    Accepted Solution

    I think I see.

    In some cases you can create a query that joins the two tables.
    using all the fields from each table.

    Then base your form or report on this Query, ...then only display the "Human" value.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    like this quick and dirty sample
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...alternately, you can create a form from the query as well...

    This is very basic, so play around with it, so you understand how it works.

    Author Closing Comment

    Thank you for the example, it was extremely helpful. I used the query wizard and it was extremely simple to make my query and select the exact data the users need.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now