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...:
Code:
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)