subquery pulling data based of another column

[Mary]

I am posting this here because i was not able to get help from the query
section..

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 jumpFROM Table1 AS T1 INNER JOIN
table2 ON T1.ID=table2.IDWHERE (((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)));


THE PRODUCTION DATABASE IS UPLOADED HERE:
http://itismystory.com/upload/

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

Ken helped me with this query. Here is the original link.
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

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

 

[Steve Sanford]

Hi Mary,

The problem is the way Access handles ties.

Create a new query and paste in the following SQL:

SELECT table1.ID, table1.amt, table1.Amttype, table1.date
FROM table1
WHERE (((table1.ID)=12))
ORDER BY table1.amt;

Run the query. You will notice that the 2nd and 3rd values are identical.
Open the query "q1" and enter 12 in the criteria for the "ID" column. run
the query. You get 1 record returned.

Now open the table "Table 2" and change the Audit value for ID 12 from 2 to
3. Make sure you save the change. Rerun query "q1". Now you have 3 records,
but 2 records are identical values.

You can do the same for ID 1 in "Table 2", changing the Audit value from 1
to 2.


You need some way to break the "Ties" when the "Amt" values are "identical"
(tied).

That is the "Why". Hopefully this will help you to determine how to fix it.

HTH

 

[Mary]

Thanks for you help. You are right. But how do i break the tie? When i
removed all identical amt for ID 12, it produces the correct records for that
ID.

 

[Steve Sanford]

Thanks for you help. You are right. But how do i break the tie? When i
removed all identical amt for ID 12, it produces the correct records for that
ID.

Your example only had three fields: the amount, the amount type and a date.
You need another field that will be different (unique) to break a tie. I
don't know what other fields are available that might be used.

I did find a post by Neil Sunderland that might help. See

http://groups.google.com/group/micr...f7de842517214?lnk=gst&q=rank#f52f7de842517214


He says: "I've found a way to do it with three saved queries; one to get the
initial ranking (with duplicates), one to rank the duplicates amongst
themselves, and a third to show the final ranking."

See the rest of the post for example SQL.


HTH