asked on

How in access 2007 can I hide or exclude a row of data in my query

I designed a query to list data for a client for each month [claimdate]. A client may have multiple rows of data for the same [claimdate], however, I only want to list the [claimdate] with the largest revision number.

I have attached a spreadsheet depicting the information listed above. From my query, I highlighted two rows of data, with the same [claimdate] (10-31-2013). I only want to display or list the [claimdate] with the newest/greatest [RevisionNo].... in this example would be 1, however, client can have several revisions (2, 3, etc. )

FYI, a client can have multiple months with duplicate [claimdate] not shown in this example.
Your assistance is always greatly appreciated.
Query-Exclusion.xlsx

magarity

Unfortunately Access does not have a built in RANK function but your query is simple enough to do it yourself like this:

First, make your own ranking query
SELECT CLAIMDATE, REVISIONNO, (SELECT COUNT(*) FROM table B WHERE A.CLAIMDATE = B.CLAIMDATE AND B.REVISIONNO > A.REVISIONNO) AS REV_RANK FROM table A

From that, you can select where REV_RANK is 1. I may have assumed incorrectly somewhere in the details of my example query, but this should get you going in the right direction.

PatHartman

Although you can do this with a sub query, I prefer separate queries that I join since that gives me the ability to test them independently.
qry1 - Select ClientID, ClaimDT, Max(RevisionNo)
From YourTable
Group By ClientID, ClaimDT;

Second query join qry1 to the query you are currently using to limit the resultset to only the most recent revision for each claim.

softsupport

ASKER

Please explain where to place this in my query.

PatHartman

Who are you addressing? What does your query look like now?

softsupport

ASKER

I am sorry Pat.... I was trying your solution.

Please see attached.
query.docx

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.