Subreport with union query

[Sharon M via AccessMonster.com]

I have the following union query:

SELECT qryB.*, qryA].*
FROM [qryB] left join [qryA]
ON qryB.Act_No = qryA.Act_No
UNION SELECT qryB.*, qryA.*
FROM qryA left join qryB]
ON qryA.Act_No = qryB.Act_No;

This returns two colums with qry_A.Act_No and qry_B.Act_No.
Some qry_A.Act_No and qry_B.Act_No fields are null, but what i would like to
do is merge these fields to a new column called Act_No, so that i can dump a
subreport into a report by using the Act_No.

the problem i'm having so far is that i can only use one column or the other
to define the subreport, even if i define my own relations the child link can
only have one reference.
can anyone help?

 

[Marshall Barton]

I have the following union query:

SELECT qryB.*, qryA].*
FROM [qryB] left join [qryA]
ON qryB.Act_No = qryA.Act_No
UNION SELECT qryB.*, qryA.*
FROM qryA left join qryB]
ON qryA.Act_No = qryB.Act_No;

This returns two colums with qry_A.Act_No and qry_B.Act_No.
Some qry_A.Act_No and qry_B.Act_No fields are null, but what i would like to
do is merge these fields to a new column called Act_No, so that i can dump a
subreport into a report by using the Act_No.

You should list out the fields instead of using *
That way, you can select the AcctNo field that exists in
each of the Select statements:

SELECT qryB.Acct_No, qryB.fld1, . . ., qryA.flda, . . .
FROM qryB left join qryA
ON qryB.Act_No = qryA.Act_No
UNION
SELECT qryA.Acct_No, qryB.fld1, . . ., qryA.flda, . . .
FROM qryA left join qryB
ON qryA.Act_No = qryB.Act_No

 

[John Spencer (MVP)]

You might try using NZ(QryB.Act_No,QryA.Act_No) to return a value.

SELECT qryB.*, qryA].*,
NZ(QryB.Act_No,QryA.Act_No) as CombinedAccountNum
FROM [qryB] left join [qryA]
ON qryB.Act_No = qryA.Act_No
UNION SELECT qryB.*, qryA.*,
NZ(QryB.Act_No,QryA.Act_No)
FROM qryA left join [qryB]

 

[Sharon M via AccessMonster.com]

That worked brilliantly, thanx guys

You might try using NZ(QryB.Act_No,QryA.Act_No) to return a value.

SELECT qryB.*, qryA].*,
NZ(QryB.Act_No,QryA.Act_No) as CombinedAccountNum
FROM [qryB] left join [qryA]
ON qryB.Act_No = qryA.Act_No
UNION SELECT qryB.*, qryA.*,
NZ(QryB.Act_No,QryA.Act_No)
FROM qryA left join [qryB]

I have the following union query:

[quoted text clipped - 14 lines]

only have one reference.
can anyone help?