How do I add to Union Query

[MNNovice]

A union query named qryUnionGrant has this in the SQL

SELECT ECHOID, GrantNo, FundNo, AcctNo,SubClassNo, ProjectNo, Amount,"Allocation" as Category
FROM qryAllocDetail
 
UNION SELECT ECHOID, GrantNo, FundNo, AcctNo,SubClassNo, ProjectNo, PayAmount, "Payroll" as Category
FROM qryPayDetail;
 
 
UNION SELECT ECHOID,GrantNo, FundNo, AcctNo,SubClassNo, ProjectNo, NetAmount, "AP" as Category
FROM qryApDetail;

I need to add qryEcho to this. But qryEcho does not have similar fields. All I am trying to do is to pull the EchoNo and NOT ECHOID from qryEcho. My goal is list each grant total amount by fund, Sub class, Project and Echo Number.

Any guidance from anyone?

[Stewart Ross]

Hi. The field names in the union query do not have to be the same, so there is no reason why you cannot union what you have got with a

SELECT EchoNo, ...., FROM qryEcho;

You must, however, union the same number of fields - and they should be of the same type or one which is compatible with the final type (e.g. if the third field is a date type in one SELECT statement it should be a Date type in them all).

I suspect that you do not need to use a Union query at all, and that what you really need is a join of some kind in a standard query. I can't comment further on this as you have not told us what the additional query does or how it relates to the unioned ones you list.

-Stewart

[MNNovice]

Stewart:

I solved it. I simply replaced Select ECHOID with Select EchoNo in the Union Query - and it worked. Now onto my report. Thanks.