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
softsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

magarityCommented:
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.
0
PatHartmanCommented:
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.
0
softsupportAuthor Commented:
Please explain where to place this in my query.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PatHartmanCommented:
Who are you addressing?  What does your query look like now?
0
softsupportAuthor Commented:
I am sorry Pat.... I was trying your solution.

Please see attached.
query.docx
0
PatHartmanCommented:
Select Max(CenterClaim.RevisionNo), CenterClaim.ClaimDate, CenterClaim.[Center Id]
From CenterClaim
Group By CenterClaim.ClaimDate, CenterClaim.[Center Id];

Save this query as qMaxRevNo.  Open it and look for a claim that you know has multiple revisions.  Did it return the correct revision?

Then open the query you posted and add this query to the grid.  Draw the join lines to connect the three fields.  If it doesn't do what you want, then the problem is with [Center ID], there may be a different column that you need to use.  I can't tell what it might be from the query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
softsupportAuthor Commented:
That should have been simple, but drove me crazy.  Thank you so very much.
0
softsupportAuthor Commented:
I have another question regarding access ID: 28485671.  Can you assist with this as well.  Separate issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.