none
Help with Converting an Access query

    Question

  • Hello:

    First time poster and would appreciate some help in converting this Access query into T-SQL so I can use in a Stored Procedure. I don't need help in resolving the syntax for the variables as parameter in the second query, but stuck in writing the first query in T-SQL . I assume I need some type of nested query with a left join to another query in T-SQL. 

    How do I convert this from Access into MS-SQL

    SELECT TBL_MONTH.L_Month, TBL_MONTH.L_MonthName
    FROM TBL_MONTH LEFT JOIN qry_MonthCheck ON TBL_MONTH.L_Month = qry_MonthCheck.L_Month
    WHERE (((qry_MonthCheck.L_Month) Is Null));

    qryMonthCheck in Access is defined as

    SELECT TBL_MONTH.L_Month, TBL_SIGNOFF.SO_YEAR, TBL_SIGNOFF.SO_USER, TBL_SIGNOFF.SO_LOCATION
    FROM TBL_MONTH RIGHT JOIN TBL_SIGNOFF ON TBL_MONTH.L_Month = TBL_SIGNOFF.SO_MONTH
    WHERE (((TBL_SIGNOFF.SO_YEAR)=[forms]![frm_signoff]![cboyear]) AND ((TBL_SIGNOFF.SO_LOCATION)=[Tempvars]![tmpVarUserLOC]));


    Saturday, March 01, 2014 10:15 PM

Answers

  • select L_Month, L_MonthName FROM tbl_Month Mth WHERE NOT EXISTS

    (SELECT 1 FROM tbl_SignOff Sgn

    WHERE Sgn.SO_Month = Mth.L_Month

    AND Sgn.SO_Year = @YearVar

    AND Sgn.SO_Location = @UserLoc)



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Sunday, March 02, 2014 2:38 AM
  • There is a typo in Erland's query. He named cte qryMonthCheck but then referred to it as qry_MonthCheck. Remove the _ for the query to work.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Bpl521 Sunday, March 02, 2014 8:00 AM
    Sunday, March 02, 2014 2:55 AM
  • What about this one with a WHERE clause:

    SELECT TBL_MONTH.L_Month, TBL_MONTH.L_MonthName
    FROM TBL_MONTH LEFT JOIN TBL_SIGNOFF ON TBL_MONTH.L_Month = TBL_SIGNOFF.SO_Month
    AND TBL_SIGNOFF.SO_YEAR=@cboyear AND TBL_SIGNOFF.SO_LOCATION=@tmpVarUserLOC
    WHERE TBL_SIGNOFF.SO_Month IS NULL;

    • Proposed as answer by Kalman TothModerator Sunday, March 02, 2014 7:28 AM
    • Marked as answer by Bpl521 Sunday, March 02, 2014 8:00 AM
    Sunday, March 02, 2014 4:20 AM

