Peculiar Behavior of Query

  • Thread starter Thread starter Claudette Hennessy
  • Start date Start date
C

Claudette Hennessy

Recently I posted a problem (cf Query Impossible) which Bob Barrows (MVP)
provided a solution for. My question only listed basic fields in the query.
Bob Barrows' solution using generic fields was:

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer


However the query got changed upon saving to:

Select Dealer,Max(DealerYear) As FilteredYear FROM
[select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table)]. as q
group by Dealer

tblContractTemp ShopID Spring Summer Fall ShowYear DateMailed
1 -1 0 0 2009

2 -1 0 0 2009

2 -1 0 0 2008

3 -1 -1 -1 2008 8/12/2008
4 -1 -1 -1 2008 8/12/2008
5 -1 0 0 2009

6 -1 0 0 2009



My actual query using real fields which provides the desired results is:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1,Min(q.FilteredSummer) AS
Summer1,Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS DateSelected,
Max(q.DealerYear) AS FilteredYear
FROM (SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp
) AS q
GROUP BY q.ShopID;

Which gets saved as:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1, Min(q.FilteredSummer) AS
Summer1, Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS DateSelected,
Max(q.DealerYear) AS FilteredYear
FROM [SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp
]. AS q
GROUP BY q.ShopID;

The queries with the [ for ( and ]. for ) substitution will execute, but do
not permit an edit, even an adding a space text edit , resulting in a
'Invalid bracketing of name ...' error.


????
Claudette
 
What you're observing is the manner in which ACCESS/Jet stores a query that
uses another "SELECT" query statement as a data table source. Unfortunately,
you cannot prevent this type of saving method in ACCESS, and as you've noted
the query cannot be edited because the embedded [ ] characters for that
"SELECT" query are misinterpreted by the SQL parser when you try to edit the
query.

The workaround is to save that "SELECT" query as a separate query, and then
use that query in your original query.

Save this query as a new query (name it qryFilteredQuery):

SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp


Then rewrite your original query this way:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1, Min(q.FilteredSummer) AS
Summer1, Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS DateSelected,
Max(q.DealerYear) AS FilteredYear
FROM qryFilteredQuery AS q
GROUP BY q.ShopID;

--

Ken Snell
<MS ACCESS MVP>


Claudette Hennessy said:
Recently I posted a problem (cf Query Impossible) which Bob Barrows (MVP)
provided a solution for. My question only listed basic fields in the
query.
Bob Barrows' solution using generic fields was:

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer


However the query got changed upon saving to:

Select Dealer,Max(DealerYear) As FilteredYear FROM
[select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table)]. as q
group by Dealer

tblContractTemp ShopID Spring Summer Fall ShowYear DateMailed
1 -1 0 0 2009

2 -1 0 0 2009

2 -1 0 0 2008

3 -1 -1 -1 2008 8/12/2008
4 -1 -1 -1 2008 8/12/2008
5 -1 0 0 2009

6 -1 0 0 2009



My actual query using real fields which provides the desired results is:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1,Min(q.FilteredSummer) AS
Summer1,Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS DateSelected,
Max(q.DealerYear) AS FilteredYear
FROM (SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp
) AS q
GROUP BY q.ShopID;

Which gets saved as:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1, Min(q.FilteredSummer)
AS Summer1, Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS
DateSelected, Max(q.DealerYear) AS FilteredYear
FROM [SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp
]. AS q
GROUP BY q.ShopID;

The queries with the [ for ( and ]. for ) substitution will execute, but
do not permit an edit, even an adding a space text edit , resulting in a
'Invalid bracketing of name ...' error.


????
Claudette
Click to expand...
 
Thank you, that works.
Claudette
Ken Snell (MVP) said:
What you're observing is the manner in which ACCESS/Jet stores a query
that uses another "SELECT" query statement as a data table source.
Unfortunately, you cannot prevent this type of saving method in ACCESS,
and as you've noted the query cannot be edited because the embedded [ ]
characters for that "SELECT" query are misinterpreted by the SQL parser
when you try to edit the query.

The workaround is to save that "SELECT" query as a separate query, and
then use that query in your original query.

Save this query as a new query (name it qryFilteredQuery):

SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp


Then rewrite your original query this way:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1, Min(q.FilteredSummer)
AS
Summer1, Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS
DateSelected,
Max(q.DealerYear) AS FilteredYear
FROM qryFilteredQuery AS q
GROUP BY q.ShopID;

--

Ken Snell
<MS ACCESS MVP>


Claudette Hennessy said:
Recently I posted a problem (cf Query Impossible) which Bob Barrows
(MVP) provided a solution for. My question only listed basic fields in
the query.
Bob Barrows' solution using generic fields was:

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer


However the query got changed upon saving to:

Select Dealer,Max(DealerYear) As FilteredYear FROM
[select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table)]. as q
group by Dealer

tblContractTemp ShopID Spring Summer Fall ShowYear DateMailed
1 -1 0 0 2009

2 -1 0 0 2009

2 -1 0 0 2008

3 -1 -1 -1 2008 8/12/2008
4 -1 -1 -1 2008 8/12/2008
5 -1 0 0 2009

6 -1 0 0 2009



My actual query using real fields which provides the desired results is:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1,Min(q.FilteredSummer)
AS Summer1,Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS
DateSelected, Max(q.DealerYear) AS FilteredYear
FROM (SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp
) AS q
GROUP BY q.ShopID;

Which gets saved as:

SELECT q.ShopID, Min(q.FilteredSpring) AS Spring1, Min(q.FilteredSummer)
AS Summer1, Min(q.FilteredFall) AS Fall1, First(q.DateMailed) AS
DateSelected, Max(q.DealerYear) AS FilteredYear
FROM [SELECT tblContractTemp.ShopID, IIf([ShowYear]=[Enter
Year],[Spring],Null) AS FilteredSpring, IIf([ShowYear]=[Enter
Year],[Summer],Null) AS FilteredSummer,
IIf([ShowYear]=[Enter Year],[Fall],Null) AS FilteredFall,
tblContractTemp.DateMailed AS DateMailed, IIf([ShowYear]=[Enter
Year],[ShowYear],Null) AS DealerYear
FROM tblContractTemp
]. AS q
GROUP BY q.ShopID;

The queries with the [ for ( and ]. for ) substitution will execute, but
do not permit an edit, even an adding a space text edit , resulting in a
'Invalid bracketing of name ...' error.


????
Claudette
Click to expand...
Click to expand...
 
Back
Top