Joining two tables

[rspai]

There are two tables: GLEntries and RepaymentSchedule
Each of them has the same structure: Account and Amount


However, the GL Entries table has less rows than the RepaymentSchedule table.


When the following query is run it returns 157 rows:


Select GLEntries.Account, GLEntries.Amount, RepaymentSchedule.Amount
from GLEntries RIGHT JOIN RepaymentSchedule
ON GLEntries.Account = RepaymentSchedule.Account
where RepaymentSchedule.Amount > GLEntries.Amount


I tried changing "RIGHT JOIN" to "INNER JOIN" AND "LEFT JOIN". The other queries too return 157 rows.


How should be the query be reworded to get all rows from he RepaymentSchedule table where there are no equivalent Accounts in the GL Entries table?


Thanks in advance for the help.

[jzwp11]

How should be the query be reworded to get all rows from he RepaymentSchedule table where there are no equivalent Accounts in the GL Entries table?

You will actually need to use the "Find Unmatched Query Wizard" to create the query you describe.

It will look like this:

SELECT RepaymentSchedule.Account, RepaymentSchedule.Amount
FROM RepaymentSchedule LEFT JOIN GLEntries ON RepaymentSchedule.[Account] = GLEntries.[Account]
WHERE (((GLEntries.Account) Is Null));

But, since both tables have the same structure, by normalization rules, you should have only 1 table. You can add a field to designated a GL entry or a repayment entry.

[ssanfu]

The SQL looks right to me. Try removing the WHERE clause to see if all records from the RepaymentSchedule table are returned. Maybe add an ORDER BY clause on Account number

Select GLEntries.Account, GLEntries.Amount, RepaymentSchedule.Amount 
from GLEntries  RIGHT JOIN RepaymentSchedule 
ON GLEntries.Account = RepaymentSchedule.Account
ORDER BY RepaymentSchedule.Account

[Robeen]

Try this:

Select * From RepaymentSchedule Where NOT EXISTS
(Select GLEntries.Account 
From GLEntries
Where GLEntries.Account = RepaymentSchedule.Account);

[rspai]

Maybe I have not stated the issue correctly in my post above. Although the structures of the two tables are the same, the amounts in the amount column vary. ("where RepaymentSchedule.Amount > GLEntries.Amount")

Hence the query results should return all accounts where the amount in the RepaymentSchedule table are greater than than the amounts in the GLEntries table
AND
all accounts in the RepaymentSchedule table which are not present in the GLEntries table

[ssanfu]

In your first post, you said:

However, the GL Entries table has less rows than the RepaymentSchedule table.

When the following query is run it returns 157 rows:

So how many records did you expect?
How many records are in GL Entries table?
How many records are in the RepaymentSchedule table?

I read your post one way. jzwp11 read it another. And Robeen read it still another way.

Hence the query results should return all accounts where the amount in the RepaymentSchedule table are greater than than the amounts in the GLEntries table
AND
all accounts in the RepaymentSchedule table which are not present in the GLEntries table

Not sure it can be done in one query. It might have to be a union query which is not updatable. What is this to be used for? If the record source for a report, a union query would be acceptable. Not so much for a form...

You might try the following... it is entirely a WAG!!! Probably won't give the correct results, but...:

Select GLEntries.Account, GLEntries.Amount, RepaymentSchedule.Amount 
from GLEntries  RIGHT JOIN RepaymentSchedule 
ON GLEntries.Account = RepaymentSchedule.Account 
WHERE (RepaymentSchedule.Amount > GLEntries.Amount) OR ((GLEntries.Account) IS NULL)

[rspai]

Thanks a ton, Steve.

That was the query I was looking for. I tweaked it it a bit to get the Account numbers from the RepaymentSchedule table. (Earlier, it was returning blanks for account numbers because of "GLEntries.Account"

Select RepaymentSchedule.Account, GLEntries.Amount, RepaymentSchedule.Amount
from GLEntries RIGHT JOIN RepaymentSchedule
ON GLEntries.Account = RepaymentSchedule.Account
WHERE (RepaymentSchedule.Amount > GLEntries.Amount) OR ((GLEntries.Account) IS NULL)

The query is used for a report.

[ssanfu]

Wonderful