Commenting Records by ranked comments (1 Viewer)

Rakesh935 · Oct 4, 2018

Hi Guys,

Need help in writing a SQL query in Ms Access with respect to ranked comments:

Ranked Comments:
Comments Rank
FullName_Tel_Dup 1
FirstName_LastName_Tel_Dup 2
FullName_Fax_Dup 3
FirstName_LastName_Fax_Dup 4

Source Table Data:
ID Comments
1 FullName_Tel_Dup
2 FirstName_LastName_Tel_Dup
3 FirstName_LastName_Tel_Dup
1 FirstName_LastName_Fax_Dup
2 FullName_Fax_Dup
3 FirstName_LastName_Fax_Dup
1 FirstName_LastName_Tel_Dup
2 FirstName_LastName_Fax_Dup
3 FullName_Tel_Dup

And below should be the required output:

ID Comments
1 FullName_Tel_Dup
2 FirstName_LastName_Tel_Dup
3 FullName_Tel_Dup

Request your help for the above requirement and let me know if any further information is required.

Thanks and regards,
Rakesh

plog · Oct 4, 2018

This is going to take a subquery:

Code:

SELECT ID, MAX(Rank) AS HighestRank 
FROM TableData
INNER JOIN RankedComments ON RankedComments.Comments = TableData.Comments
GROUP BY ID

Paste that into a new query object and name it 'sub1'. Then to get your results use this SQL:

Code:

SELECT ID, Comments
FROM sub1
INNER JOIN RankedComments ON HighestRank = Rank

Commenting Records by ranked comments (1 Viewer)

Rakesh935

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread