Results 1 to 9 of 9
  1. #1
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110

    Intersect


    Hello,

    I would like to find the intersection of two queries. Is the SQL keyword INTERSECT supported in Access?

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,408
    No. Just join the 2 tables. The result will be the intersect.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    No, it wouldn't be.

    the SQL INTERSECT command tells you what values are on the LEFT side of a join that aren't on the RIGHT side of a join.

    In your case in the TO side (the table that your join arrow is pointing to) you would want to put in IS NULL in the criteria of the join field. SO let's say I had two tables

    Table1 (PK1 as the join field) and Table2 (PK2 as the join field)

    You'd create your query joining Table1 to Table2 on the PK1 to PK2 field, double click the link between the two tables and select the option to include all records from TABLE1 and only those records with from TABLE2 where the link criteria was present. Then put IS NOT NULL in the PK2 field of table 2.

  4. #4
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    The queries are created on the fly in a VBA code. My code takes user specified criteria and creates a string of SQL code to find those records. Users search for a product based on multiple related tables. For each related table, I create a string of SQL code that returns a distict list of products. I want to use INTERSECT to combine these seperate queries into one string and apply it.

    How would I use a join instead?
    Maybe create empty tables, and join them. Then in code, apply the individual SQL code to each empty table. Then join table would be the intersection of the specified queries. Does that sound alright? (New to Access)

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Have you defined which fields are in which tables, and what the join fields between tables will be? I am not sure you can programmatically retrieve relationship information from the database (I've never tried) to determine which tables are related to which others by which fields. Personally I do not set up relationships, I find them more trouble than they are worth but I don't see how you could build a table without knowing HOW your tables are related to one another and what the linking criteria were. Perhaps I'm misunderstanding your question, if you have a working query you are just looking for a way to apply criteria to that I would like to see an example database.

  6. #6
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    (As rpeare suggested, you should use a LEFT OUTER JOIN here with a where condition that checks the join key field of the right table to be NULL. It will give you all records from the left table that don't have a match in the right table.)

    Misinterpreted your post, ignore my blubbering.
    Last edited by hapm; 06-12-2014 at 11:06 AM. Reason: misunderstood OP

  7. #7
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    Sorry VIP but I don't understand your question. I have a Products table that has a 1 to Many relationship with Features table. It also has a 1 to Many relationship with Performance table. I want to allow my user to find all the products that have specifci features and perfromance parameters. My plan (before I discovered Access doesn' allow INTERSECT) was to build one string of SQL code that would Select all products with the user specified features and another string of SQL code that would Select all products with user specified performance parameters. Then concatenate the two strings with INTERSECT and set that string of SQL as the form's record source. Does that clear anything up?

    I've done nothig yet with your join table idea because I'm not sure how to use a join table to solve my problem.

    I just want to build a user search interface. So if my approach won't work, please tell me. I'm new to Access and have been unable to find any guidance on how to build a search interface that looks at more than 1 table. I know it has to be possible or no one would ever develop in Access.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    you posted another thread about this problem didn't you?

  9. #9
    DB88 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    110
    I've posted many thread about this problem. I try a new apprach and ask for help on the individual details. I'm getting closer to a solution and learning a lot in trial & error

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

Similar Threads

  1. Delete Table intersect.
    By suverman in forum Queries
    Replies: 2
    Last Post: 05-13-2011, 07:11 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 - Senior Forums