union q using 2 quries from 2 seperate tables

D

Danny

Can some please help

I have a union query, which is made up from the sql from 2 seperate queries
from 2 seperate tables.

Both the tables have a field called "Account No"

When I run the union (Union all) query the result do not give all the
account numbers in both of the original queries - and yet when the original
query is run seperately they do give the desired results.

Thanks
Danny
 
J

Jeff Boyce

Danny

It would probably help if we could see the SQL statements for all three...

Jeff Boyce
<Access MVP>
 
D

Danny

Hi Jeff

1st Q is

SELECT DISTINCTROW [Org details - Legal].CoCode, AJE.CoCd, AJE.Account,
AJE.AJETrprt, Sum(AJE.[Val amt loccurr2 - ETB]) AS [SumOfVal amt loccurr2 -
ETB]
FROM AJE LEFT JOIN [Org details - Legal] ON AJE.CoCd = [Org details -
Legal].[Sub CoCode]
GROUP BY [Org details - Legal].CoCode, AJE.CoCd, AJE.Account, AJE.AJETrprt
HAVING (((AJE.Account)=140010 Or (AJE.Account)=190990 Or
(AJE.Account)=140020 Or (AJE.Account)=210010 Or (AJE.Account)=241990 Or
(AJE.Account)=210020));


2nd Q is

SELECT DISTINCTROW [Org details - Legal].CoCode, [SAP - Data].CoCd, [SAP -
Data].Account, [SAP - Data].Trprt, Sum([SAP - Data].[Val amt loccurr2]) AS
[SumOfVal amt loccurr2], [SumOfVal amt loccurr2] AS sap, " " AS aje
FROM [SAP - Data] LEFT JOIN [Org details - Legal] ON [SAP - Data].CoCd =
[Org details - Legal].[Sub CoCode]
GROUP BY [Org details - Legal].CoCode, [SAP - Data].CoCd, [SAP -
Data].Account, [SAP - Data].Trprt, " "
HAVING ((([SAP - Data].Account)=140010 Or ([SAP - Data].Account)=190990 Or
([SAP - Data].Account)=140020 Or ([SAP - Data].Account)=210010 Or ([SAP -
Data].Account)=241990 Or ([SAP - Data].Account)=210020));


data for account 140010 is in q 1 but nt in q2 nor is the account in q2

Just to recap - I have 2 tables with the same design grid - table one is
standing dat table 2 is used for adjusting items to the original data in
table 1 , but needs to be kept seperate for reporting purposes. All I am
trying to acheive is to "append" the data from both tables regardless if data
is in only 1 or both tables.

Thanks Danny
 
J

Jeff Boyce

Danny

For a UNION query to work, you need the same number of "fields" in each SQL
statement. You have 4 in the first and 6 in the second. Either leave the
extra two out of the second, or add two "dummy" fields to the first.

Good luck

Jeff Boyce
<Access MVP>
 
Top