Hello,
I would like to find the intersection of two queries. Is the SQL keyword INTERSECT supported in Access?
Thanks
Hello,
I would like to find the intersection of two queries. Is the SQL keyword INTERSECT supported in Access?
Thanks
No. Just join the 2 tables. The result will be the intersect.
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.
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)
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.
(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
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.
you posted another thread about this problem didn't you?
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