Eliminate duplicate values in a query

[Jake]

Hi,
This query extracts records fine, but produces duplicate values in the
Tencount field. The Amndt No field contains values from 0 to 171 that track
amendments. A record can have the same Tencount number but different
Amendment numbers. I would like the query to not produce duplicate records
based on the Tencount field and in the duplicate records only choose to
extract the record with highest amendment number. The user should be able
to extract records less than or equal to a certain amendment number and only
get the highest amendment number for that Tencount. I've tried using a Max
function on the ammendment number and setting the query to Unique Values or
Unique Records without success. Here is the query I'm using:

SELECT DISTINCTROW tblFARZeroSectPlus.strFAR25para, tblFARZeroSectPlus.[Sect
No], tblFARZeroSectPlus.Tencount, tblFAR25AmndtListSections.Subject,
tblFAR25AmndtListSections.Statement, tblFAR25AmndtListSections.[Amndt No],
tblFAR25AmndtListSections.Decade
FROM tblFARZeroSectPlus INNER JOIN tblFAR25AmndtListSections ON
tblFARZeroSectPlus.[Sect No] = tblFAR25AmndtListSections.[Sect No]
WHERE (((tblFAR25AmndtListSections.[Amndt No])<=50))
ORDER BY tblFARZeroSectPlus.Tencount, tblFAR25AmndtListSections.[Amndt No]
DESC , tblFAR25AmndtListSections.Decade DESC;

It's arbitrarily set to extract amendments less than or equal to 5.
Thanks for any help,
Cheers!
Jake

 

[Michel Walsh]

Hi.


Take a look at http://www.mvps.org/access/queries/qry0020.htm which is about
finding, in a library, who is the last one to have borrow each books. Quite
similar to your case, isn't it? Four methods are presented, pick one that
sounds appropriate to your context.

Hoping it may help,
Vanderghast, Access MVP

 

[David Lloyd]

Jake:

One approach is to use a two step approach. First, create a query grouping
on the TenCount field and taking the MAX of the Amndt No field. This will
give you a list of distinct TenCounts and their matching maximum Amndt No.

Step two would be to take the results of step one and join this against your
current query on the TenCount and Amndt No fields to get all of the data you
need.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi,
This query extracts records fine, but produces duplicate values in the
Tencount field. The Amndt No field contains values from 0 to 171 that track
amendments. A record can have the same Tencount number but different
Amendment numbers. I would like the query to not produce duplicate records
based on the Tencount field and in the duplicate records only choose to
extract the record with highest amendment number. The user should be able
to extract records less than or equal to a certain amendment number and only
get the highest amendment number for that Tencount. I've tried using a Max
function on the ammendment number and setting the query to Unique Values or
Unique Records without success. Here is the query I'm using:

SELECT DISTINCTROW tblFARZeroSectPlus.strFAR25para, tblFARZeroSectPlus.[Sect
No], tblFARZeroSectPlus.Tencount, tblFAR25AmndtListSections.Subject,
tblFAR25AmndtListSections.Statement, tblFAR25AmndtListSections.[Amndt No],
tblFAR25AmndtListSections.Decade
FROM tblFARZeroSectPlus INNER JOIN tblFAR25AmndtListSections ON
tblFARZeroSectPlus.[Sect No] = tblFAR25AmndtListSections.[Sect No]
WHERE (((tblFAR25AmndtListSections.[Amndt No])<=50))
ORDER BY tblFARZeroSectPlus.Tencount, tblFAR25AmndtListSections.[Amndt No]
DESC , tblFAR25AmndtListSections.Decade DESC;

It's arbitrarily set to extract amendments less than or equal to 5.
Thanks for any help,
Cheers!
Jake