0

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.

amiroof
  • 31
  • 3
  • That's why MVF are [evil](https://stackoverflow.com/questions/1461582/multivalued-fields-a-good-idea). – Andre Jan 23 '23 at 17:32

2 Answers2

0

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)
Gor Grigoryan
  • 297
  • 1
  • 7
  • i dont understand any of this. can you give more explanation or give me some links to documentations. – amiroof Jan 23 '23 at 13:28
0
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
George Moik
  • 445
  • 4
  • 10
  • im getting The multi-valued field 'PersonalInfo.[k-commission]' cannot be used in an GROUP BY clause. – amiroof Jan 23 '23 at 13:27