Select distinct

[HV_L]

Hi all,
I can't get my Query right. :banghead:
I only want unique address records. With this query I still get the same address multiple times.

SELECT DISTINCT ([Klanten.Adres] & " " & [Klanten.Huisnr]) AS Adres1, Klanten.Voorl, Klanten.Naam, Klanten.Postcode, Klanten.Woonplaats
FROM Klanten;

What is wrong?
Thanks

 

[StarGrabber]

I cannot find any fault in your SQL statement. Did you check your table for duplicates?

 

[JHB]

If you only want the address, then take away all the fields there is not address, like names (Naam).

I only want unique address records. With this query I still get the same address multiple times.

Else show some sample data and what you like to get..

 

[HV_L]

Oke some sample data and desired result. Hope it makes sense

Klnr|Voorl|Tussenvoegsel|Naam|Adres|Huisnr|Postcode|Woonplaats
012|M.|van|Geest|Pentur|12|1166 PW|City B
013|M.F.||Rosheuvel|Wesselstraat|123|1611 RM|City A
014|A.||Giesen|Distelstraat|56|1317 LP|City A
014|K.||Giesen-Soest|Distelstraat|56|1317 LP|City A
015|M.A.||Asruf|P. Brandypad|15|1514 NL|City A
015|C.N.||Bunsee|P. Brandypad|15|1514 NL|City A

Desired result:
012|M.|van|Geest|Pentur|12|1166 PW|City B
013|M.F.||Rosheuvel|Wesselstraat|123|1611 RM|City A
014|A.||Giesen|Distelstraat|56|1317 LP|City A
015|M.A.||Asruf|P. Brandypad|15|1514 NL|City A

 

[StarGrabber]

Create a new table (e.g. Klnrs) with the field Klnr as the primary key. Create a relationship to table Klanten via that field. Then change your query to:

SELECT Klnrs.Klnr, First(Klanten.Voorl) AS FirstValueOfVoorl, First(Klanten.Tussenvoegsel) AS FirstValueOfTussenvoegsel, First(Klanten.Naam) AS FirstValueOfNaam, First(Klanten.Adres) AS FirstValueOfAdres, First(Klanten.Huisnr) AS FirstValueOfHuisnr, First(Klanten.Woonplaats) AS FirstValueOfWoonplaats
FROM Klnrs INNER JOIN Klanten ON Klnrs.Klnr = Klanten.Klnr
GROUP BY Klnrs.Klnr;

 

[HV_L]

Isn't there anither way?
I'm not to happy about creating another table, how would I keep this up to date with the Klanten table?
And which fields should be in the Klnrs table?
Thanks for support!

 

[StarGrabber]

What exactly is your field "Klnr"?

 

[HV_L]

Just a Clientnumber.
For example a husband and wife get the same number.
I'm creating a directory, named with this number where the documents are stored.
Still, every record has its own KLID, which is the PK.

 

[StarGrabber]

Ah, understood.

If you want to have more than one record in Klanten related to a directory then I think using a separate table (e.g. tblDirectories) would be the best. The number (or name) of the directory is the foreign key in Klanten.

In the form you use to add new clients you should put a combobox wich offers the directory numbers. Creating a new number you can evaluate the "...NotInList" event to add it.

I hope this makes sense to you.

 

[JHB]

The below query will give you what you are looking for:

SELECT DISTINCT Klanten.KlNr, First(Klanten.Voorl) AS Voorl, Klanten.Tussenvoegsel, First(Klanten.Naam) AS Naam, Klanten.Adres, Klanten.Huisnr, Klanten.Postcode, Klanten.Woonplaats
FROM Klanten
GROUP BY Klanten.KlNr, Klanten.Tussenvoegsel, Klanten.Adres, Klanten.Huisnr, Klanten.Postcode, Klanten.Woonplaats;

 

[HV_L]

Hi JHB,
Thanks for your response, but when I run the query to the real table, I still get (altough less then before) duplicate fields for the Adres en Huisnr fields.
So we're almost there, I guess..
The combined data of Adres & huisnr need to appear once, no matter what the other fields (naam etc) contain.
Hope it makes sense..

 

[JHB]

Ok - then show some data where you get more as one after you have run the query.
And I would also like to see the same "raw" table data.

 

[HV_L]

Ok I attached a slighly modified db.
Still enough data in for testing purposes.
Thanks for your time and patience

 

[JHB]

Puh - I can't see the duplicate records, (point them out for me)!

 

[HV_L]

This is the query result you posted earlier??
When I look in the table, Klnr 10, 14 and 15 appear twice (same adres and huisnr) and need to be filtered as this picture shows..
Weird..
Can you post the db back or the query you used here?
Maybe I did something wrong..

 

[JHB]

I only run the query "Kopie van Adressen" in you database and it gave the result. I didn't change anything.
I send the database back to you.
Sorry about the pic, it is removed now.

 

[HV_L]

I feel terrible, but when I run the query on the complete table, I stil see addresses appear twice, have no clue why...
Maybe another approach: Klnr should only appear once
I will edit more data in the table and repost it if that is oke with you.
Will test run it before posting
Thanks

Edit: Attached db with duplicates

 

[StarGrabber]

And what about this:

SELECT DISTINCT digid.KlNr, First(digid_1.Voorl) AS Voorl, First(digid_1.Tussenvoegsel) AS Tussenvoegsel, First(digid_1.Naam) AS Naam, First(digid_1.Adres) AS Adres, First(digid_1.Huisnr) AS Huisnr, First(digid_1.Postcode) AS Postcode, First(digid_1.Woonplaats) AS Woonplaats
FROM digid INNER JOIN digid AS digid_1 ON digid.Klnr = digid_1.Klnr
GROUP BY digid.KlNr
ORDER BY digid.KlNr;

 

[HV_L]

It looks like this does the trick!
Thanks man !!

 

[StarGrabber]

You were right, there was another way. - So I hope you feel better now.