Getting a SQL Query to work in Access

Question

I have the following Query written in MS SQL Server:

select
    (select (CAST( count(*) as decimal (38,4))) from Inventor) /
    (select (CAST( count(*) as decimal(38,4))) from General);

which works perfectly, but when I try to use it in Access it doesn't work at all.

Help please!

Ok so it doesnt have to be cast as decimal, a float would work as well.

My Inventor table has the PK from the General table as a FK in it. The issue is that if something has multiple inventors listed on it. So i Tried the following:

SELECT TotalInventors/TotalPatents
(SELECT COUNT (DISTINCT PatentNo) FROM Inventor AS TotalPatents
(SELECT COUNT (*) FROM Inventor AS TotalInventors))
FROM Inventor;

Still with a syntax error

Why are you casting an integer count to a decimal at the db server side? — huysentruitw, Jul 22 '12 at 03:54
@WouterH I am casting an integer count to decimal so that the result is decimal and not whole number, this is the way I know how to do it. — Johnny B, Jul 22 '12 at 04:34
@Luxspes it says "syntax error in query expression 'select (select (CAST( count(*) as decimal (38,4))) from Inventor) / (select (CAST( count(*) as decimal(38,4))) from General);' — Johnny B, Jul 22 '12 at 04:35
AFAICT, `CAST` doesn't work in Access. Apparently `CDec` doesn't work either. Try the `NZ` function. — Tom, Jul 22 '12 at 04:46
It is not the casting, see my answer down there, is the the selects without the "from" that you are using, if you simplify it to `select ( select 1 ) / ( select 1 )` you still get a syntax error — Luxspes, Jul 22 '12 at 05:03
@HansUp then how do you count values and ignore duplicate records in Access. Apologies if this should be a separate question altogether I am new to the boards. — Johnny B, Jul 22 '12 at 06:01
@rcdmk: Wrong, Access (Jet-Engine SQL to be precise) accepts subqueries. Them problem is (as HansUp says) that there is no `COUNT(DISTINCT xy)` in Access. — Olivier Jacot-Descombes, Oct 10 '12 at 22:19
@JonnyB: Why do you have problems with multiple inventors in Access but not with SQL Server? — Olivier Jacot-Descombes, Oct 10 '12 at 22:21

score 2 · Answer 1 · edited May 23 '17 at 11:56

The syntax for Select in Access does NOT allow you to write a select without a "from".

Access Grammar:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase] --FROM is NOT optional
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

SQL Server Grammar:

[ WITH <common_table_expression>]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ] --FROM is optional
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

As you can see here, the [ ] mean that something is optional. And the [ ] are wrapping the "From" the SQL Server Grammar, but not in the Access Grammar.

So, basically, your query is invalid in Access because it needs a "FROM"

Now this is a little bit inconsistent, while this :

select    ( 1  ) /  ( 1 )

Or this

select    ( 1 )

will get me a valid answer, this will give me a syntax error in Access (but it does work in SQL Server):

select    ( select 1  ) /  ( select 1 )

You need a "dual" table, so that you can write:

select    (  select 1  from Dual) /  ( select 1 from Dual) from Dual.

Here is how you can create one

@HansUp to write the query the way the OP wants, in access, you do need Dual... (unless, as you pointed out, you change the query and start using DCount)... although your answer is also incomplete, because CDec is [buggy](http://support.microsoft.com/kb/225931) :-( — Luxspes, Jul 22 '12 at 05:20

score 1 · Accepted Answer · answered Jul 22 '12 at 13:21

In the current version of your question, Access complains with this part of your query because Access SQL doesn't support COUNT (DISTINCT anything).

SELECT COUNT (DISTINCT PatentNo) FROM Inventor

You could rewrite that piece as:

SELECT Count(*) FROM
(SELECT DISTINCT PatentNo
FROM Inventor);

However adapting the full query to use that will be more challenging. Consider whether a single Access query to give you TotalInventors/TotalPatents is really the best way to go. I suspect you could make the SQL coding task easier for yourself by splitting that into 2 queries (one to give you TotalInventors and another to give you TotalPatents). Then do the division in your client code which calls the queries.

it works as 2 separate queries and then doing my division. Thanks for all of the help, Access annoys me more and more every second I use it. — Johnny B, Jul 22 '12 at 16:49
@JonnyB: If the backend is still a SQL-Server, consider using a pass-through query, which allows you to write the query in SQL-Server SQL inside your Access application. — Olivier Jacot-Descombes, Oct 10 '12 at 22:25

ypercubeᵀᴹ · Answer 3 · 2012-10-10T22:14:21.477

0

I think this will work:

SELECT
    CDbl( ci ) / CDbl( cg )   AS result
FROM 
    (SELECT COUNT(*) AS ci FROM Inventor) AS i
  ,
    (SELECT COUNT(*) AS cg FROM General) AS g ;

The comma , would be written as CROSS JOIN in other DBMS.

edited Oct 10 '12 at 22:14

answered Oct 10 '12 at 22:04

ypercubeᵀᴹ

113,259
19
174
235

Getting a SQL Query to work in Access

3 Answers3