SQL Division

[Guest]

I have 2 sql

The First
SELECT G05_LacGastIngr.CodProd, G05_LacGastIngr.CodPer,
G05_LacGastIngr.CodLac, Sum(G05_LacGastIngr.Cantidad) AS Cantidad
FROM G05_LacGastIngr
WHERE (((G05_LacGastIngr.CodGastIngr) In ("2")))
GROUP BY G05_LacGastIngr.CodProd, G05_LacGastIngr.CodPer,
G05_LacGastIngr.CodLac
HAVING (((G05_LacGastIngr.CodLac)="1"));

The Second
SELECT [7AreaFinca].CodProd, [7AreaFinca].CodPer, [7AreaFinca].AreaTotal
FROM 7AreaFinca
WHERE ((([7AreaFinca].CodPer)="11"));

The Division qry First/Second
SELECT tabla1.CodProd, tabla1.CodPer, tabla1.CodLac,
[tabla1].[leche/vaca]/[tabla2].[AreaTotal] AS [Produccion/Vaca]
FROM (SELECT G05_LacGastIngr.CodProd, G05_LacGastIngr.CodPer,
G05_LacGastIngr.CodLac, Sum(G05_LacGastIngr.Cantidad) AS Cantidad
FROM G05_LacGastIngr
WHERE (((G05_LacGastIngr.CodGastIngr) In ("2")))
GROUP BY G05_LacGastIngr.CodProd, G05_LacGastIngr.CodPer,
G05_LacGastIngr.CodLac
HAVING (((G05_LacGastIngr.CodLac)="1"));
).AS Tabla1, (SELECT [7AreaFinca].CodProd, [7AreaFinca].CodPer,
[7AreaFinca].AreaTotal
WHERE ((([7AreaFinca].CodPer)="11"));
FROM 7AreaFinca
).AS Tabla2
WHERE (((tabla1.CodProd)=[tabla2].[codprod]) AND
((tabla1.CodPer)=[tabla2].[codper]) AND ((tabla1.CodLac)=[tabla2].[codlac]));

However, when I run the Query (division) the message "Syntax error in FROM
clause"is displayed. I have been checking the syntax and I can not find the
mistake. How do I fix the error?Could you help me?

I would like to share my experience with division operation:
I did a couple of division and they work.
If I copy and paste the syntax of a working qry to a new query it doesn't
work. Seems to be copy and paste is no good idea to create a new sql.
Many thanks

 

[Roger Carlson]

Is this query a Pass-Through query to a SQL Server database? Because that's
the only way I see this query ever working.

In Access, the only way you can have a Subquery in the FROM clause is to
surround the subquery with brackets ([]), NOT parenthases. The problem,
however, is that you CANNOT, then have any brackets in your subquery. On my
website (www.rogersaccesslibrary.com), is a small Access database sample
called "SubQueryInFROM.mdb" which illustrates this.

If it IS a pass-through query, copying the SQL to a new query will not work
until you convert your new query to a pass-through query as well. On the
toolbar, go to Query>SQL Specific>Pass-through.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[AaronKempff]

In Access, the only way you can have a Subquery in the FROM clause is to
surround the subquery with brackets ([]), NOT parenthases. The problem,
however, is that you CANNOT, then have any brackets in your subquery.

Oh yeah?

select *
from (
select OrderID, max(UnitPrice)
from [Order Details]
group by OrderID
) as t

I suppose this is an IMPOSSIBLE query that can just never be made to
work in Access? LOL

Want answers to your Access answer?

 

[Stefan Hoffmann]

hi,

However, when I run the Query (division) the message "Syntax error in FROM
clause"is displayed. I have been checking the syntax and I can not find the
mistake. How do I fix the error?Could you help me?

Store the subqueries as normal queries, and try to "divide" them.


mfG
--> stefan <--

 

[Roger Carlson]

Why would Jamie Collins want to impersonate Aaron K.e.m.p.f?

http://groups.google.com/group/micr...bd4f/c4ff024bd7652b5b?&hl=en#c4ff024bd7652b5b

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

In Access, the only way you can have a Subquery in the FROM clause is to
surround the subquery with brackets ([]), NOT parenthases. The problem,
however, is that you CANNOT, then have any brackets in your subquery.

Oh yeah?

