Distinct?

[Guest]

Hi. I am having trouble creating a query that will bring back the results I
want.

I've set up a table that contains:
DuesRateID, EffectiveDate, and MemberCategoryID
5, 1/1/2000, 1
6, 1/5/2006, 1
7, 1/7/2000, 2
10, 1/8/2002, 2
8, 1/1/2000, 3

I'd like the query to return:
6, 1/5/2006, 1
10, 1/8/2002, 2
8, 1/1/2000, 3
So that's the most current DuesRateID for each MemberCategoryID.

Struggling. Here's the short query:
SELECT tblDuesRates.MemberCategoryID, tblDuesRates.DuesRateID,
tblDuesRates.DuesEffective
FROM tblDuesRates
GROUP BY tblDuesRates.MemberCategoryID, tblDuesRates.DuesRateID,
tblDuesRates.DuesEffective;

Any thoughts of how I can limit the return records to the ones I want. Thanks!

 

[Guest]

You used two labels for the same field -- EffectiveDate and DuesEffective.

Use two queries unless you know how to do subqueries.
LatestDuesRate ---
SELECT tblDuesRates.MemberCategoryID, Max(tblDuesRates.EffectiveDate) AS
MaxOfEffectiveDate
FROM tblDuesRates
GROUP BY tblDuesRates.MemberCategoryID;

SELECT tblDuesRates.DuesRateID, tblDuesRates.EffectiveDate,
tblDuesRates.MemberCategoryID
FROM LatestDuesRate INNER JOIN tblDuesRates ON
(LatestDuesRate.MaxOfEffectiveDate = tblDuesRates.EffectiveDate) AND
(LatestDuesRate.MemberCategoryID = tblDuesRates.MemberCategoryID);

 

[Guest]

Karl,
You rock! Thanks for taking the time to rewrite the queries for me. I was
able to use your advice to get just what I needed.
No wonder you are well on your way to being a MVP!
Cheers to you!