Make a table query with no duplicates

J

Jeannie

I am making a table query however, I get duplicate records, the end of the
table, the field Dossier, has a different one for each IDContribuable,
however, the IDContribuable field has been indexed with no duplicate, but
they still show up in my make table query. I have already set the Unique
Values to Yes and the Output fields to All. Here is my SQL statement for the
make table query:
SELECT DISTINCT tblContribuable.IdContribuable, tblContribuable.Langue,
tblContribuable.Titre, tblContribuable.Prénom, tblContribuable.Nom,
tblContribuable.AdresseCivique, tblContribuable.Ville,
tblContribuable.Province, tblContribuable.Pays, tblContribuable.CodePostal,
tblContribuable.Téléphone, tblContribuable.Courriel,
tblAdministration.NoDossier, tblContribuable.RetraitDossier,
tblAdministration.SoldeFederal, tblAdministration.SoldeProvincial,
tblContribuable.SoldeDû, tblProjetDossierCour.Projet, * INTO TBLcontribuableEN
FROM (((tblContribuable INNER JOIN tblAdministration ON
tblContribuable.IdContribuable = tblAdministration.IdContribuable) INNER JOIN
tblHonoraires ON tblAdministration.IdContribuable =
tblHonoraires.IdContribuable) INNER JOIN tblProjetDossierCour ON
tblAdministration.IdContribuable = tblProjetDossierCour.IdContribuable) INNER
JOIN QRYprojet ON tblAdministration.IdContribuable = QRYprojet.IdContribuable
WHERE (((tblProjetDossierCour.NoCour)<>"À venir(2)" Or
(tblProjetDossierCour.NoCour)<>"À venir" Or
(tblProjetDossierCour.NoCour)<>"n/a"))
GROUP BY tblContribuable.IdContribuable, tblContribuable.Langue,
tblContribuable.Titre, tblContribuable.Prénom, tblContribuable.Nom,
tblContribuable.AdresseCivique, tblContribuable.Ville,
tblContribuable.Province, tblContribuable.Pays, tblContribuable.CodePostal,
tblContribuable.Téléphone, tblContribuable.Courriel,
tblAdministration.NoDossier, tblContribuable.RetraitDossier,
tblAdministration.SoldeFederal, tblAdministration.SoldeProvincial,
tblContribuable.SoldeDû, tblProjetDossierCour.Projet
HAVING (((tblContribuable.Langue)="anglais") AND
((tblContribuable.RetraitDossier)=False) AND ((tblProjetDossierCour.Projet)
Like "*sret*" And (tblProjetDossierCour.Projet) Like "*minirobot*" And
(tblProjetDossierCour.Projet) Like "*ecolo*" And
(tblProjetDossierCour.Projet) Like "*ecologia*" And
(tblProjetDossierCour.Projet) Like "*ect*"));

Is there any way I can tell it that I only want one occurrence of each
IDContribuable?

Thanks for your time
 
J

John Spencer (MVP)

Instead of Group By, for all the fields EXCEPT tblContribuable.IdContribuable,
use FIRST. Also, drop the ALL fields output - to tell the truth I am surprised
that didn't give you an error message.

Also for efficiency I would move the having clause into the where clause,
although it should work if it remains as a Having clause.

SELECT tblContribuable.IdContribuable, First(tblContribuable.Langue) as fLangue,
First(tblContribuable.Titre) as fTitre, ...
INTO TBLcontribuableEN
FROM (((tblContribuable INNER JOIN tblAdministration ON
tblContribuable.IdContribuable = tblAdministration.IdContribuable) INNER JOIN
tblHonoraires ON tblAdministration.IdContribuable =
tblHonoraires.IdContribuable) INNER JOIN tblProjetDossierCour ON
tblAdministration.IdContribuable = tblProjetDossierCour.IdContribuable) INNER
JOIN QRYprojet ON tblAdministration.IdContribuable = QRYprojet.IdContribuable
WHERE (((tblProjetDossierCour.NoCour)<>"À venir(2)" Or
(tblProjetDossierCour.NoCour)<>"À venir" Or
(tblProjetDossierCour.NoCour)<>"n/a"))
GROUP BY tblContribuable.IdContribuable,
HAVING (((tblContribuable.Langue)="anglais") AND
((tblContribuable.RetraitDossier)=False) AND
((tblProjetDossierCour.Projet)Like "*sret*" And
(tblProjetDossierCour.Projet) Like "*minirobot*" And
(tblProjetDossierCour.Projet) Like "*ecolo*" And
(tblProjetDossierCour.Projet) Like "*ecologia*" And
(tblProjetDossierCour.Projet) Like "*ect*"));
 
Top