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
– huysentruitw, Commented Jul 22, 2012 at 3: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
– Johnny B, Commented Jul 22, 2012 at 4: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
– Johnny B, Commented Jul 22, 2012 at 4:35

Community · Accepted Answer · 2017-05-23 11:56:22Z

2

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jul 22, 2012 at 4:39

Luxspes

6,8003 gold badges30 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luxspes Over a year ago

@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 :-(

HansUp · Accepted Answer · 2012-07-22 13:21:43Z

1

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.

answered Jul 22, 2012 at 13:21

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

2 Comments

Johnny B Over a year ago

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.

Olivier Jacot-Descombes Over a year ago

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

ypercubeᵀᴹ · Accepted Answer · 2012-10-10 22:14:21Z

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, 2012 at 22:14

answered Oct 10, 2012 at 22:04

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Getting a SQL Query to work in Access

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related