INNER JOIN auto changes

  • Thread starter Thread starter hmmmm?
  • Start date Start date
H

hmmmm?

I have this query(A) which works fine, however, whenever I make any
modification (e.g. change an alias) to another query used in query(A) the
syntax of QueryA changes and it doesn't work. Here's the query before it
changes:

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS MaxVol
FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM]. AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

and here's the query after

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS MaxVol
FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM;] AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

The only thing that changes is around the embedded SELECT....so "]. AS T2"
changes to ";] AS T2"

It's not a huge issue to change it back and save it, however is REALLY
annoying and I want to understand why it's happening.

Thanks :)
 
You have syntax errors (I do not see how it works as is but I am getting
suprised all the time.)
A subquery needs to be started and ended with parenthesis instead of
brackets as you have.
I always get errors if I try outputting more than one field from a subquery
as you did. The error says I did not use EXISTS.
Why the period following NUM]. -- GROUP BY PLOT, NUM]. AS T2 ON
 
That bracket syntax was required for A97 and, I think, A2000. it was an
undocumented hack somebody found back then to enable the use of inline
subqueries in the FROM clause. It appears the syntax is still supported,
but I don't know if it will be supported forever.
To the OP - as the others have said, change to parentheses and get rid
of that period if you are using a newer version of Access.

Also, the only time I've seen the sql getting changed is when I've made
the mistake of switching to Design view. Never switch to Design view if
you need your sql unchanged.

KARL said:
You have syntax errors (I do not see how it works as is but I am
getting suprised all the time.)
A subquery needs to be started and ended with parenthesis instead of
brackets as you have.
I always get errors if I try outputting more than one field from a
subquery as you did. The error says I did not use EXISTS.
Why the period following NUM]. -- GROUP BY PLOT, NUM]. AS T2 ON

--
Build a little, test a little.


hmmmm? said:
I have this query(A) which works fine, however, whenever I make any
modification (e.g. change an alias) to another query used in
query(A) the syntax of QueryA changes and it doesn't work. Here's
the query before it changes:

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS
MaxVol FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM]. AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

and here's the query after

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS
MaxVol FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM;] AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

The only thing that changes is around the embedded SELECT....so "].
AS T2" changes to ";] AS T2"

It's not a huge issue to change it back and save it, however is
REALLY annoying and I want to understand why it's happening.

Thanks :)
Click to expand...
Click to expand...
 
First of all, thanks so much for the responses!

So... I've changed the square brackets to parentheses and removed the period
(suggested by Bob). The query works with these changes.

However, the same problem occurs when i change anything in a related query

i.e. ") AS T2" changes to ";] AS T2"

I've even tried to build the sub-query as an independent object but for some
CRAZY reason the MaxVol doesn't relate back to perc_vol (which I don't
understand how that's possible)..

ay ay ay
I appreciate the help :)
 
Run a check on the MaxVol and see want it is outputting. Maybe some math
changes due to formating or such.
--
Build a little, test a little.


hmmmm? said:
First of all, thanks so much for the responses!

So... I've changed the square brackets to parentheses and removed the period
(suggested by Bob). The query works with these changes.

However, the same problem occurs when i change anything in a related query

i.e. ") AS T2" changes to ";] AS T2"

I've even tried to build the sub-query as an independent object but for some
CRAZY reason the MaxVol doesn't relate back to perc_vol (which I don't
understand how that's possible)..

ay ay ay
I appreciate the help :)



hmmmm? said:
I have this query(A) which works fine, however, whenever I make any
modification (e.g. change an alias) to another query used in query(A) the
syntax of QueryA changes and it doesn't work. Here's the query before it
changes:

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS MaxVol
FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM]. AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

and here's the query after

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS MaxVol
FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM;] AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

The only thing that changes is around the embedded SELECT....so "]. AS T2"
changes to ";] AS T2"

It's not a huge issue to change it back and save it, however is REALLY
annoying and I want to understand why it's happening.

Thanks :)
Click to expand...
Click to expand...
 
Thanks Karl! The formatting solved the problem.

Although....by implementing this sub-query as an independent object means
LOTS more work (i.e. revamping the whole database).

I will wait a day or two to see if anyone knows how to solve my sub-query
problem...

Cheers




KARL DEWEY said:
Run a check on the MaxVol and see want it is outputting. Maybe some math
changes due to formating or such.
--
Build a little, test a little.


hmmmm? said:
First of all, thanks so much for the responses!

So... I've changed the square brackets to parentheses and removed the period
(suggested by Bob). The query works with these changes.

However, the same problem occurs when i change anything in a related query

i.e. ") AS T2" changes to ";] AS T2"

I've even tried to build the sub-query as an independent object but for some
CRAZY reason the MaxVol doesn't relate back to perc_vol (which I don't
understand how that's possible)..

ay ay ay
I appreciate the help :)



hmmmm? said:
I have this query(A) which works fine, however, whenever I make any
modification (e.g. change an alias) to another query used in query(A) the
syntax of QueryA changes and it doesn't work. Here's the query before it
changes:

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS MaxVol
FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM]. AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

and here's the query after

SELECT T1.PLOT, T1.NUM, T1.GENUS, T1.SPECIES
FROM spp_vol AS T1 INNER JOIN [SELECT PLOT, NUM, MAX(perc_vol) AS MaxVol
FROM spp_vol WHERE FORTYPE="SW" GROUP BY PLOT, NUM;] AS T2 ON
(T1.PLOT=T2.PLOT) AND (T1.NUM=T2.NUM) AND (T1.perc_vol=T2.MaxVol)
WHERE FORTYPE="SW"
GROUP BY T1.PLOT, T1.NUM, GENUS, SPECIES;

The only thing that changes is around the embedded SELECT....so "]. AS T2"
changes to ";] AS T2"

It's not a huge issue to change it back and save it, however is REALLY
annoying and I want to understand why it's happening.

Thanks :)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top