Results 1 to 3 of 3
  1. #1
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38

    Foreign Key Query - Outputting the Information, Not the Foreign Key #


    Hi all,

    I'm querying a table that has multiple foreign keys in it. It's an inventory table. Instead of giving users the opportunity to misspell everything under the sun, I created other tables with brand names, equipment types, etc., and users pick from combo boxes. Their choices are stored in the main inventory table as the foreign keys from the other tables, if I'm using my terminology correctly.

    I designed a query that outputs a lot of those fields from the inventory table - meaning I output a lot of foreign keys. I would like to output the actual information (brand name, etc.). The only way I've figured out is to go into the query design and set lookup properties for each field, but as far as I can tell the only way to do that is through combo boxes and list boxes. Is there some way to design the query to output the readable text without creating combo and list boxes in the resulting query datasheet view?

    Also, I run into the same problem when creating reports. The foreign keys show up instead of the clear text. How do I fix this?


    All help is greatly appreciated.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,262
    Add the other tables (brand names, equipment types, etc) to your query. If you have already set up your relationships in the Relationships window, they will be joined to the inventory table automatically, other wise you will need to create the joins yourself. Then drag the description field from each table to create a column for each.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    SeaTigr is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2011
    Location
    U.S.A.
    Posts
    38
    Thank you kindly. I also figured out that my foreign keys were being stored as text, not numbers - giving me a type mismatch error.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-15-2012, 02:11 PM
  2. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 PM
  3. How to query using a foreign-keyed name
    By david28 in forum Queries
    Replies: 0
    Last Post: 10-17-2009, 12:57 PM
  4. Getting a foreign key set
    By bkelly in forum Access
    Replies: 5
    Last Post: 08-18-2009, 09:22 PM
  5. Foreign Data
    By bmiller in forum Queries
    Replies: 0
    Last Post: 03-21-2006, 01:02 PM

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