Re: distinct to exclude fields from same table?

[Oliver Peters]

I meant

compare the results of

strSQL = "SELECT t.PLZ, t.Ort, " & _
"t.Strasse, t.Hausnummer, t.Nachname, t.Vorname, " & _
"t.Vertragsart, t.Versorgung, t.aktive_WE, t.max_WE, " & _
"t.letzter_Wechse, t.Kuendigungsgr, t.Telefonie, t.FSA, " & _
"t.HSI, t.uep_zustand, t.Agentur, " & _
"t.BEMERKUNG, t.[AUSGEFÜHRTE ARBEIT] " & _
"FROM [Kopie von Objekt_Daten_Steger] AS t "

and

strSQL = "SELECT DISTINCT t.PLZ, t.Ort, " & _
"t.Strasse, t.Hausnummer, t.Nachname, t.Vorname, " & _
"t.Vertragsart, t.Versorgung, t.aktive_WE, t.max_WE, " & _
"t.letzter_Wechse, t.Kuendigungsgr, t.Telefonie, t.FSA, " & _
"t.HSI, t.uep_zustand, t.Agentur, " & _
"t.BEMERKUNG, t.[AUSGEFÜHRTE ARBEIT] " & _
"FROM [Kopie von Objekt_Daten_Steger] AS t "


DISTINCT only works because there is no difference between the records "it makes invisible" and the one that is left.

But this doen't solve your problem - if I understand correct you have cases where every field content except Object_Nummer and Kundennum can be multiple times in your table and you can't eliminate duplicates because if you include Object_Nummer and Kundennum the records are unique (think about it I had the same problem and needed my time to understand).

The solution in theory is very: simple - take a decision: what should be the unambigious combination of your 2 fields for every record?

But my recommendation is: think again - is this really what you want? Maybe first of all you have a building (ojectno & address) and this building consists of appartements (subobjectnos & clients) or not (subobjectno & client) and related are the fields about the work that is done on specific dates. There is no redundance; you' re only thinking in the false direction. Maybe the only problem is that you don't like how the table looks like? Than you should try to develop a report (maybe with ACCESS)

good night
oliver