select *
from (
select OrderID, max(UnitPrice)
from [Order Details]
group by OrderID
) as t

I suppose this is an IMPOSSIBLE query that can just never be made to
work in Access? LOL

Want answers to your Access answer?

 

[Jamie Collins]

Why would Jamie Collins want to impersonate Aaron K.e.m.p.f?

Homage <g>? I thought it would make an amusing twist to for someone to
impersonate him for a change. And I would have got away with it too,
if it hadn't been for you pesky MDB kids ...oh, and the obvious give
away of saying something other than the 'use SQL Server' mantra, lack
of profanity, etc.

 

[Roger Carlson]

Perhaps it might be more amusing if you tried to help the original poster.

 

[Roger Carlson]

Yes, this will work. However if you go to the Design View of the query and
save it, it will revert to the bracketed version:

select *
from [select OrderID, max(UnitPrice)
from [Order Details]
group by OrderID]. as t

So the version with parentheses is not stable in Access.

As for the original post:

It appears that you are mixing syntax. You are using the parenthases for
the subquery, but following it with a dot. Have you tried removing the dot?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

In Access, the only way you can have a Subquery in the FROM clause is to
surround the subquery with brackets ([]), NOT parenthases. The problem,
however, is that you CANNOT, then have any brackets in your subquery.

Oh yeah?

select *
from (
select OrderID, max(UnitPrice)
from [Order Details]
group by OrderID
) as t

I suppose this is an IMPOSSIBLE query that can just never be made to
work in Access? LOL

Want answers to your Access answer?

 

[Jamie Collins]

Perhaps it might be more amusing if you tried to help the original poster.

I'm not sure I understand the question. By 'SQL Division', do they
mean 'relational division' (e.g. the supplier that supplies all
parts)? And because they've prefixed some columns with 'Cod' are they
alluding to Codd's definition thereof?

What really amuses me is that a MS Access MVP can post something
obviously erroneous about Access in a Microsoft public forum such "the
only way you can have a Subquery in the FROM clause is to surround the
subquery with brackets ([]), NOT parenthases" and when challenged on
it completely fail to acknowledge the point. If you guys never learn,
what chance do I have of educating less experienced participants such
as the OP? I'd be tickled pink if you could answer that one for me.

Jamie.

--

 

[Roger Carlson]

I thought I did acknowledge it in a subsequent post. But perhaps I didn't
grovel sufficiently for you. Sorry. I'm very grateful for the opportunity
to learn something new.

But my purpose for participating in these newsgroups is to help people solve
problems, not to showcase my brilliance by ridiculing others. In that, we
obviously differ.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

Perhaps it might be more amusing if you tried to help the original
poster.

I'm not sure I understand the question. By 'SQL Division', do they
mean 'relational division' (e.g. the supplier that supplies all
parts)? And because they've prefixed some columns with 'Cod' are they
alluding to Codd's definition thereof?

What really amuses me is that a MS Access MVP can post something
obviously erroneous about Access in a Microsoft public forum such "the
only way you can have a Subquery in the FROM clause is to surround the
subquery with brackets ([]), NOT parenthases" and when challenged on
it completely fail to acknowledge the point. If you guys never learn,
what chance do I have of educating less experienced participants such
as the OP? I'd be tickled pink if you could answer that one for me.

Jamie.

 

[Jamie Collins]

my purpose for participating in these newsgroups is to help people solve
problems

And if in the process of trying to help you inadvertently made a
misstatement that provided a distraction from a solution, would you
want to resolve it?

I've a proposal: we collaborate to resolve your point to our mutual
satisfaction, thank each other sincerely for taking the time, then the
one of us with the better 'people skills' and greater 'ownership' of
the OP's problem (which I suggest is you) can give a more direct
answer to the OP. OK?

A reminder of your point:

In Access, the only way you can have a Subquery in the FROM clause is to
surround the subquery with brackets ([]), NOT parenthases. The problem,
however, is that you CANNOT, then have any brackets in your subquery.

Given the example

SELECT *
FROM (
SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
) AS t;

which features

· a subquery in the FROM clause
· parentheses surrounding the subquery
· brackets and parentheses within the subquery

you later qualified:

Yes, this will work. However if you go to the Design View of the query and
save it, it will revert to the bracketed version...
So the version with parentheses is not stable in Access.

