Results 1 to 7 of 7
  1. #1
    stephena is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    3

    Query with join and multiple WHERE conditions not working

    I've got a problem with a query that I am putting together. I have two related tables. One containing keywords (Assigned_Subject_Keywords_Table) related to records in the first table (Title_table). I am trying to search for records in the Title_table that may be assigned to multiple subject keywords using an AND operator. Each time I run the query it doesn't return any records. However if I substitute the AND operator for a OR operator it works fine. However this is obviously not what I am after. I need to return only those records assigned to multiple subject keywords.



    SELECT DISTINCTROW Title_table.* FROM Title_table LEFT JOIN Assigned_Subject_Keywords_Table ON Title_table.TitleID = Assigned_Subject_Keywords_Table.TitleID WHERE Assigned_Subject_Keywords_Table.SubjectID = 220 AND Assigned_Subject_Keywords_Table.SubjectID = 84

    it has been suggested to me to incorporate an aggregate function and loof for records with a count > 1. However I am a bit unsure how to do this.

    Many thanks in advance for your assistance.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How can the same field be equal to both 220 AND 84?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show us the structure of your tables.
    We need to see the fields involved to help with a query using aggregate functions.

  4. #4
    stephena is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    3
    My apologies. I thought my original post may have been confusing.

    The main table is the Title_Table. It contains listings of publications, Title, Subtitle, Author etc. The key field of this table is TitleID.

    The Title table is related to the Assigned_Subject_Keywords_Table. It has two fields only. TitleID and SubjectID. There is a 1 to many relationship between the Title_table and this table. This table can contain multiple entries for different subjects that are related to the main parent record in Title_table. In other words multiple subjects can be associated to a single record for a publication.

    I am trying to search the Assigned_Subject_Keywords_Table in the SubjectID. In this case I am trying to find instanaces of Subject 84 and 220 and determine the TitleID of those records and ultimately retrieve the record in Title_table.

    I hope that clears it up. I'm happy to provide any further information that might help.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    OR is the correct operator for what you want. Your WhereCondition insists that the field be equal to both 220 and 84 at the same time!

  6. #6
    stephena is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    3
    Thanks Rural Guy. That makes sense. Do you have any sggestions as to how I would construct a correct query?

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    No, I'm sorry but I do not.

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

Similar Threads

  1. iif statement with multiple conditions
    By ragsgold in forum Queries
    Replies: 7
    Last Post: 08-24-2011, 05:38 PM
  2. Update Query - Multiple SETS and WHERE conditions
    By jasonbarnes in forum Queries
    Replies: 26
    Last Post: 12-15-2010, 01:08 PM
  3. Edit Query Data with multiple JOIN
    By Bruce in forum Queries
    Replies: 7
    Last Post: 07-08-2010, 05:20 PM
  4. Replies: 5
    Last Post: 06-19-2010, 07:55 PM
  5. Expression with multiple conditions
    By techexpressinc in forum Queries
    Replies: 3
    Last Post: 06-19-2009, 08:33 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