query to pull Top records based of subquery column (1 Viewer)

[victorwill]

I am really really in need of a help.

I have a table and a query, table1 and query1. Query1 has the following
columns and rows:
ID Audit
223 2
234 3
225 1
226 2
The column ID is unique, have one record per row and data in column Audit
are not unique.

Here are the columns and rows for table 1. Records in column ID are unique
but can have more than one record per rows. Amt Type is grouped by Trans
type. Example, cash and visa are credit Trans type; where as, all MC and TC
are paytogo.

ID Amt Amttype TransType
223 1.50 cash credit
223 2.03 visa credit
223 2.30 MC paytogo
234 .50 TC paytogo
234 1.75 cash credit
234 .90 visa credit
234 5.30 visa credit
234 1.25 TC paytogo
225 1.35 MC paytogo
225 2.24 visa credit
225 3.35 MC paytogo
226 2.17 visa credit
226 1.15 TC paytogo
226 1.50 TC paytogo
226 1.60 visa credit

I would like a sql statement or know how to create a query that will look at
column http://www.dbforums.com/#audit of query1 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.

The result for this query should be as shown below. There are 3 records for
ID 234, 1 record for ID 225, 2 records for ID 223 and 2 records for ID 226.
ID Amt Amttype TransType
234 5.30 visa credit
234 1.25 TC paytogo
234 1.75 cash credit
225 3.35 MC paytogo
223 2.30 MC paytogo
223 2.03 visa credit
226 2.17 visa credit
226 1.60 visa credit

Any help will be greatly appreciated as this has been holding be back from
completing this project. I am opened to any direction. I think this is should
query1 should be joined as a subquery of table1.

Thanks a million in advance.
----------------------------------------------------------------------
I had sought help from MS Knowledge base but no luck. Ken Sheridan was able to help but for some reason when i used his SQL on the sample database above it works well but when i used it On production the result is able to correctly
pull about 85% of the result.
For example cases where table1.audit is 5 the result may produce 5 records and others may produce 3 or 4 record. Another example is that cases where table1.audit is 1 some results may correctly pull one records and some will pull no records.
The production data for this example supposed to pull a total of 612 records
but only pulled 503.
This only happens when the records size increases (using the production size
I have mentioned below) .

Here is the sql that was writtern by Ken sheridan:


SELECT T1.*
FROM Table1 As T1
INNER JOIN Query1
ON T1.ID = Query1.ID
WHERE T1.Amt >=
(SELECT MIN(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) <= Query1.Audit);

Thanks in advance.