union q using 2 quries from 2 seperate tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Danny

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

Jeff Boyce
<Access MVP>
 
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
 
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>
 
Back
Top