获取SQL查询以在Access中工作

I have the following Query written in MS SQL Server:

我在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.

哪个工作完美,但当我尝试在Access中使用它时根本不起作用。

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:

My Inventor表将General表中的PK作为FK。问题是,如果有什么东西列在其上的多个发明者。所以我尝试了以下内容:

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

Still with a syntax error

仍然有语法错误

3 个解决方案

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

在当前版本的问题中,Access会对此部分查询抱怨,因为Access SQL不支持COUNT(DISTINCT任何内容)。

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.

但是,调整完整查询以使用它将更具挑战性。考虑是否为您提供TotalInventors / TotalPatents的单个Access查询确实是最好的方法。我怀疑你可以通过将它分成2个查询(一个给你TotalInventors而另一个给你TotalPatents)让你自己更容易编写SQL编码任务。然后在调用查询的客户端代码中进行除法。

#2

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

Select in Access的语法不允许您在没有“from”的情况下编写select。

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:

SQL Server语法:

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

正如你在这里看到的,[]意味着某些东西是可选的。并且[]正在包装“From”SQL Server语法,但不包含Access Grammar。

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

所以,基本上,你的查询在Access中无效,因为它需要一个“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):

将得到一个有效的答案,这将在Access中给我一个语法错误(但它确实在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

以下是如何创建一个

#3

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.

逗号,将在其他DBMS中写为CROSS JOIN。

#1