2 table query problem

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

Guest

I have 2 tables - each of the tables have an account numbers field

Table 1 has most of the accounts with data filed populated
The 2 table has an account that has data but is not in the 1st Table

I cannot get a query of return all accounts with data from both fields , I
have tried joinuing 2 seperate queries into one this has not worked either,
can some please help.

Thanks
danny
 
Danny

Am I right in thinking your tables are not related in any way but you want
to combine records from both? If so, you could do a make table query from
Table 1 - go to query type and change to make table - then an append query
from table 2 which appends records to the new table. This would give you all
the records in 1 new table. Hope this helps

Sheila
 
Hi Sheila

Thanks for your reply, I would like to keep the 2 tables seperate as I
intend to use the contents of the 2 tables else where and they need to be in
seperate tables.

Table 2 is dymamic i.e. users can add recored to this table , the content of
table 1 remains constant.

Danny
 
You might be able to use a UNION query to get the results you want.

SELECT AccountNumber
FROM Table1
UNION
SELECT AccountNumber
FROM Table2

That will return a list of all account numbers that are in the tables. It will
be a unique list of the numbers.
 
Thanks John -

I am trying to get a union query that will list both the account numbers and
the data that is associated with the account number from Q1 and Q2, but the
account some of the account numbers only existrs in Q1.

Account 140010 is in the first q and not in the secons q.

Any would be greatly appricted.
Danny


SELECT [Org details - Legal].CoCode, AJE.CoCd, AJE.Account, AJE.AJETrprt,
Sum(AJE.[Val amt loccurr2 - ETB]) AS [SumOfVal amt loccurr2 - ETB], [SumOfVal
amt loccurr2 - ETB] AS AJE, "" AS SAP
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) AND ((Sum(AJE.[Val amt loccurr2 - ETB]))<>0));

UNION ALL



SELECT [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) AND ((Sum([SAP -
Data].[Val amt loccurr2]))<>0));
 
Danny

You can still keep the 2 tables seperate, but use the make table, append
route when you want to get all records. If you set up a macro which runs both
queries then open the new table you should get the result you want.

Each time you run the macro the data will be recreated based on the
information in your 2 existing tables

Sheila
 
Back
Top