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.


Having trouble with syntax using EXCEPT in query - MS Access 2016

Having trouble with syntax using EXCEPT in query - MS Access 2016

I have a database for tracking song usage. I have a list of songs in tblSongs, and then I record the history of those songs being played in tblHistory, joined by tblLinkHistory.

I'm trying to write a query that will give me all songs that were NOT played in the last quarter. I could just do that on the one table, but the main table contains songs that don't appear in the History table (because there are a lot of songs we haven't performed yet, or have no history on), so it seems I need to use a query utilizing the EXCEPT statement. I can't seem to get the syntax right.

I have a query that pulls all the songs from the last quarter from tblHistory, called qryHistory3Month:

CODE -->

SELECT tblSongs.SongID, tblLinkHistory.SongID, tblHistory.Date
FROM tblSongs INNER JOIN (tblHistory INNER JOIN tblLinkHistory ON tblHistory.EventID = tblLinkHistory.EventID) ON tblSongs.SongID = tblLinkHistory.SongID
WHERE (((tblHistory.Date)>=DateAdd("q",-1,Now())))
ORDER BY tblHistory.Date DESC; 

So here's my starting point just to try to get the query to run, but I keep getting the error "Syntax Error in FROM clause"

CODE -->

SELECT tblSongs.SongID, tblSongs.Title
FROM tblSongs
SELECT qryHistory3Month.SongID
FROM qryHistory3Month; 



RE: Having trouble with syntax using EXCEPT in query - MS Access 2016

The Except operator does not work in Access SQL AFAIK. You can use a NOT IN syntax instead.
WHERE TblSongs.SongId NOT IN (Select qryHistory3Month.SongID from qryHistory3Month)

Or you can do a Outer join from tblSongs to qry3Month and not return those where the qry3Month.SongID is not null

RE: Having trouble with syntax using EXCEPT in query - MS Access 2016

Thanks for your help!

I'm still having problems. Trying to do this simply and can't get a basic statement to work. I'm sure I'm doing it wrong but as a non-expert I'm very dependent on Access's graphical way of setting up a query:

CODE -->

SELECT tblSongs.SongID
FROM tblSongs
FULL OUTER JOIN qryHistory3Month ON tblSongs.SongID = qryHistory3Month.tblSongs.SongID; 

I get the error "Syntax Error in FROM clause" I get that regardless of whether I use "FULL OUTER JOIN" or "OUTER JOIN"

Any advice?



RE: Having trouble with syntax using EXCEPT in query - MS Access 2016

You can create complete query using visual designer.
Add tblSongs table and qryHistory3Month query to the designer. Link SongID fields in both objects (drag SongID from table onto SongID in query). Configure join type (right-click created line and set options). Drag required fields into the grid. Add SongID from the query and deselect display option. Write condition for this field: Not Is Null (or, in case of error: Is Not Null, I have no access to access now, don't remember proper syntax).
You can see generated sql when you switch view type to "sql".


RE: Having trouble with syntax using EXCEPT in query - MS Access 2016

That did it, combo, MajP, or almost, but it led me to the right answer. MajP was right, needs to be a Left Join. combo was close, I actually needed a Is Null.

Here's the right answer, in SQL, for what I have:


SELECT tblSongs.SongID, tblSongs.Title, qryHistory3Month.tblLinkHistory.SongID
FROM tblSongs LEFT JOIN qryHistory3Month ON tblSongs.SongID = qryHistory3Month.tblSongs.SongID
WHERE (((qryHistory3Month.tblLinkHistory.SongID) Is Null)); 

WOO! Time for a nap. I'm tired.



RE: Having trouble with syntax using EXCEPT in query - MS Access 2016


Most of your posts are concerning SQL joins, or more accurately as your unclear understanding of them.

It's refreshing to see someone learning database design - but actually understanding the relational design concept correctly first (rather than years of agony after realising that it's critical).

Study and thoroughly understand the SQL 'join' terminology, and what each type of join ACTUALLY does, and you'll answer most of your posts yourself. (This is the simple part - you've already done the hard work).
And, remember that different vendors DO NOT adhere to any SQL standard (although we all wish they would); all are 95%-ish compliant, but most add their own little twists.

Congratulations on making a cynic happy - good luck in whatever area you work in - I can foresee a good, professional future for you (if that is your aim).




RE: Having trouble with syntax using EXCEPT in query - MS Access 2016

Man, you are so kind Darrylle! Thanks! Not going to be a professional, unless something really weird and/or amazing happens. This is just a means to an end to help my workflow be more efficient when it comes to selecting songs for a weekly gig (funny to call "church" a gig, but it technically fits!)

Years ago I read the book "Database Design for Mere Mortals" and that got me to understand the reason and purpose of the normal forms and how to set things up so you (essentially) have one piece of information only in one, very logical place.

I agree that it would help me immensely to fully understand how JOIN works; thank you for that suggestion!



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!


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