Pulling null values from a many-to-many relationship

[kdirvin]

I am using Access 2007. I have two tables, tbl_PROJECTS and tbl_CHECKS. Tbl_PROJECTS has a ProjectID field and Project Name field. Tbl_CHECKS has a CheckID and Check Number field. These tables are connected in a many-to-many relationship through a juncture table called tbl_Projects Checks mm, and the juncture table contains only the ProjectID and CheckID foreign keys.
I need a query that will find projects without check numbers. I originally created a query that pulled both fields from juncture table tbl_Projects Checks mm, then set CheckID to “Is Null” in the Criteria row. I received a blank query datasheet in return. I do not know where to go from here. How can I build a query that lists the projects from tbl_PROJECTS that are not yet linked with a check through the juncture table?
Thank you for your help!

 

[emorris1000]

I think you do it by changing the joins to outer joins, I think.

In the query design window you have tbl_ProjectsChecks related to tbl_Projects and tbl_Checks. tbl_projects will be related to the junction table tbl_projectsChecks through project_ID with an outer join.

You can do this by clicking the relationship and selecting "show all records from tbl_projects and only the records from tbl_projectsChecks where the values are equal." Edit: hm, maybe you need the outer join in the other direction, not sure just yet.

Set the criteria to "is not null" for the tbl_projects (not sure that's necessary).

For tbl_checks you will relate it to the junction table and do outer join on the check_ID, and this time the criteria will be "is null". Same as before you can do this by double clicking the relationship in the query design window and selecting "show all records from tbl_projectsChecks and only those in tbl_checks where the two values match."

This should show you the project_id records in tbl_projects where there is no corresponding check_id record in tbl_checks.

You will probably also want to do an aggregate function depending on how "one-to-many" the relationship between tbl_projects and the junction table is.

I think that should work.

Edit: Actually I am not convinced this will work. I am pretty sure that what you need will be done through outer joins, but I am not sure that the way I mentioned above is the right way to do it.


edit2:
Ok, Think I have it now. tbl_products can do a normal join with the junction table, but the junction table needs to do an outer join with tbl_checks like how I listed above. You will need to keep the criteria "is null" and "is not null" in checks and projects respectively, and I would suggest you aggregate the Project_ID values since it is a 1-many relationship.

 

[kdirvin]

Thank you so much for your quick reply.

I followed your steps, and Access gave me the following message:

"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

If I do make separate queries for this task, how would I include it in the SQL statement of the second query?

Thank you!

 

[emorris1000]

Yeah, hrm...


Ok, try removing the outer join to the tbl_products, make it a normal join, and for the moment ignore any aggregates.

edit: ah, yeah I already mentioned that.

 

[boblarson]

first -

Do a query with the junction table to the checks table (all of the fields you want from the checks table) and make it an outer join which selects all from the junction table and those that match from the checks table.

second -

Use that query in another one where you then link the projects table to that query using a normal inner join. That should let you put in all of the fields you want from Projects and also have the values from the other query for the Checks table.

 

[boblarson]

An alternative method would be similar but would have the junction table (remember to include the checkID field) joined to the projects table with an Inner join and then using the second query have the first query joined to the checks table using an outer join.

 

[kdirvin]

I got it! I first made a query with an outer join that pulled all records from the junction table and only matching records from tbl_checks. Also included both fields from my juncture table. I then made a second query and linked the first query to tbl_Projects with a normal join (linked on the Project ID field from tbl_Projects and Project ID FK from juncture table). I set criteria in the check number field to "Is Null". And it worked!

Thank you. This has made my day.