Query for records with a field that is either blank or not blank 1

[MattGreer]

Googled and couldn't find an answer that helped. I have a table, tblSongs, that has a couple fields called "Group" and "Style". If I understand the term correctly, I would call them Lookup tables.

I haven't filled out all the "Style" entries for the Songs in tblSongs, but I want a query that returns all the records anyway. I only get records that have entries in the field "Style". How do I get all the records to show up?

SELECT tblSongs.SongID, tblSongs.Title, tblSongs.Use1, tblSongs.Tempo, tblGrouping.GroupName, tblStyles.Style, tblSongs.RevisionDate, tblSongs.Selected, tblSongs.Order
FROM tblStyles INNER JOIN (tblGrouping INNER JOIN tblSongs ON tblGrouping.GroupID = tblSongs.Group) ON tblStyles.StyleID = tblSongs.Style
ORDER BY tblSongs.Title;

Thanks!!


Matt

 

[MattGreer]

Fixed it by adjusting the join properties and I seem to have been previously incapable of appyling "Is Null" and/or "Is Not Null" correctly. Still can't figure out what I did wrong, but, it's working perfectly now.

SELECT tblSongs.SongID, tblSongs.Title, tblSongs.Use1, tblSongs.Tempo, tblGrouping.GroupName, tblStyles.Style, tblStyles.Style, tblSongs.RevisionDate, tblSongs.Selected, tblSongs.Order
FROM tblStyles RIGHT JOIN (tblGrouping INNER JOIN tblSongs ON tblGrouping.GroupID = tblSongs.Group) ON tblStyles.StyleID = tblSongs.Style
WHERE (((tblStyles.Style) Is Null Or (tblStyles.Style) Is Not Null))
ORDER BY tblSongs.Title;

Thanks!!


Matt

 

[Darrylles]

Matt,

The problem IS the relationship to tblStyles (but, it IS correct).
This means that your query JOINS must be adapted for this.
It states that every Song should have a tblStyle record (not MUST, but should).
As you have designed this schema 'logically' to say that EVERY tblSong record should have a tblStyle record - a query based on that relationship does as it's told - it won't 'naturally' show you a Song if it hasn't got a style; you have to work around it every time you want something (that you haven't designed it to do).

Rather than 'work around' your design - make the user adhere to your rules.

Add a 'dummy record' to tblStyle called maybe "Not determined" or "Unknown". (In most other cases - I call this 'N/A', but in this case - EVERY song ALWAYS has a 'style').
Get that new record Style_ID value.
In tblSongs, make the 'Style' field DEFAULT value equal to that Style_ID value.

Now, whenever a tblSongs record is added - it will ALWAYS have a default 'tblStyle' record (in case the user does't select one) or ...

Make the Style field mandatory (Required = Yes / Allow zero length = No), now, when a new Song is added, it cannot be saved unless a 'tblStyle' record is assigned.
If there isn't a valid 'tblStyle' record available - make a button available to the user - so that they can add a brand new style record; which can then be assigned to this Song.

ATB,

Darrylle

 

[MattGreer]

Good stuff Darrylle. Thanks for helping me learn better about the JOIN functionality.

Thanks!!


Matt

 

[Andrzejek]

And if/when you apply Darrylle's suggestion, revert your SELECT statement back to the original one.

Have fun.

---- Andy

There is a great need for a sarcasm font.