Find duplicates and create table with dates of duplicates

[alix.grant]

Hi this is my first post... so hi all

ok what i have is a table with contact details 900k plus

there are about 90k of which are duplicates.

this is the basic feilds that are important in this case.

Id, data_source, data_recived, data_code,

what i want is to have a table with unique records (no dups in data_code)

this table will look like this...

Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,

I know there is no more than 4 dups of each record.

what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.

if anyone can help it would be great .

thanks in advance.

 

[m.shakeebahmed]

this require a macro... what my understanding is !! because the source table is in normalized form and that structure of new table is non-normalized. Possible solution is to go with group by and aggregate functions which will only provide the number of duplicate records, without any additional information

 

[alix.grant]

ok what about a table that has data_code, Num_dups, first_dup_date, last_dup_date, daysbtw_first_Last.

Would this be possible and what would be the best way of doing it?

Thanks

 

[m.shakeebahmed]

yes this can be done with a group by query

query would be like this
select datacode, count(datacode), min(dup_date) as first_dup_date, max (dup_date) as last_dup_date, (max(dup_date) - min(dup_date) ) daysbtw_first_Last
from Table
group by datacode

 

[m.shakeebahmed]

yes this can be done with a gropu by query

query would be like this

SELECT datacode, Count(1) AS number of duplicate, Min(dup_date) AS MinOfdate, Max(dup_date) AS MaxOfdate, (Max(dup_date)-Min(dup_date)) AS Expr2
FROM Table
GROUP BY datacode;

 

[alix.grant]

Hi sorry i must explain I am a total newby to access how would i add this query to my database?

Thanks

 

[m.shakeebahmed]

queries -> New query -> design view -> close show table -> right click -> sql view
and then paste the query....