I tested on Access2000 and Access2007. The query executes without
error. The query saves without error. Upon switching between 'design
view' and 'SQL view', saving and reopening and combinations thereof,
the SQL text remains identical.

In case it makes a difference, I used the Access interface to create a
new Query object in 'design view', switched to 'SQL view' at the
earliest opportunity and pasted in the SQL text as posted. In detail,
on the Access2007 ribbon choose 'Create' (menu item), then 'Query
Wizard' (button); on the resulting 'Show Table' dialog and with no
table selected, hit 'Close' (button); on the ribbon, choose 'SQL
View' (button). At this point I can save and reopen, execute or switch
views, in any order, and the SQL text remains identical and
functional, with the subquery remaining in parentheses.

In short, nothing you have said about 'bracketing' is bourn out by my
testing and IMO can be disregarded. Therefore, we are back at, "Try
removing the dot."

Jamie.

--

 

[Michael Gramelspacher]

my purpose for participating in these newsgroups is to help people solve
problems

And if in the process of trying to help you inadvertently made a
misstatement that provided a distraction from a solution, would you
want to resolve it?

I've a proposal: we collaborate to resolve your point to our mutual
satisfaction, thank each other sincerely for taking the time, then the
one of us with the better 'people skills' and greater 'ownership' of
the OP's problem (which I suggest is you) can give a more direct
answer to the OP. OK?

A reminder of your point:

In Access, the only way you can have a Subquery in the FROM clause is to
surround the subquery with brackets ([]), NOT parenthases. The problem,
however, is that you CANNOT, then have any brackets in your subquery.

Given the example

SELECT *
FROM (
SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
) AS t;

which features

· a subquery in the FROM clause
· parentheses surrounding the subquery
· brackets and parentheses within the subquery

you later qualified:

Yes, this will work. However if you go to the Design View of the queryand
save it, it will revert to the bracketed version...
So the version with parentheses is not stable in Access.

I tested on Access2000 and Access2007. The query executes without
error. The query saves without error. Upon switching between 'design
view' and 'SQL view', saving and reopening and combinations thereof,
the SQL text remains identical.

In case it makes a difference, I used the Access interface to create a
new Query object in 'design view', switched to 'SQL view' at the
earliest opportunity and pasted in the SQL text as posted. In detail,

Jamie,

Is this discussion about Access 2007?

In Access 2003
SELECT *
FROM (
SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
) AS t;

became

SELECT *
FROM [SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
]. AS t;

when query was saved.

Changing views does not change the parentheses to brackets plus period,
saving the query does.

My experience is that making a change to the SQL with a bracketed FROM
clause often causes an error on saving the change. All that is required to
get around the error is to replace the brackets with parentheses and then
save. Of course, the parentheses are changed right back to brackets upon
saving.

 

[Jamie Collins]

Is this discussion about Access 2007?

No. I get the same behaviour both versions of Access I currently have
installed, being Access2000 and Access2007. I chose to describe my
steps to reproduce using Access2007 because it's the most recent and
because the regulars do not rate Access2000 as a 'good' release.

In Access 2003
SELECT *
FROM (
SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
) AS t;

became

SELECT *
FROM [SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
]. AS t;

when query was saved.

Changing views does not change the parentheses to brackets plus period,
saving the query does.

My experience is that making a change to the SQL with a bracketed FROM
clause often causes an error on saving the change. All that is required to
get around the error is to replace the brackets with parentheses and then
save. Of course, the parentheses are changed right back to brackets upon
saving.

If you've only tested on Access2003, and in lieu of anyone else
jumping in, perhaps this behaviour is limited to in Access2003. FWIW I
have all versions from Access95 through Access2007 available to
install if you think I should test on a specific version.

I think the current position is sometimes (on Access2003 only?) it
changes it to brackets but other times (on Access2000 and Access2007)
it never does. I suspect it has nothing to do with versioning and
everything to do with the way the 'Query' object was first created.
When using the Access UI, I never actively use the GUI designer view
and always switch to SQL view at the earliest opportunity. More often,
though, to create a persisted object comprising a query I use CREATE
VIEW or CREATE PROCEDURE. Coupled with a sensible naming convention
for data elements and a desire to write portable code whenever
possible, I never need to use brackets

