Access 2003 Query question

[mysterj1]

Hi all,

I have a Access 2003 app that I am creating to catalog my music collection and have come across a situation I was hoping someone might be able to shed some light on for me.

On occasion I will enter an album or group of albums into the database without also adding the associated songs included on those albums. What I want to do is to have Access remind me (through a report based on a query) which albums I need to update.

So, to my question: I have already created the relationships between the artists, albums, and songs tables, but searching for "is null" on the songs table isn't pulling any results up. How do I have Access realize there is an entry with a value in the ALBUMS table and nothing in the SONGS table?




Joe

 

[plog]

You want to use a LEFT JOIN. You bring Album and Songs into a query, link them by their ID, change the link to show all from Albums, bring down the Album_ID from the Songs table and set its criteria to null.

This is the SQL you need:

SELECT Album.*
FROM Album LEFT JOIN Songs ON Album.ID = Songs.Album_ID
WHERE (((Songs.Album_ID) Is Null));

 

[bob fitz]

Use the "Find Umatched Query Wizard" to create the required query which I've dione for you in the attached db.

 

[mysterj1]

Use the "Find Umatched Query Wizard" to create the required query which I've dione for you in the attached db.

Bob,

Many thanks for your insight. As a reward, (along with passing thanks on here), I will take 10 min to read up on the Unmatched Query Wizard and become proficient at it!

 

[mysterj1]

You want to use a LEFT JOIN. You bring Album and Songs into a query, link them by their ID, change the link to show all from Albums, bring down the Album_ID from the Songs table and set its criteria to null.

This is the SQL you need:

SELECT Album.*
FROM Album LEFT JOIN Songs ON Album.ID = Songs.Album_ID
WHERE (((Songs.Album_ID) Is Null));

Thank you for the assist! I was a sys admin for many years and knew enough SQL just to get by, I'll read up on the Left Join and become more adept at it!