Solved Good Resource For Learning Access SQL?

[dalski]

A brilliant SQL resource on Youtube tachTFQ (great teacher) - BUT exactly how different is native SQL compared to ACCESS SQL? Richard Prost does not indicate a great difference & I would have thought semantically it would be near identical. Seems to have different datatypes, ∴ data-sizes presumably. I know there are macros for converting these from Access to MSSQL for future development. But on such a basic I'm failing; questioning if it is worth studying these if they are greatly different & if I'm just wasting my time. The lack of MS Access tutorials indicates they are near identical.

E.g.

• I received 2 different error warnings (not concurrently):
  • Warning on SELECT clause mispelled/ reserved keyword
  • Syntax error in FROM clause (pictured)
• I thought the below may have been because Alias' need to have the AS operator, but NO
• Or complete tbl names instead of Alias' - NO
• Am I correct in saying an alias can be created by merely following the declared tbl/ qry/ field with the desired name of the Alias in native SQL but not in Access SQL?
• If it is worth me studying native SQL?

Would have thought below would work:

SELECT employee.emp_name, department.dept_name
FROM employee
JOIN department on employee.dept_id = department.dept_id;

What's up here?

Or:

SELECT e.emp_name, d.dept_name
FROM employee e
JOIN department d on e.dept_id = d.dept_id;

 

[The_Doc_Man]

Offhand I don't see anything wrong. I don't download other people's databases most of the time (O.K., I'm paranoid) so my question is, if you do a copy/paste of that entire SQL SELECT query - copy from SQL view, paste into something like NOTEPAD, see if there is a "phantom" i.e. non-printing character in there somewhere.

 

[dalski]

Thanks @The_Doc_Man, I do not blame you for not downloading. Pasting in Notepad & Word I can't see any unexpected phantoms. I didn't have a primary key in one of the four tbl's as one of the tbl's didn't have an obvious PK (not related to this query). Putting a PK in said table (unrelated to this qry) did not cause anything either.

 

[plog]

Aliases have nothing to do with your error. JOIN isn't valid by itself. Add INNER before it (or LEFT if you prefer).

As for aliases in the FROM, yes you just space from the name and then use the alias you want:

FROM employee e

 

[dalski]

Thanks @plog, interesting that the prompt from Access is incorrect; should have warned me about the join statement not 'FROM clause'.

Grateful for the input on Alias' shorthand only in FROM clause.

 

[cheekybuddha]

The JOIN statement is part of the FROM clause.

 

[GPGeorge]

Aliases have nothing to do with your error. JOIN isn't valid by itself. Add INNER before it (or LEFT if you prefer).

As for aliases in the FROM, yes you just space from the name and then use the alias you want:

FROM employee e

I've seen comments indicating that is a good idea to fully qualify the alias, in order to be explicit and unambiguous.

FROM employee AS e

I'm not sure I'm convinced, but it can't be bad to be as explicit as possible, IMO.

 

[CJ_London]

If it is worth me studying native SQL?

A bit meaningless - the variety of sql is native to the db - access, sql server, mySql etc

They all have much the same syntax and structure but each has its variations and each has features other do not.

Some examples -
Like uses a * in access, a % in sql server
Access uses the vba iif and switch functions, sql server the Case When syntax
as above Access uses inner join, sql server join
sql server has Outer Join, Access does not

table names do not need to be specified unless the fieldname is in more than one table within the query - a good reason for using specific field names rather than a generic such as 'id' or 'name'

 

[dalski]

Thanks people., much appreciated.

 

[GPGeorge]

A bit meaningless - the variety of sql is native to the db - access, sql server, mySql etc

They all have much the same syntax and structure but each has its variations and each has features other do not.

Some examples -
Like uses a * in access, a % in sql server
Access uses the vba iif and switch functions, sql server the Case When syntax
as above Access uses inner join, sql server join
sql server has Outer Join, Access does not

table names do not need to be specified unless the fieldname is in more than one table within the query - a good reason for using specific field names rather than a generic such as 'id' or 'name'

"sql server has Outer Join, Access does not"

Access has Left Outer Join and Right Outer Join.

 

[plog]

So you quoted the whole post, then put quote marks around just part of the quoted part you just quoted wholesale, then you made a tangential, near non-sequitur comment?

 

[The_Doc_Man]

Ah, I missed the "INNER" - darn! As to the other question ("flavor" of SQL syntax), it is possible for you to set Access to use ANSI 92 standard SQL. From the ribbon, you can use File >> Options >> Object Designers and then scroll down to Query Design, where you will find that option at the bottom of that section.

 

[CJ_London]

Access has Left Outer Join and Right Outer Join.

Access does not use the word outer, just left join and right join

 

[Isaac]

A brilliant SQL resource on Youtube tachTFQ (great teacher) - BUT exactly how different is native SQL compared to ACCESS SQL? Richard Prost does not indicate a great difference & I would have thought semantically it would be near identical. Seems to have different datatypes, ∴ data-sizes presumably. I know there are macros for converting these from Access to MSSQL for future development. But on such a basic I'm failing; questioning if it is worth studying these if they are greatly different & if I'm just wasting my time. The lack of MS Access tutorials indicates they are near identical.