All replies

  • If I understand this correctly (I don't know Access), you can easily do this with a Common Table Expression:

    WITH qryMonthCheck AS (
       SELECT TBL_MONTH.L_Month, TBL_SIGNOFF.SO_YEAR, TBL_SIGNOFF.SO_USER, TBL_SIGNOFF.SO_LOCATION
      FROM TBL_MONTH RIGHT JOIN TBL_SIGNOFF ON TBL_MONTH.L_Month = TBL_SIGNOFF.SO_MONTH
    WHERE (((TBL_SIGNOFF.SO_YEAR)=[forms]![frm_signoff]![cboyear]) AND ((TBL_SIGNOFF.SO_LOCATION)=[Tempvars]![tmpVarUserLOC]))
    )
    SELECT TBL_MONTH.L_Month, TBL_MONTH.L_MonthName
    FROM TBL_MONTH LEFT JOIN qry_MonthCheck ON TBL_MONTH.L_Month = qry_MonthCheck.L_Month
    WHERE (((qry_MonthCheck.L_Month) Is Null));


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 01, 2014 11:01 PM
  • SELECT TBL_MONTH.L_Month, TBL_MONTH.L_MonthName
    FROM TBL_MONTH LEFT JOIN TBL_SIGNOFF ON TBL_MONTH.L_Month = TBL_SIGNOFF.SO_Month
    AND TBL_SIGNOFF.SO_YEAR=@cboyear AND TBL_SIGNOFF.SO_LOCATION=@tmpVarUserLOC;

    Saturday, March 01, 2014 11:39 PM
  • Thanks Jingyang, but unfortunately does not return the correct values. It returns all the values in TBL_MONTH. The query should return ALL months that are NOT returned if there is a result in qryMonthCheck

    If qrymonthCheck returned  1,2,3,4,5,6

    Then the query should return
    JULY
    AUGUST
    SEPTEMBER
    OCTOBER
    NOVEMBER
    DECEMBER

    TBL_Month is a simple lookup table with the name of the month as L_MONTHNAME and the number of the month as L_Month



    • Edited by Bpl521 Sunday, March 02, 2014 2:40 AM
    Sunday, March 02, 2014 2:33 AM
  • select L_Month, L_MonthName FROM tbl_Month Mth WHERE NOT EXISTS

    (SELECT 1 FROM tbl_SignOff Sgn

    WHERE Sgn.SO_Month = Mth.L_Month

    AND Sgn.SO_Year = @YearVar

    AND Sgn.SO_Location = @UserLoc)



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Sunday, March 02, 2014 2:38 AM
  • Thanks for the reply Erland, compiles fine but when executed returns an error

    Msg 208, Level 16, State 1, Procedure sp_SignOff_MonthCheck, Line 32
    Invalid object name qry_MonthCheck.

    Sunday, March 02, 2014 2:39 AM
  • There is a typo in Erland's query. He named cte qryMonthCheck but then referred to it as qry_MonthCheck. Remove the _ for the query to work.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Bpl521 Sunday, March 02, 2014 8:00 AM
    Sunday, March 02, 2014 2:55 AM
  • What about this one with a WHERE clause:

    SELECT TBL_MONTH.L_Month, TBL_MONTH.L_MonthName
    FROM TBL_MONTH LEFT JOIN TBL_SIGNOFF ON TBL_MONTH.L_Month = TBL_SIGNOFF.SO_Month
    AND TBL_SIGNOFF.SO_YEAR=@cboyear AND TBL_SIGNOFF.SO_LOCATION=@tmpVarUserLOC
    WHERE TBL_SIGNOFF.SO_Month IS NULL;

    • Proposed as answer by Kalman TothModerator Sunday, March 02, 2014 7:28 AM
    • Marked as answer by Bpl521 Sunday, March 02, 2014 8:00 AM
    Sunday, March 02, 2014 4:20 AM
  • There is a typo in Erland's query. He named cte qryMonthCheck but then referred to it as qry_MonthCheck. Remove the _ for the query to work.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Aah yes of course. Works now. Strange how it would compile without reporting any errors. 

    Sunday, March 02, 2014 7:17 AM
  • What about this one with a WHERE clause:

    SELECT TBL_MONTH.L_Month, TBL_MONTH.L_MonthName
    FROM TBL_MONTH LEFT JOIN TBL_SIGNOFF ON TBL_MONTH.L_Month = TBL_SIGNOFF.SO_Month
    AND TBL_SIGNOFF.SO_YEAR=@cboyear AND TBL_SIGNOFF.SO_LOCATION=@tmpVarUserLOC
    WHERE TBL_SIGNOFF.SO_Month IS NULL;

    Thank you very much. That also works as well.

    Appreciate all the help everyone.

    Sunday, March 02, 2014 7:20 AM
  • Aah yes of course. Works now. Strange how it would compile without reporting any errors. 

    Welcome to SQL Server, which is designed to confuse as much as possible!

    This particular phenomenon is due to a misfeature known as "Deferred name resolution". When you create a stored procedure, and SQL Server cannot find one of the table sources in a query, it gets the idea to be a nice guy and does not bicker about it, thinking that the table may be created later. The same thing happens when you invoke the procedure. That is, SQL Server starts executing thinking that the table may be created before the statement is reached. And, indeed, that is often the case with a temp table. But eventually the statement is reached, and at this point SQL Server has no choice to stop being the nice guy and you get slapped on the fingers.

    On a more important note: I see that your procedure is called sp_something. The sp_ prefix is reserved for system objects. Do not use it for your stored procedures.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 02, 2014 11:26 AM
  • Thanks very much Erland, very informative. SQL Server may be confusing, but nothing like fumbling around in Access <g>

    Any suggestions for naming conventions for objects in SQL. I guess it's really user specific and old habits. 

    I tend to prefix all tables with TBL_, curious as to what others do for naming Views, Stored Procedures etc.


    Sunday, March 02, 2014 11:40 AM
  • It is very much up to personal taste. For tables I use no prefix at all. Or suffix. I prefer plural: categories, products, etc. For junction tables, I drop the plural in the first entity, for instance productcategories. As for the case, many people prefer initial uppercase, while I go for lowercase only. But initial uppercase has its points, particularly in documentation. I am a staunch advocate of using case-sensitive collation for metadata. I don't see any point in mixing productcategories, Productcategories, ProductCategories etc. That can only cause confusion.

    As for either entities, I don't use views much, and I don't have any prefix for these either. In fact, several views I've been involved used to be tables once upon a time.

    If you want to add a marker to the object name, I recommend using a suffix. It is much easier to find objects in the version control system and other browser, when not everything is cluttered on the same letter. For instance, I typically add _sp at the end of stored procedure names.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 02, 2014 1:35 PM
  • Did you try the NOT EXISTS version I posted? It should work too.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, March 02, 2014 7:35 PM
  • Did you try the NOT EXISTS version I posted? It should work too.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thanks Naomi, missed it, Just tried and yes it works as well. the SELECT 1 confuses me, what exactly is the "1" representing or doing?
    Sunday, March 02, 2014 10:12 PM
  • Thanks Naomi, missed it, Just tried and yes it works as well. the SELECT 1 confuses me, what exactly is the "1" representing or doing?

    Fulfilling the syntax. That is, it carries no meaning. There could just as well be pi(), 'Spring is here!', 4711, NULL or whatever that fits the syntax. The most common is to use a star, but some people take this "don't use SELECT * in production code a little too seriously. Using SELECT * with EXISTS or NOT EXISTS is perfectly OK.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 02, 2014 10:58 PM

  • Fulfilling the syntax. That is, it carries no meaning. There could just as well be pi(), 'Spring is here!', 4711, NULL or whatever that fits the syntax. The most common is to use a star, but some people take this "don't use SELECT * in production code a little too seriously. Using SELECT * with EXISTS or NOT EXISTS is perfectly OK.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thanks Erland, good to know. Access when using a "*" will return all columns, MS- SQl appears to do the same. 

    • Edited by Bpl521 Sunday, March 02, 2014 11:25 PM
    Sunday, March 02, 2014 11:25 PM
  • Thanks Erland, good to know. Access when using a "*" will return all columns, MS- SQl appears to do the same. 

    Yes, that's a standard SQL feature which works the same everywhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 03, 2014 8:08 AM
  • EXISTS it's a special syntax that checks if there are rows that satisfy the condition. As Erland said, it doesn't matter that used after SELECT. However, a while ago I read on another site that EXISTS (SELECT 1 performs better than EXISTS (SELECT * The blog explaining that difference disappeared since then, but I got a habit of using 1 instead of more commonly used *. In any case, the performance of both should be about the same.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 03, 2014 3:54 PM