subquery pulling data based of another column

[mary1994]

The query below works correctly on a sample database with fewer records but on the production database it is skipping some of the records.
Here is my query:


SELECT T1.* INTO jump
FROM Table1 AS T1 INNER JOIN table2 ON T1.ID=table2.ID
WHERE (((T1.amt)<=(SELECT Max(Amt) FROM Table1 As T2 WHERE T2.ID = T1.ID AND (SELECT COUNT(*) FROM Table1 AS T3 WHERE T3.ID = T2.ID AND T3.Amt <= T2.Amt) <= table2.Audit)));

Someone here has tried to help me but it was writing for sql application and will not execute on the access sql view.

http://www.tek-tips.com/viewthread.cfm?qid=1523311

I have uploaded a close sample of the production database. As you will see when you run the make table. Record for ID #1 is missing even though there is one audit record for ID#1. The result should have able 621 but it only produce 527 records.
Here is the link for that database

http://itismystory.com/upload/

Any help will be greatly appreciated as this part the assignment is causing me not to finish this project.

Thanks a million

ALSO you can read this link from this site

 

[jedraw]

What exactly are you trying to do?

 

[mary1994]

Disregard. I will try to do something using Teradata