By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 2,104 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Acc2003: Problem with UNION query in subreport?

P: n/a
Hi All,

I'd like to place a subreport onto a report that draws its records from
a UNION query.

When I design the query and view the records all looks fine. However,
when I put the query as the subreport's data source and open it, Access
complains that Jet can't find the table, and then when I open up the
query itself to look at the SQL, it appears all mangled.

The query def is as follows:

SELECT recdate, orderid, outcontext, outtext, reviewdate, recstatus
FROM (SELECT recdate, orderid, iif(context=1, "Call", iif(context=2,
"Email", iif(context=3,"Check Order",iif(context=4,"Speak to","Find
out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
"Outstanding", "Resolved") as recstatus FROM tbl_ordernextactions
UNION
SELECT recdate, orderid,iif(notetype=1, "Outstanding", "Resolved") as
outcontext, note as outtext, "" as reviewdate, "" as recstatus FROM
tbl_ordernotes) AS unquery order by recdate

When I try and attach it to the subreport and open it, it gets mangled
to:

SELECT recdate, orderid, outcontext, outtext, reviewdt, recstatus
FROM [SELECT recdate, orderid, iif(context=1, "Call", iif(context=2,
"Email", iif(context=3,"Check Order",iif(context=4,"Speak to","Find
out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
"Outstanding", "Resolved") as recstatus FROM tbl_o] AS unquery
ORDER BY recdate;

Is this a bug, or a limitation of Acc2003, or am I doing something
wrong in my query def?

Any help appreciated!!

Much warmth,

planetthoughtful
---
"Lost in thought"
http://www.planetthoughtful.org

Mar 12 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Okay, it appears it is a limitation of Acc2003, in that it doesn't seem
to like UNION queries embedded in a subquery. A little odd - most other
db environments would cope with this, but now that I've removed the
subquery component and I'm providing ordering via the subreport, all
seems to work okay.

Much warmth,

planetthoughtful
---
"Lost in thought"
http://www.planetthoughtful.org

Mar 12 '06 #2

P: n/a
Yes, as others have pointed out before me :~), Access will
tend to mangle "(select ...)" into "[select ...]."
(Note the full stop after the final square bracket)

[]. was the Jet 3.5 format, but never properly supported.
() is the Jet 4.0 format, but Access will rewrite it
if given a chance.

For very simple sub-selects, I think that this might not
matter. For more complex sub-selects, you need to write
the sub-select as a stored query. For dynamic SQL, where
Access doesn't have a chance to re-write the SQL, you
can use anything that works.

(david)

"planetthoughtful" <pl**************@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
Hi All,

I'd like to place a subreport onto a report that draws its records from
a UNION query.

When I design the query and view the records all looks fine. However,
when I put the query as the subreport's data source and open it, Access
complains that Jet can't find the table, and then when I open up the
query itself to look at the SQL, it appears all mangled.

The query def is as follows:

SELECT recdate, orderid, outcontext, outtext, reviewdate, recstatus
FROM (SELECT recdate, orderid, iif(context=1, "Call", iif(context=2,
"Email", iif(context=3,"Check Order",iif(context=4,"Speak to","Find
out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
"Outstanding", "Resolved") as recstatus FROM tbl_ordernextactions
UNION
SELECT recdate, orderid,iif(notetype=1, "Outstanding", "Resolved") as
outcontext, note as outtext, "" as reviewdate, "" as recstatus FROM
tbl_ordernotes) AS unquery order by recdate

When I try and attach it to the subreport and open it, it gets mangled
to:

SELECT recdate, orderid, outcontext, outtext, reviewdt, recstatus
FROM [SELECT recdate, orderid, iif(context=1, "Call", iif(context=2,
"Email", iif(context=3,"Check Order",iif(context=4,"Speak to","Find
out")))) as outcontext, nextaction as outtext, reviewdt, IIF(status=1,
"Outstanding", "Resolved") as recstatus FROM tbl_o] AS unquery
ORDER BY recdate;

Is this a bug, or a limitation of Acc2003, or am I doing something
wrong in my query def?

Any help appreciated!!

Much warmth,

planetthoughtful
---
"Lost in thought"
http://www.planetthoughtful.org

Mar 13 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.