Access - display data from related table

Posted on 2012-08-21
Medium Priority
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
  • 4
LVL 85
ID: 38317385
How are users "pulling up data"? Are they doing so through a Form, Report, Query, etc?
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 38317563
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
ID: 38317622
like this quick and dirty sample
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38317630
...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

ID: 38325880
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
ID: 38326238

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

589 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