conditional dedupe in Access 2000

[not_an_exit]

Hello there,

Does anybody know how to dedupe in Access 2000 with a condition
attached? I am aware that you can use an append query after copying
your table and making one field a primary key (this is in the Access
help file). However I can't find a way of controlling which duplicate
record is kept after running the append query, which would be really
handy. For example, if I had the following table with 2 fields:

field_a field_b
1 28/05/08
1 29/05/08
1 30/05/08
2 30/05/08
2 29/05/08
2 28/05/08
3 28/05/08
3 30/05/08
3 29/05/08

I would like to dedupe field_a with the condition that the date in
field_b is the most recent date. These would be the results I'm
after:

field_a field_b
1 30/05/08
2 30/05/08
3 30/05/08

However when I run the append query after making field_a the primary
key, the results I get are:

field_a field_b
1 30/05/08
2 28/05/08
3 29/05/08

This is great because it dedupes field_a, but it doesn't give any
control over which duplicate record is kept. Any help would be
greatly appreciated!
Cheers
Andy

 

[Michel Walsh]

Append the query:


SELECT field_a, MAX(field_b)
FROM tableName
GROUP BY field_a


to your other table. ie, something like:


INSERT INTO otherTable
SELECT field_a, MAX(field_b)
FROM tablename
GROUP BY field_a



(you may have to specify a list of two fields after the other table name).



Vanderghast, Access MVP

 

[not_an_exit]

Append the query:

SELECT field_a, MAX(field_b)
FROM tableName
GROUP BY field_a

to your other table. ie, something like:

INSERT INTO otherTable
SELECT field_a, MAX(field_b)
FROM tablename
GROUP BY field_a

(you may have to specify a list of two fields after the other table name).

Vanderghast, Access MVP

Michael,

Thanks great it works perfectly! Thank you very much for your help
Andy

 

[not_an_exit]

Michael,

Thanks great it works perfectly! Thank you very much for your help
Andy

Argh looks like I spoke too soon.. I would like to include other
fields in the results, but when I add them I get "you tried to execute
a query that does not include the specified expression field_c as part
of the aggregate function. So using the above example, from a table
which has this in it:

field_a field_b field_c
1 28/05/08 A
1 29/05/08 B
1 30/05/08 C
2 30/05/08 A
2 29/05/08 B
2 28/05/08 C
3 28/05/08 A
3 30/05/08 B
3 29/05/08 C

I would like to return the results:

field_a field_b field_c
1 30/05/08 C
2 30/05/08 A
3 30/05/08 B

Is this possible do you think?

 

[Michel Walsh]

Append the two fields:

INSERT INTO otherTable(field1, field2)
SELECT field_a, MAX(field_b)
FROM tablename
GROUP BY field_a


Note that you have to specify which of the two fields will get field_a and
MAX(field_b), then, make another query:



UPDATE otherTable INNER JOIN tableName
ON otherTable.field1=tableName.field_a
AND otherTable.field2= tableName.field_b

SET otherTable.field3 = tableName.field_c





Vanderghast, Access MVP