Problem Joining Query Results

[RayH]

I have two queries whose results I want to join together in a third.
My problem occurs when one of the queries returns no records so that my third query also returns no records.
I have created some pseudo-queries so that I can demonstrate my problem.

Query One:
select field_a,count(field_a) count_a
from table_a
where field_a=<some_value>
group by field_a

Query Two:
select field_b,count(field_b) count_b
from table_b
where field_b=<some_value>
group by field_b

Query Three:
select field_a,count_a,field_b,count_b
from a,b

<** Note there is no relationship between table_a and table_b ** >

Result:
Query Three will contain no records if EITHER Query One or Query Two
contain no records but works if both do.

example one:
field_a count_a field_b count_b
XXXX 5 YYYY 1


Desired Result:
Query Three to contain one record regardless of the number of records
in either Query One or Query Two.

example two:
field_a count_a field_b count_b
XXXX 5 <null> 0

example three:
field_a count_a field_b count_b
<null> 0 YYYY 1


Is this sort of thing possible in Access?

 

[krunalprajapati]

U can use UNION
for e.g.

select field_a,count_a, null as field_b, null as count_b
from a
union
select null as field_a, null as count_a, field_b, count_b
from b

 

[RayH]

Great that did it.
But, if I may extend the problem a little further.

How would I acheive this:

field_a count_a field_b count_b totcount
XXXX 5 YYYY 1 sum(count_a+count+b)

Edit: Sorry, it didn't work like I said.
This only works if one or the other queries is empty.
If both queries return values then I get two records record where I only want one.

 

[krunalprajapati]

the fields field_a, field_b have same name and same data type.

if so, than it will return one row for the same data of field_a and field_b, otherwise it returns different rows.

or give me the actual filed name and some of example than i will try to solve it

 

[RayH]

Right this is the actual SQL I have at the moment.

SELECT qryWonStatus.BidStatus AS BidWon,
qryWonStatus.WonCount AS WonCount,
qryWonStatus.ValueByType1 AS ValueByType1,
qryWonStatus.AvgMargin1 AS AvgMargin1,
'Lost ' as BidLost,
0 as LostCount,
0 as ValueByType2,
0 as AvgMargin2,
qryWonStatus.WonCount/IIf((NZ(qryLostStatus.LostCount)+NZ(qryWonStatus.WonCount))=0,1,NZ(qryLostStatus.LostCount)+NZ(qryWonStatus.WonCount)) AS WinLoss
FROM qryWonStatus
UNION ALL SELECT 'Won' as BidWon,
0 as WonCount,
0 as ValueByType1,
0 as AvgMargin1,
qryLostStatus.BidStatus AS BidLost,
qryLostStatus.LostCount AS LostCount,
qryLostStatus.ValueByType2 AS ValueByType2,
qryLostStatus.AvgMargin2 AS AvgMargin2,
qryWonStatus.WonCount/IIf((NZ(qryLostStatus.LostCount)+NZ(qryWonStatus.WonCount))=0,1,NZ(qryLostStatus.LostCount)+NZ(qryWonStatus.WonCount)) AS WinLoss
FROM qryLostStatus;

Edit: The WinLoss fields causes an error. This is calc'd field I referred to in an earlier post.