Query中的FROM子句问题 [英] Problem with FROM Clause in Query
问题描述
我正在创建一个查询，我想在我的VB.NET应用程序的代码中使用它。
此查询在Access中执行时产生正确的结果：
SELECT tblEncounters.EncounterBeginDT，Query11.RID，Query11.LName，
Query11.FName，Query11.MI FROM tblEncounters INNER JOIN Query11 ON
tblEncounters。 RID = Query11.RID;
Query11如下所示：
tblCases.TotalNum
FROM tblCases INNER JOIN [SELECT [tblRecipients]。[RID]，
[tblRecipients]。[LName] ，[tblRecipients]。[FName]，[tblRecipients]。[MI]，
[tblEncounters]。[Status]，Count（[Status]）AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients]。[RID] = [tblEncounters]。[RID] WHERE
（（（[tblEncounters]。[Status]）= 1 ））GROUP BY [tblRecipients]。[RID]，
[tblRecipients]。[LName]，[tblRecipients]。[FName]，[tblRecipie nts]。[MI]，
[tblEncounters]。[状态]]。 AS Query8 ON（tblCases.TotalNum =
Query8.TotComplete）AND（tblCases.RID = Query8.RID）;
现在获取第一个查询我我已经列出了在代码中工作（即没有
指的是Access中的命名查询），我试过这样做：
SELECT tblEncounters.EncounterBeginDT， Query11.RID，Query11.LName，
Query11.FName，Query11.MI FROM tblEncounters INNER JOIN（SELECT
tblCases.RID，tblCases.LName，tblCases.FName，tblCases .MI，
tblCases.TotalNum FROM tblCases INNER JOIN [SELECT
[tblRecipients]。[RID]，[tblRecipients]。[LName]，
[tblRecipients]。[FName]，[tblRecipients]。[MI]，
[tblEncounters]。[Status]，Count（[Status]）AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients]。[RID] = [tblEncounters]。[RID] WHERE
（（（[tblEncounters]。[Status]）= 1））GROUP BY [tblRecipients]。[RID]，
[tblRecipients]。[LName]，[tblRecipients]。[FName]，[tblReci [MI]，
[tblEncounters]。[状态]]。 AS Query8 ON（tblCases.TotalNum =
Query8.TotComplete）AND（tblCases.RID = Query8.RID））作为Query11 ON
tblEncounters.RID = Query11.RID;
我收到消息FROM FROM子句中的SYNTAX错误。对于我的生活，我
无法弄清楚错误的位置。我之前做过这样的事情
- 创建一个查询并在另一个查询中使用它，然后使用
SQL并执行As Query。 。 " （就像我在查询8中所做的那样）。
我要做的是创建一个报告，显示
完成案例的数量（不是访问报告）。一个人可以遇到多个
遭遇，并且当状态字段中包含1
时，会遇到相遇。当一个人遇到他们的所有
遇到状态字段中的1时，一个案例被认为是完整的，所以我比较一个人遇到的
总数与总数相比状态字段中为1的
人的遭遇次数。
任何帮助都将受到赞赏。
谢谢。
Molly J. Fagan
俄克拉荷马州医疗质量基金会
I''m creating a query, which I want to use in code in my VB.NET app.
This query produces the correct results when executed in Access:
SELECT tblEncounters.EncounterBeginDT, Query11.RID, Query11.LName,
Query11.FName, Query11.MI FROM tblEncounters INNER JOIN Query11 ON
tblEncounters.RID = Query11.RID;
Query11 looks like this:
SELECT tblCases.RID, tblCases.LName, tblCases.FName, tblCases.MI,
tblCases.TotalNum
FROM tblCases INNER JOIN [SELECT [tblRecipients].[RID],
[tblRecipients].[LName], [tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status], Count([Status]) AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients].[RID]=[tblEncounters].[RID] WHERE
((([tblEncounters].[Status])=1)) GROUP BY [tblRecipients].[RID],
[tblRecipients].[LName], [tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status]]. AS Query8 ON (tblCases.TotalNum =
Query8.TotComplete) AND (tblCases.RID = Query8.RID);
Now to get the first query I have listed to work in code (i.e. to not
be referring to a named query in Access), I tried doing this:
SELECT tblEncounters.EncounterBeginDT, Query11.RID, Query11.LName,
Query11.FName, Query11.MI FROM tblEncounters INNER JOIN (SELECT
tblCases.RID, tblCases.LName, tblCases.FName, tblCases.MI,
tblCases.TotalNum FROM tblCases INNER JOIN [SELECT
[tblRecipients].[RID], [tblRecipients].[LName],
[tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status], Count([Status]) AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients].[RID]=[tblEncounters].[RID] WHERE
((([tblEncounters].[Status])=1)) GROUP BY [tblRecipients].[RID],
[tblRecipients].[LName], [tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status]]. AS Query8 ON (tblCases.TotalNum =
Query8.TotComplete) AND (tblCases.RID = Query8.RID)) As Query11 ON
tblEncounters.RID = Query11.RID;
I get the message "SYNTAX error in FROM clause." For the life of me, I
cannot figure out where the error is at. I''ve done this sort of thing
before--create a query and use it in another query and then take the
SQL and do the "As Query. . ." (like I did for Query8).
What I''m trying to do is create a report that shows the number of
completed cases (not an Access report). A person can have multiple
encounters and an encounter is completed when the status field has a 1
in it. A case is considered complete for a person when ALL of their
encounters has a 1 in the status field so I compare the total number of
encounters a person has against the total number of encounters for a
person that has a 1 in the status field.
Any help would be appreciated.
Thanks.
Molly J. Fagan
Oklahoma Foundation for Medical Quality
推荐答案
我可以在那里看到几个错误
如果你布置SQL语句并使用别名，它会变得更容易阅读
他们调试它们只需布置你的SQL语句我们得到
SELECT
tblEncounters.EncounterBeginDT，
Query11.RID，
Query11.LName，
Query11.FName，
Query11.MI
FROM
tblEncounters
INNER JOIN
（
SELECT
tblCases.RID，
tblCases.LName，
tblCases.FName，
tblCases.MI，
tblCases.TotalNum
FROM
tblCases
INNER JOIN
[
SELECT
[ tblRecipients]。[RID]，
[tblRecipients]。[LName]，
[tblRecipients]。[FName]，
[tblRecipients] 。[MI]，
[tblEncounters]。[状态]，
计数（[状态]）AS TotComplete
FROM
tblRecipients
INNER JOIN
tblEncounters
ON
[tblRecipients]。[RID] = [ tblEncounters]。[RID]
WHERE
（（[[tblEncounters]。[Status]）= 1））
GROUP BY
[tblRecipients]。[RID]，
[tblRecipients]。[LName]，
[tblRecipients]。[FName]，
[tblRecipients]。[MI]，
[tblEncounters]。[状态]
]。 AS Query8
ON
（tblCases.TotalNum = Query8.TotComplete）
AND
（tblCases.RID = Query8.RID）
）作为Query11
ON
tblEncounters.RID = Query11.RID;
我们可以在第二个INNER JOIN行之后看到
你已经在[]中包含SELECT
语句而不是（）
就在结束之后（就在AS Query8之前）你有一个虚假的''。''
（句号或句号）
- -
Terry Kreft
< mo **** @ hotmail.com>在消息中写道
news：11 ********************* @ z14g2000cwz.googlegro ups.com ...
Well I can see a couple of errors in there
If you lay out SQL statements and use Aliases it becomes much easier to read
them and debug them so just laying out your SQL statement we get
SELECT
tblEncounters.EncounterBeginDT,
Query11.RID,
Query11.LName,
Query11.FName,
Query11.MI
FROM
tblEncounters
INNER JOIN
(
SELECT
tblCases.RID,
tblCases.LName,
tblCases.FName,
tblCases.MI,
tblCases.TotalNum
FROM
tblCases
INNER JOIN
[
SELECT
[tblRecipients].[RID],
[tblRecipients].[LName],
[tblRecipients].[FName],
[tblRecipients].[MI],
[tblEncounters].[Status],
Count([Status]) AS TotComplete
FROM
tblRecipients
INNER JOIN
tblEncounters
ON
[tblRecipients].[RID]=[tblEncounters].[RID]
WHERE
((([tblEncounters].[Status])=1))
GROUP BY
[tblRecipients].[RID],
[tblRecipients].[LName],
[tblRecipients].[FName],
[tblRecipients].[MI],
[tblEncounters].[Status]
]. AS Query8
ON
(tblCases.TotalNum = Query8.TotComplete)
AND
(tblCases.RID = Query8.RID)
) As Query11
ON
tblEncounters.RID = Query11.RID;
We can then see that
just after the second INNER JOIN line you''ve enclosed the SELECT
statement in [ ] instead of ( )
just after the closing ] (just before AS Query8) you have a spurious ''.''
(full stop or period)
--
Terry Kreft
<mo****@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
我正在创建一个查询，我想在我的VB.NET应用程序中的代码中使用。
此查询在Access中执行时产生正确的结果：
SELECT tblEncounters .EncounterBeginDT，Query11.RID，Query11.LName，
Query11.FName，Query11.MI FROM tblEncounters INNER JOIN Query11 ON
tblEncounters.RID = Query11.RID;
Query11的搭配像这样：
SELECT tblCases.RID，tblCases.LName，tblCases.FName，tblCases.MI，
tblCases.TotalNum
FROM tblCases INNER JOIN [SELECT [tblRecipients]。[ RID]，
[tblRecipients]。[LName]，[tblRecipients]。[FName]，[tblRecipients]。[MI]，
[tblEncounters]。[Status]，Count（[Status]）AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients]。[RID] = [tblEncounters]。[RID] WHERE
（（（[tblEncounters]。[Status]）= 1）） GROUP BY [tblRecipients]。[RID]，
[tblRecipients ]。[LName]，[tblRecipients]。[FName]，[tblRecipients]。[MI]，
[tblEncounters]。[Status]]。 AS Query8 ON（tblCases.TotalNum =
Query8.TotComplete）AND（tblCases.RID = Query8.RID）;
现在获取我列出的第一个查询代码（即为了不引用Access中的命名查询，我尝试这样做：
SELECT tblEncounters.EncounterBeginDT，Query11.RID，Query11.LName，
Query11.FName， Query11.MI FROM tblEncounters INNER JOIN（SELECT
tblCases.RID，tblCases.LName，tblCases.FName，tblCases.MI，
tblCases.TotalNum FROM tblCases INNER JOIN [SELECT
[tblRecipients]。 [RID]，[tblRecipients]。[LName]，
[tblRecipients]。[FName]，[tblRecipients]。[MI]，
[tblEncounters]。[Status]，Count（[Status]） AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients]。[RID] = [tblEncounters]。[RID] WHERE
（（[[[[[[[[[[[[[状态]）= 1） ）GROUP BY [tblRecipients]。[RID]，[tblRecipients]。[LName]，[tblRecipients]。[FName]，[tblRecipients]。[MI]，
[tblEncounters]。[状态] AS。Query8 ON（tblCases.To talNum =
Query8.TotComplete）AND（tblCases.RID = Query8.RID））作为Query11 ON
tblEncounters.RID = Query11.RID;
我收到消息 FROM子句中的SYNTAX错误。对于我的生活，我无法弄清楚错误的位置。我之前做过这样的事情 - 创建一个查询并在另一个查询中使用它，然后使用
SQL并执行As Query。 。 " （就像我为Query8所做的那样）。
我要做的是创建一个报告，显示已完成案例的数量（不是Access报告）。一个人可以有多次遭遇，并且当状态字段中包含1
时，会遇到相遇。当一个人在状态字段中遇到一个1时，一个案例被认为是完整的，所以我将一个人遇到的总数与一个人的遭遇总数进行比较。 >在状态字段中具有1的人。
任何帮助将不胜感激。
谢谢。
Molly J. Fagan 俄克拉荷马州医疗质量基金会
I''m creating a query, which I want to use in code in my VB.NET app.
This query produces the correct results when executed in Access:
SELECT tblEncounters.EncounterBeginDT, Query11.RID, Query11.LName,
Query11.FName, Query11.MI FROM tblEncounters INNER JOIN Query11 ON
tblEncounters.RID = Query11.RID;
Query11 looks like this:
SELECT tblCases.RID, tblCases.LName, tblCases.FName, tblCases.MI,
tblCases.TotalNum
FROM tblCases INNER JOIN [SELECT [tblRecipients].[RID],
[tblRecipients].[LName], [tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status], Count([Status]) AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients].[RID]=[tblEncounters].[RID] WHERE
((([tblEncounters].[Status])=1)) GROUP BY [tblRecipients].[RID],
[tblRecipients].[LName], [tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status]]. AS Query8 ON (tblCases.TotalNum =
Query8.TotComplete) AND (tblCases.RID = Query8.RID);
Now to get the first query I have listed to work in code (i.e. to not
be referring to a named query in Access), I tried doing this:
SELECT tblEncounters.EncounterBeginDT, Query11.RID, Query11.LName,
Query11.FName, Query11.MI FROM tblEncounters INNER JOIN (SELECT
tblCases.RID, tblCases.LName, tblCases.FName, tblCases.MI,
tblCases.TotalNum FROM tblCases INNER JOIN [SELECT
[tblRecipients].[RID], [tblRecipients].[LName],
[tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status], Count([Status]) AS TotComplete FROM
tblRecipients INNER JOIN tblEncounters ON
[tblRecipients].[RID]=[tblEncounters].[RID] WHERE
((([tblEncounters].[Status])=1)) GROUP BY [tblRecipients].[RID],
[tblRecipients].[LName], [tblRecipients].[FName], [tblRecipients].[MI],
[tblEncounters].[Status]]. AS Query8 ON (tblCases.TotalNum =
Query8.TotComplete) AND (tblCases.RID = Query8.RID)) As Query11 ON
tblEncounters.RID = Query11.RID;
I get the message "SYNTAX error in FROM clause." For the life of me, I
cannot figure out where the error is at. I''ve done this sort of thing
before--create a query and use it in another query and then take the
SQL and do the "As Query. . ." (like I did for Query8).
What I''m trying to do is create a report that shows the number of
completed cases (not an Access report). A person can have multiple
encounters and an encounter is completed when the status field has a 1
in it. A case is considered complete for a person when ALL of their
encounters has a 1 in the status field so I compare the total number of
encounters a person has against the total number of encounters for a
person that has a 1 in the status field.
Any help would be appreciated.
Thanks.
Molly J. Fagan
Oklahoma Foundation for Medical Quality
特里
我很困惑。
在JET中，我使用了
[一些选择字符串]。 AS名称
在SQL字符串中实现子查询
很长一段时间。 （我没有以任何方式发明这一点，但我可能已经支持它了。）
我相信在T-SQL中我们可以使用
（一些选择字符串）AS名称
OP用[]表示查询。 AS名称在Access中有效。
所以，我可以假设OP的帖子中有一些内容表明
他没有发送这个查询字符串在他的.NET应用程序中用于JET
（也许就是这样：.NET？），或者.NET将ANSI SQL发送到JET和
[]。因为名字不允许或者什么？
我很困惑。
哦，我已经说过了。 />
好的，我很困惑！
Terry
I''m confused.
In JET, I have used
[some select string]. AS name
to effect subqueries in SQL strings
for a long time. (I didn''t invent this by any means, but I may have
championed it).
I believe that in T-SQL we can just use
(some select string) AS name
OP states that the query with []. AS name does work in Access.
So, can I assume that there is something in OP''s post which indicated
he''s not sending this query string to JET in his .NET application
(maybe it''s just that: ".NET"?), or does .NET send ANSI SQL to JET and
[]. As name isn''t allowed there or what?
I''m confused.
Oh, I already said that.
OK, I''m very confused!
Lyle，
SQL我工作过的是OP为了摆脱
原始查询而复制的那个，我不相信这是他们所说的工作在
访问我认为OP指的是关于
的第一个陈述。总之，他们并不是说SQL在Access中有效，因此
并不是说[]分隔符有效。
[]曾经分隔子查询;我之前没有见过这样的，所以我测试了它，我不能在没有错误的情况下使用它作为查询或ADO，
两种情况都返回错误，即
Microsoft Jet数据库引擎无法找到输入表或查询
''SELECT * FROM YourTable''。
确保它存在并且其名称拼写正确。
BTW''SELECT * FROM YourTable''是t []中保存的子查询文本。< br $>
-
Terry Kreft
" lylefair" < LY *********** @ aim.com>在消息中写道
news：11 ********************** @ g43g2000cwa.googlegr oups.com ...
Lyle,
The SQL I worked on was the one the OP had compounded to get rid of the
original query, I don''t believe this is the one they stated was working in
Access I think the OP was referring to the first statements with respect to
this. In summary they aren''t saying the SQL worked in Access and therefore
aren''t saying the [] delimiter worked.
[] used to delimit a sub-query; I''ve not seen this before so I tested it, I
can''t get it to work without error in either Access as a query or ADO, in
both cases an error is returned i.e.
"The Microsoft Jet database engine cannot find the input table or query
''SELECT * FROM YourTable''.
Make sure it exists and that its name is spelled correctly."
BTW ''SELECT * FROM YourTable'' is the subquery text held in t [].
--
Terry Kreft
"lylefair" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
特里
我很困惑。
在JET中，我使用了
[一些选择字符串]。 AS名称
长时间在SQL字符串中实现子查询
。 （我没有以任何方式发明这个，但我可能已经支持它了。）
我相信在T-SQL中我们可以使用
（一些选择字符串）AS名称
OP表示查询带有[]。 AS名称在Access中起作用。
因此，我可以假设OP的帖子中有一些内容表明他没有将此查询字符串发送给他的JET。 NET应用程序
（也许只是那个：.NET？），或者.NET将ANSI SQL发送到JET和
[]。因为名字不允许或者什么？
我很困惑。
哦，我已经说过了。
好的，我很困惑！
Terry
I''m confused.
In JET, I have used
[some select string]. AS name
to effect subqueries in SQL strings
for a long time. (I didn''t invent this by any means, but I may have
championed it).
I believe that in T-SQL we can just use
(some select string) AS name
OP states that the query with []. AS name does work in Access.
So, can I assume that there is something in OP''s post which indicated
he''s not sending this query string to JET in his .NET application
(maybe it''s just that: ".NET"?), or does .NET send ANSI SQL to JET and
[]. As name isn''t allowed there or what?
I''m confused.
Oh, I already said that.
OK, I''m very confused!
这篇关于Query中的FROM子句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
