Results 1 to 3 of 3
  1. #1
    SRILes is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    1

    Simple select statement that uses a field that references another table

    Hey,

    I'm trying to build a simple query to display some records based on their status.

    The problem is, the record with the status field, the status references a table called STATUS.

    My table as follows:
    PROJECTS table

    ID Project Name Status
    1 Proj1 DRAFT
    2 Proj2 DRAFT

    STATUS table

    ID Name Description
    1 DRAFT The proj is still being drafted
    2 SUBMITED the proj has been submitted.

    My problem is, when I want to list the projects that have a status of DRAFT for example, I cannot reference the status field directly.

    This is my current SQL select statement:

    SELECT *
    FROM PROJECTS


    WHERE Status="DRAFT";

    I'm new to Access 2010, is there special syntax that I need to use to access a field that references another table?

    Any help would be greatly appreciated.

    SRILes

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't understand the issue. Projects table has Status field. Use it to filter records.

    However, if the Status field is a lookup field (I never build lookups in table) and actually has the ID from Status table, not the descriptive text "DRAFT", then use the ID to filter records.

    Or, build query that joins the two tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    Your query should show all fields from the Projects table, but not any fields from the Status table. To see records from the Status table, you need to add the Status table to your query and select the fields to see from that table also.

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

Similar Threads

  1. Replies: 3
    Last Post: 05-13-2015, 08:36 AM
  2. Replies: 13
    Last Post: 03-12-2015, 05:55 PM
  3. Calculated field that references row below
    By Access_Novice in forum Queries
    Replies: 15
    Last Post: 08-25-2014, 10:32 PM
  4. select statement with variable table name
    By dv89k in forum Queries
    Replies: 1
    Last Post: 05-26-2011, 10:54 PM
  5. Replies: 2
    Last Post: 08-26-2009, 10:43 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