Please try creating the 'Query' object using CREATE VIEW (I've seen
you post SQL DDL so I assume you know how to do this <g>) using the
parentheses then open it in the Access UI to see if it changes the
parentheses to brackets. Please post your findings, TIA.

I think the original author has revised his assertion to somthing
like:

"In Access, the only way you can have a *stable* Subquery in the FROM
clause is to
surround the subquery with brackets ([]), NOT parenthases."

At this point I still disagree because, for me (and perhaps the OP), a
FROM clause subquery in parentheses is both functional and stable.

SELECT *
FROM [SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
]. AS t;

Do I understand from the above that you disagree the authors other
assertion:

"The problem, however, is that you CANNOT, then have any brackets in
your subquery."

because your posted query has brackets (i.e. [Order Details]) within
the subquery in brackets? Please clarify your position.

TIA,
Jamie.

--

 

[Michael Gramelspacher]

Is this discussion about Access 2007?

No. I get the same behaviour both versions of Access I currently have
installed, being Access2000 and Access2007. I chose to describe my
steps to reproduce using Access2007 because it's the most recent and
because the regulars do not rate Access2000 as a 'good' release.

In Access 2003
SELECT *
FROM (
SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
) AS t;

became

SELECT *
FROM [SELECT OrderID, max(UnitPrice)
FROM [Order Details]
GROUP BY OrderID
]. AS t;

when query was saved.

Changing views does not change the parentheses to brackets plus period,
saving the query does.

My experience is that making a change to the SQL with a bracketed FROM
clause often causes an error on saving the change. All that is required to
get around the error is to replace the brackets with parentheses and then
save. Of course, the parentheses are changed right back to brackets upon
saving.

If you've only tested on Access2003, and in lieu of anyone else
jumping in, perhaps this behaviour is limited to in Access2003. FWIW I
have all versions from Access95 through Access2007 available to
install if you think I should test on a specific version.

I think the current position is sometimes (on Access2003 only?) it
changes it to brackets but other times (on Access2000 and Access2007)
it never does. I suspect it has nothing to do with versioning and
everything to do with the way the 'Query' object was first created.
When using the Access UI, I never actively use the GUI designer view
and always switch to SQL view at the earliest opportunity. More often,
though, to create a persisted object comprising a query I use CREATE
VIEW or CREATE PROCEDURE. Coupled with a sensible naming convention
for data elements and a desire to write portable code whenever
possible, I never need to use brackets

Jamie,

Using Access 2003 even when the query is created with CREATE VIEW Access
saves the query with brackets.

CurrentProject.Connection.Execute _
"CREATE VIEW MyTest AS" & _
" SELECT * FROM (SELECT OrderID, max(UnitPrice)" & _
" FROM [Order Details]GROUP BY OrderID) AS t;"

SELECT *
FROM [SELECT OrderID, max(UnitPrice) FROM [Order Details] GROUP BY
OrderID]. AS t;

if I edit query in SQL View

SELECT OrderID
FROM [SELECT OrderID, max(UnitPrice) FROM [Order Details] GROUP BY
OrderID]. AS t;

I get error

Invalid bracketing of name 'Select ........

so I change it to

SELECT OrderID
FROM (SELECT OrderID, max(UnitPrice) FROM [Order Details] GROUP BY OrderID)
AS t;

and the query is saved and parentheses changed back to brackets.


Also, I recently had a query of the form
SELECT FROM Table INNER JOIN (SELECT FROM (SELECT ) );

Created in SQL view it became

SELECT FROM Table INNER JOIN [SELECT FROM [SELECT ]. ].;

Created with CREATE VIEW it became

SELECT FROM Table INNER JOIN [SELECT FROM (SELECT ) ].;

I am merely reporting my observations. I do not take issue with anyone.

 

[Marshall Barton]

If you've only tested on Access2003, and in lieu of anyone else
jumping in, perhaps this behaviour is limited to in Access2003. FWIW I
have all versions from Access95 through Access2007 available to
install if you think I should test on a specific version.

I can not comment on either Access 1 or Access 2000, but the
(Select ) subquery syntax was not introduced until after
A97. This made the issue of [ ] inside of [ ] a critical
consideration in A2, A95 and A97. As Michael is pointing
out, the issue of saving (Select ) as [Select ]. is
certainly an issue in both A2002 and A2003, but it appears
to be fixed in A2007.

 

[Jamie Collins]

