Get Only Non Matching Rows

[kbsudhir]

Hi All,

I have two queries in Access. Query "Get Options" provides the list options available. Query "Used Options" provides the list of options already used.

Now I want to create a query "Options_Left" to compare both the queries & get a list of options from "Get Options" query which are not matching with "Used Options" Query

Please guide on how to create this query.

: pray2: : pray2: :help :help :dunno :dunno :banghead: :banghead:

Thanks for your precious time
Sudhir

[kbsudhir]

Hi All,

I got it.

Below is the query which got the desired results

SELECT Get_Activities_Form.Activity
FROM Get_Activities_Form
WHERE (((Get_Activities_Form.Activity) Not In (SELECT Time_N_Motion_Study.Activities
FROM Time_N_Motion_Study
WHERE (((Time_N_Motion_Study.Req_num)=[Forms]![Time_N_Motion_Study]![Req_NbrTxt].[value])))));

If somebody knows a better/easier way then please let me know.

Thanks
Sudhir

[Kafrin]

Your solution is entirely valid. I do know a different way to do this, but I'm not going to claim that it's better. It may be easier if you need to make changes to the subquery in the future though.

Save your subquey as a separate query:
SELECT Time_N_Motion_Study.Activities
FROM Time_N_Motion_Study
WHERE Time_N_Motion_Study.Req_num=[Forms]![Time_N_Motion_Study]![Req_NbrTxt].[value];
This is UsedOptions.

You could just have
SELECT Get_Activities_Form.Activity
FROM Get_Activities_Form
WHERE (((Get_Activities_Form.Activity) Not In (SELECT UsedOptions.Activities
FROM UsedOptions)));

I personally however would join it in to a query in its own right and use this:
SELECT Get_Activities_Form.Activity
FROM Get_Activities_Form LEFT JOIN UsedOptions ON Get_Activities_Form.Activity = UsedOptions.Activity
WHERE Get_Activites_Form.PrimaryKey Is Null;

Note that this is my best guess from the data provided, you may need to use different fields from your tables/queries to make this work.

[kbsudhir]

Thanks Kafrin, I will check this out.