Problem with working queries when used in UNION

[Geoff]

Hi, all.

I'm seeking an explanation or fix for the following issue.
First some background...

Working with a copy of an Informix DB in MS-Access 2002 (2000 file format).
I have no control over the data structure - it is part of a prorietary
management system. The queries are for external reporting in Crystal
Reports only. Ideally, for speed, a single 'all encompassing' table is best
for crystal. I have successfully moved earlier versions of the queries
between Access SQL and Informix SQL.

I have created a set of queries to link account codes with manager
information. The queries gather different sub-sets of the account codes -
managed at the account code level, managed at parent level, not managed, and
each works fine by itself. There are multiple accoun levels (master,
subacct and activity). Any account code does not necessarily have its
manager directly defined - often at the parent or grandparent level (or
both!)

The trick is that for manager ID, I have to derive it from a key-string and
use the *calculated* number to link to manager details - I join to the same
staff details table twice in each query. This appears to be where the issue
arises in the UNION, because if I remove it, the UNION works (of course the
data is incomplete ). Secondly, for the partial data sets e.g. parent
management defined, I need to link on a part of the account code string to a
second copy of an owner table, so that link value is derived/calculated
too.... There are a total of 9 individual queries in the full UNION, about
27k characters!

Confusingly, and not able to be used in the final solution, if each of the
queries is added as part of a SELECT into the UNION, the full data set is
returned. - this works:
SELECT * FROM [vk SBAL Direct Resp ALL]
UNION
SELECT * FROM [vk SBAL Master Parent ALL]
UNION
SELECT * FROM [vk SBAL No Resp ALL]
....

Using the SQL of these queries directly in the final query does not work!
Here are some sample queries:

Managed at the Account Level
SELECT DISTINCT subacct.lgr_acc, subacct.fnd_num, subacct.gen_num,
subacct.sub_num, 0 AS atv_num, subacct.acc_dsc, subacct.acc_lvl,
subacct.pst_yer, <lots of financial values>, acckeys.key_dsc,
respstr.rsp_stt, respstr.nod_num, 1*Left([respstr].[rsp_key],4) AS mgr_key,
officers_1.pos_dsc AS mgr_pos, respstr.rsp_off, officers.pos_dsc,
respstr.nod_dsc, 1 AS lgr_lvl, Count(actacct.atv_num) AS childcnt
FROM (((((subacct LEFT JOIN actacct ON (subacct.pst_yer = actacct.pst_yer)
AND (subacct.sub_num = actacct.sub_num) AND (subacct.gen_num =
actacct.gen_num)) INNER JOIN (resplnk INNER JOIN respstr ON (resplnk.nod_num
= respstr.nod_num) AND (resplnk.rsp_stt = respstr.rsp_stt)) ON
subacct.lgr_acc = resplnk.lgr_acc) INNER JOIN mstacct ON subacct.gen_num =
mstacct.gen_num) INNER JOIN acckeys ON mstacct.tot_key = acckeys.key_val)
INNER JOIN officers ON respstr.rsp_off = officers.off_num) INNER JOIN
officers AS officers_1 ON {*1*Left([respstr].[rsp_key],4) =
officers_1.off_num*}
WHERE (((subacct.clo_dte) Is Null) AND ((subacct.pst_yer)=2004))
GROUP BY subacct.lgr_acc, subacct.fnd_num, subacct.gen_num, subacct.sub_num,
subacct.acc_dsc, subacct.acc_lvl, subacct.pst_yer, <lots of financial
values>, acckeys.key_dsc, respstr.rsp_stt, respstr.nod_num,
1*Left([respstr].[rsp_key],4), officers_1.pos_dsc, respstr.rsp_off,
officers.pos_dsc, respstr.nod_dsc;



Managed at the parent level.

SELECT DISTINCT subacct.lgr_acc, subacct.fnd_num, subacct.gen_num,
subacct.sub_num, 0 AS atv_num, subacct.acc_dsc, subacct.acc_lvl,
subacct.pst_yer, <lots of financial values>, acckeys.key_dsc,
respstr.rsp_stt, respstr.nod_num, 1*Left([respstr].[rsp_key],4) AS mgr_key,
officers_1.pos_dsc AS mgr_dsc, respstr.rsp_off, officers.pos_dsc,
respstr.nod_dsc, 1 AS lgr_lvl, Count(actacct.atv_num) AS childcnt
FROM ((((((subacct LEFT JOIN actacct ON (subacct.pst_yer = actacct.pst_yer)
AND (subacct.sub_num = actacct.sub_num) AND (subacct.gen_num =
actacct.gen_num)) INNER JOIN (resplnk INNER JOIN respstr ON (resplnk.nod_num
= respstr.nod_num) AND (resplnk.rsp_stt = respstr.rsp_stt)) ON
{*left(subacct.lgr_acc,4) = resplnk.lgr_acc*}) INNER JOIN mstacct ON
subacct.gen_num = mstacct.gen_num) INNER JOIN acckeys ON mstacct.tot_key =
acckeys.key_val) INNER JOIN officers ON respstr.rsp_off = officers.off_num)
LEFT JOIN resplnk AS resplnk_1 ON subacct.lgr_acc = resplnk_1.lgr_acc) INNER
JOIN officers AS officers_1 ON {*1*Left([respstr].[rsp_key],4) =
officers_1.off_num*}
WHERE (((subacct.clo_dte) Is Null) AND ((subacct.pst_yer)=2004) AND
((resplnk_1.lgr_acc) Is Null))
GROUP BY subacct.lgr_acc, subacct.fnd_num, subacct.gen_num, subacct.sub_num,
subacct.acc_dsc, subacct.acc_lvl, subacct.pst_yer, <lots of financial
values>, acckeys.key_dsc, respstr.rsp_stt, respstr.nod_num,
1*Left([respstr].[rsp_key],4), officers_1.pos_dsc, respstr.rsp_off,
officers.pos_dsc, respstr.nod_dsc;



I've {*braced*} the tricky bits...



One other thing I noticed is that when I was building these queries, they
would sometimes not work at first unless I removed and re-added some tables
in different order - seem to change the JOIN sequence. However, if I
remember (it was after midnite!) all the queries are derived from the same
basic query, and they all work independently.



If your help can solve this issue, I will be very grateful...



remove the no-spam. from no-spam.bigpond.com to reply directly...



Geoff