CODEWITHSUNDEEP
ms-access
Dave 2.71828
Dave 2.71828

Reputation: 115

MS-Access 2007 query with conditions on two fields

I have a dataset with unique records that includes 2 fields: RName and RVer. A subset looks like this:

RName, RVer
Joe,   2 
Joe,   3
Joe,   4
Joe,   5
Bill,  1
Bill,  5

I want to query on a Version number "MaxVer", returning each instance of a RName whose RVer is the greatest one, less than or equal to MaxVer. If a RName has no RVer less than or equal to MaxVer, then that RName is not returned at all.

Here are examples of the query and the desired return:

MaxVer  ---> Returns --->   Dataset Returned
1                           Bill,1
2                           Bill,1  and  Joe,2
3                           Bill,1  and  Joe,3
4                           Bill,1  and  Joe,4
5                           Bill,5  and  Joe,5
6                           Bill,5  and  Joe,5

This is the closest I've come so far, but it's not very close at all:

SELECT DISTINCT MainTbl.RName, MainTbl.RVer
FROM MainTbl
WHERE MainTbl.RVer <= MaxVer;

Using MaxVer=4 as a test case, this SQL query returns 4 records:

Bill,1   and   Joe,2   and   Joe,3   and Joe,4

If someone would help me out here, I'd surely appreciate it!

Thanks, Dave

Upvotes: 1

Views: 59

Answers (1)

Fadi
Fadi

Reputation: 3322

Try:

SELECT MainTbl.RName, Max(MainTbl.RVer) AS MaxRVer
FROM MainTbl
WHERE (MainTbl.RVer)<=[MaxVer]
GROUP BY MainTbl.RName;

You can test it Here.

Upvotes: 1

Related Questions