Access 2007 Help Needed.

Discussion in 'OT Technology' started by Wolf, Jan 10, 2010.

  1. Wolf

    Wolf No one plans to take the path that brings you lowe

    Joined:
    Mar 23, 2003
    Messages:
    105,186
    Likes Received:
    10
    Location:
    Austin, TX
    I'm setting up an Access 2007 database for my mother and need some help from someone in the know.

    The database tracks training and SOPs at the company she works for.

    Currently, I've got about 5 tables created, covering things like the SOPs, Document Revisions, Departments, etc.

    What I'm having problems with is creating the reports she needs from the tables.

    One of the tables contains all the SOPs and also has a multi-valued field with a dropdown box containing the departments in the company. The goal is to indicate which depts need the SOP training indicated by each record. So some records will have a single dept checked in that field, some will have 4-5 depts.

    I need to be able to create a report for each dept, showing the SOPs that each dept needs to be trained on. I tried writing a query:

    Select fields
    From table
    Where [Reqd for Dept(s)] = 'R&D'

    To pull the records where R&D is one of the selected values in that field.

    But Access is giving me the error "The multi-valued field [Reqd for Dept(s)] cannot be used in a WHERE or HAVING clause."

    So how do I select these records for a report?
     
  2. Wolf

    Wolf No one plans to take the path that brings you lowe

    Joined:
    Mar 23, 2003
    Messages:
    105,186
    Likes Received:
    10
    Location:
    Austin, TX
    Well.... screw you guys. I found the answer myself. :wiggle:

    I needed to change the WHERE clause to indicate: [Reqd for Dept(s)].[Value]
    so that the query looks at each selected value individually, because it can't take the multiple values altogether.

    :wiggle:


    Guide to multi-valued fields:
    http://office.microsoft.com/en-us/access/HA012337221033.aspx#7
     
  3. Swerve

    Swerve OT Supporter

    Joined:
    Feb 20, 2005
    Messages:
    3,175
    Likes Received:
    3
    Will the guide tell me what an SOP is?
     
  4. Wolf

    Wolf No one plans to take the path that brings you lowe

    Joined:
    Mar 23, 2003
    Messages:
    105,186
    Likes Received:
    10
    Location:
    Austin, TX
    Standard Operating Procedure.

    They document procedures.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I would have done it the old way (another table).
     

Share This Page