E.g.
View attachment 119405

• I received 2 different error warnings (not concurrently):
  • Warning on SELECT clause mispelled/ reserved keyword
  • Syntax error in FROM clause (pictured)
• I thought the below may have been because Alias' need to have the AS operator, but NO
• Or complete tbl names instead of Alias' - NO
• Am I correct in saying an alias can be created by merely following the declared tbl/ qry/ field with the desired name of the Alias in native SQL but not in Access SQL?
• If it is worth me studying native SQL?

Would have thought below would work:

SELECT employee.emp_name, department.dept_name
FROM employee
JOIN department on employee.dept_id = department.dept_id;

What's up here?

Or:

SELECT e.emp_name, d.dept_name
FROM employee e
JOIN department d on e.dept_id = d.dept_id;

The best teacher is to have a project to work on and go from there

 

[dalski]

Think I've ran into another flaw between native SQL & Access SQL. Access will not allow Multiple Row & Multiple field subqueries.

much the same syntax and structure but each has its variations...

Reluctantly I may have to quit the native SQL tutorials on Youtube techTFQ as it seems near unrecognizable in Access. A multiple row, multiple column subquery seems unrecognizable:

Native SQL:

SELECT *
FROM employee
WHERE (dept_name, salary) in (select dept_name, max(salary)
                                                 from employee
                                                 group by dept_name);

Access JET SQL:

SELECT employee.dept_name, Avg(employee.salary) AS AvgOfsalary
FROM employee
GROUP BY employee.dept_name
HAVING (((Avg(employee.[salary]))>=All (SELECT avg(salary)
FROM employee
GROUP BY dept_name)));

Seems that Access' Jet SQL has considerable limitations. Desperately seeking a decent SQL resource to learn from. It's tempting to transfer to MS SQL but I'm concerned in having to set-up my relationships again (small thing really), loosing intellisence on VBA & in the Expression Editor, data-ype size & conversion errors & creating more work. Let alone lord knows what else I run into. Also hope I don't loose the Query Designer as it is awful handy (I'm a complete novice).

 

[cheekybuddha]

Seems that Access' Jet SQL has considerable limitations.

This is certainly true, but in this instance I don't think what you are attempting is one of them.

In what system does your 'Native SQL' actually work?

Multiple fields in WHERE ... IN is not a syntax I have ever seen before.

Read this StackOverflow for more info.

Your query looks like it could be more effectively written as:

SELECT
  e.*
FROM employee e
INNER JOIN (
  SELECT
    e1.dept_name,
    MAX(e1.salary) AS max_salary
  FROM employee e1
  GROUP BY
    e1.dept_name
) m 
       ON e.dept_name = m.dept_name
      AND e.salary = m.max_salary
;

 

[dalski]

Thanks @cheekybuddha, it works in PostgreSQL - direct from tutorial mentioned in specific area of Multiple Rows & Multiple Fields area of subqueries Youtube techTFQ titled "Subquery in SQL | Complete SQL Subqueries Tutorial". Thanks for your input on the qry. I just cannot see how I can learn this with so many variations of SQL & few proper decent tutorials on Access' JET SQL.
Seems the same knowledgable tutor on Youtube has 3 small videos on JET SQL on another channel "Find Easy Solution" which are helpful. Shocked at the limitation of decent material out there. My books are also massively lacking on JET SQL.

 

[GPGeorge]

So you quoted the whole post, then put quote marks around just part of the quoted part you just quoted wholesale, then you made a tangential, near non-sequitur comment?

Yup.

 

[cheekybuddha]

I just cannot see how I can learn this with so many variations of SQL & few proper decent tutorials on Access' JET SQL.

Try not to run before you can walk!

Learn the SQL basics first.

SELECT clause, plus expressions
FROM clause, different kinds of joins (INNER, LEFT, RIGHT), then subqueries
WHERE clause
GROUP BY clause. Learn when and when not to use HAVING
ORDER BY

Understanding those basics will get you 95% of the way there.

You don't need to know about CTE's, window functions etc until you have a grasp of the above (and they're not available in Access anyway)

One big annoyance with Jet SQL is that if you wish to select from more than two tables then the joins must be nested.

So, instead of something like:

SELECT
  a.Fld1,
  b.Fld2,
  c.Fld3
FROM a
INNER JOIN b
        ON a.bFK = b.ID
INNER JOIN c
        ON a.cFK = c.ID
;

you have to do something like:

SELECT
  a.Fld1,
  b.Fld2,
  c.Fld3
FROM (
  a
  INNER JOIN b
          ON a.bFK = b.ID
)
INNER JOIN c
        ON a.cFK = c.ID
;

 

[dalski]

Thanks @cheekybuddha , very helpful. Believe me not wanting to run, just trying to grasp the basics but spinning out of control lol.