INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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?



CODE -->

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

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

(OP)
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.

CODE -->

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

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

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


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

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

Thanks!!


Matt

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close