January 12, 2006 at 8:53 am
I have a table in access that I want to append to a table in SQL. I know that I have some duplicate records in access that are keys in SQL. I want to load the new records and kick out the dup's. does anyone have any suggestions or sample code?
January 13, 2006 at 2:59 am
In the source query that you use get the data from access you could do a distinct across all coumns.
Thanks Jeet
January 13, 2006 at 6:41 am
Jeet - that only works if the entire row is distinct, I suspect that it may be just the key that has the duplicates.
Simplest way I know to fix this is to use GROUP BY, and group by the Key fields. But you have to use an aggregate function on each of the other fields IE:
SELECT a.KeyField1, a.KeyField2, MAX(a.DataField1), MIN(a.DataField1)
FROM a
GROUP BY a.KeyField1, a.KeyField2
In this case, where duplicate rows exist, you will get a single row back with the maximum value from a.DataField1 and minimum value from a.DataField2 out of the duplicates.
SUM, AVG and STDDEV are other useful aggregates (assuming you have numerical data). The useful thing about MAX and MIN is they work on chars & dates as well as numeric data.
(Marvin)
January 13, 2006 at 7:19 pm
Hola,
Lo que debes hacer es una sentencia SELECT DISTINCT en tu origen de datos, osea al momento de seleccionar la fuente de los datos (que seria un archivo mdb) posteriormente en lugar de seleccionar por objetos debes escoger la segunda opcion que es por sentencias.
Ahora otra forma es que por access elimines los duplicados previamente, para esto el access cuenta con sus propias herramientas.
Saludos,
Hans Arancibia
DBA - IT Manager
BitMinds.com
(escrito a proposito en castellano, ahora les toca traducir a ustedes ... Viva el español!)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply