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

[MattGreer]

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:

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"

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

Thanks!!


Matt

 

[MajP]

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

 

[MattGreer]

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:

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?

Thanks!!


Matt

 

[MajP]

Yes Access is limited compared to SQL Server. See here

http://www.databasejournal.com/feat...-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm

I think you need just a left join.

 

[combo]

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

combo

 

[MattGreer]

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.

Thanks!!


Matt

 

[Darrylles]

Matt,

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

ATB,

Darrylle

p.s.

http://a4academics.com/blog/365-easy-way-to-remember-sql-joins

 

[MattGreer]

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!



Thanks!!


Matt