Do I understand from the above that you disagree the authors other
assertion:

"The problem, however, is that you CANNOT, then have any brackets in
your subquery."

because your posted query has brackets (i.e. [Order Details]) within
the subquery in brackets? Please clarify your position.

I am merely reporting my observations. I do not take issue with anyone.

You fear that reaching a conclusion might show someone to have been
'wrong'? Shame. I try put personalities** to one side and stick to the
facts. That said, I consider a misstatement made by an MVP to be more
serious, therefore worthy of greater scrutiny, than one made by us
plebeians because they (rightly, IMO) command more respect from
newbies and (wrong, IMO) are given powers to 'Mark this as an answer'.
If it is 'wrong' of me to expect a higher standard from MVPs then
eject me from the group. Actually, don't bother, I'll go quietly.

** There are exceptions of course, for you have some great characters/
caricatures around here e.g. Messrs Kempf (Mr Angry from Pearly),
Linson (Valued Most Unprofessional) and Fenton (Cruella De Vil) ;-)

Jamie.

--

 

[Jamie Collins]

I can not comment on either Access 1 or Access 2000, but the
(Select ) subquery syntax was not introduced until after
A97. This made the issue of [ ] inside of [ ] a critical
consideration in A2, A95 and A97. As Michael is pointing
out, the issue of saving (Select ) as [Select ]. is
certainly an issue in both A2002 and A2003, but it appears
to be fixed in A2007.

Marsh
MVP [MS Access]

Thanks for pointing that out, Marsh.

Is there any conclusion to make other than:

"The A2002 and A2003 user interface has a bug that causes a VIEW's SQL
definition to be corrupted for some, maybe all, subquery constructs"?

The dot after the bracketing suggest to me that the subquery is being
parsed as a column name that contains spaces e.g.

[column has spaces here].TableNameHere

Just to clarify, it is the act of saving (not re-opening) the 'Query'
object that causes the corruption: create the Query object, switch to
SQL View, paste in the subquery with parentheses, save but do not re-
open then use Jet natively to examine the SQL definition e.g.

? CurrentProject.Connection.OpenSchema( _
adSchemaViews, Array(Empty, Empty, "QueryNameHere")) _
("VIEW_DEFINITION")

and it is seen to have be changed to the 'bracketing' syntax upon
saving.

I've long help the view that mdb corruption issues are a direct
results of the Access user interface and have nothing to do with Jet,
which looks remarkable stable in comparison. I have had a test mdb for
many years, on which I've performed all kinds of funky things using
Jet but not Access, and, file bloat aside, have had no problems.

I'm tempted to add, "Here we have some more great reasons for not
bothering with the Access user interface and instead using SQL DDL
<g>"

....But I think looking at the big picture that the correct conclusion
is, "Jet is dead. Good look everyone with Access2007 and beyond."

Jamie.

--

 

[Marshall Barton]

I can not comment on either Access 1 or Access 2000, but the
(Select ) subquery syntax was not introduced until after
A97. This made the issue of [ ] inside of [ ] a critical
consideration in A2, A95 and A97. As Michael is pointing
out, the issue of saving (Select ) as [Select ]. is
certainly an issue in both A2002 and A2003, but it appears
to be fixed in A2007.

Thanks for pointing that out, Marsh.

Is there any conclusion to make other than:

"The A2002 and A2003 user interface has a bug that causes a VIEW's SQL
definition to be corrupted for some, maybe all, subquery constructs"?

AFAIK, this issue only happens in FROM/INTO/etc clauses.
Using (SELECT ) syntax has never been an issue in a
calculated field or the WHERE/ORDER BY/etc clauses.

The dot after the bracketing suggest to me that the subquery is being
parsed as a column name that contains spaces e.g.

[column has spaces here].TableNameHere

No.

The [ ]. subquery syntax is more like an afterthought on
one of the systax choices for referencing a DSN less
external table. E.g.
[Excel 8.0;HDR=NO;DATABASE=D:\mybook.xls].[Sheet1]
is an alternative of
Sheet1 IN "" "Excel 8.0;HDR=NO;DATABASE=D:\mybook.xls"

[snip]

...But I think looking at the big picture that the correct conclusion
is, "Jet is dead. Good look everyone with Access2007 and beyond."

That's ridiculous!

A2007 has a new, partially revamped version of Jet.