[AccessD] Basic Question

This provoked a distant cob-webbed memory ...

I recall running into this problem -- it seems the first row in a union
query is treated as a 'header' row, so if the first query returns no
results, the union query gets foobared.

I fixed this by making a local table, tblUnionQueryHeader, with one column
HeaderColumn (integer). This table must contain one row -- give it a value
of 1.

Modify your union query to read:

select [HeaderColumn], "" As [WBS1], 0 as [BeginFee], 0 as [EndFee]
from [tblUnionQueryHeader]

UNION select 0 as [HeaderColumn], [WBS1], [SumOfFeeBillingCurrency] as
[BeginFee], 0 as [EndFee]
from [qryReportingBacklogFeeBegin]

UNION select 0 as [HeaderColumn], [WBS1], 0 as [BeginFee],
[SumOfFeeBillingCurrency] as [EndFee]
from [qryReportingBacklogFeeEnd];

This way you always get one good header row, with values for each column
that the union query will return. Use default values appropriate for each
column's datatype (I'm guessing WBS1 is a string).

In the parent query, you can simply add HeaderColumn=0 to your where clause
to exclude the header tow.

-Ken


>
> Keith Williamson wrote:
>
>> Hey gurus,
>>
>> I have a basic question for you.  Quite a few times, in the past, I've
>> used Union Join queries to join different queries into common data.  For
>> instance:
>>
>> select [WBS1], [SumOfFeeBillingCurrency] as [BeginFee], 0 as [EndFee]
>> from [qryReportingBacklogFeeBegin]
>>
>> UNION select [WBS1], 0 as [BeginFee], [SumOfFeeBillingCurrency] as
>> [EndFee] from [qryReportingBacklogFeeEnd];
>>
>> Recently I was testing one.....and discovered that my data result was
>> not correct.  I was thinking that with the union join, if one dataset
>> did not have matching data, to the other...I would only get one line of
>> data.  Otherwise, I should get two lines....which I then summarized in
>> another query of this union join.  However, I found that I only got data
>> from the datasets, where the select occurred in BOTH sets...([WBS1], in
>> the above scenario.)  Is that right??
>> Keith Williamson | Associate, Asst. Controller | kwilliamson at rtkl.com
>> RTKL Associates Inc. | 901 South Bond St. | Baltimore, MD  21231
>>
>> 410.537.6098 Direct | 410.276.4232 Fax | www.rtkl.com
>>
>