Access query finding the duplicates without using DISTINCT-CodePudding

i have this query


SELECT PersonalInfo.id, PersonalInfo.[k-commission], Abs(Not IsNull([PersonalInfo]![k-commission].[Value])) AS CommissionAbsent
FROM PersonalInfo;

and the PersonalInfo.k-commission is a multi value field. the CommissionAbsent shows duplicate values for each k-commission value. when i use DISTINCT i get an error saying that the keyword cannot be used with a multi value field.

now i want to remove the duplicates and show only one result for each. i tried using a WHERE but i dont know how.

edit: i have a lot more columnes and in the example i only showed the few i need.

CodePudding user response：

You can use GROUP BY and COUNT to solve your problem, here is an example for it

SELECT clmn1, clmn2, COUNT(*) as count
FROM table
GROUP BY clmn1, clmn2
HAVING COUNT(*) > 1;

the query groups the rows in the table by the clmn1 and clmn2 columns, and counts the number of occurrences of each group. The HAVING clause is then used to filter the groups and only return the groups that have a count greater than 1, which indicates duplicates.

If you want to select all, then you can do like this

SELECT *
FROM table
WHERE (clmn1, clmn2) IN (SELECT clmn1, clmn2
    FROM table
    GROUP BY clmn1, clmn2
    HAVING COUNT(*) > 1)

CodePudding user response：

SELECT PersonalInfo.id, PersonalInfo.[k-commission], Abs(Not IsNull([PersonalInfo]![k-commission].[Value])) AS CommissionAbsent
FROM PersonalInfo
GROUP BY PersonalInfo.id, PersonalInfo.[k-commission], Abs(Not IsNull([PersonalInfo]![k-commission].[Value]))
HAVING COUNT(*) > 1