Find Duplicates on Multiple Fields

[mous]

Hello

I have a simple query using A97 whereby I wish to find duplicates. I am accessing third party software that records events.

For example,

Row no, Event Day, Room, Start Time, End Time, Start Wk, End Wk

Row 1 - Monday, 1, 10:00, 11:00, 17/01/03, 17/02/03
Row 2 - Monday, 1, 10:00, 11:45, 17/03/03, 17/04/03
Row 3 - Monday, 1, 09:00, 12:00, 17/01/03, 17/10/03

From these fields I can already identify the duplicates whereby an event is taking place in the same room on the same day.

But..

This is not a true record as Row 1 and Row 2 are identical on Room and Day but you will notice that they fall into different weeks and therefore not a duplicate.

Row 2 and Row 3 would only be duplicates for 1 month out of the 9 in the date range.

My question is how can I identify all possible duplicate events based on the same room, same day, at same time (not neccesarily starting and finished at the same time though) and same weeks (not necesarily the same start and end weeks).

This sounds awfully complicated to me.

Can anyone suggest a solution. I am new to SQL.

Many Thanks.

 

[barbarossaii]

Several queries created by assistants

Hi mous,

I'm quite new to access too, but this could work: create several douplicate finding queries with the assistant and take one query as basis for the other.

Example:

Query1 searches for douplicates in 'column' day and displays all 'columns' (returning the records row1, 2 & 3 from your example).

Query2 searches for douplicates in col room (of QUERY1 !!!! ) and displays all columns (returning all rows)

query3 searches for dups in col start time of QUERY2 (returnin only row 1 & 2)

query4 searches for dups in col end time of QUERY3 (returnin none as all have different times)

query5 searches for dups in col start wk of QUERY4 (returning none even if row 1 & 3 have the same date because Query4 was empty)

query6 searches for dups in col end time of QUERY5 (returning none because Query5 was empty)


HTH,

Barbarossa II

 

[barbarossaii]

bad suggestion...

Hi mous,

thought about it again and it seems like my suggestion does not work in every case properly:

room....Date
1...........02/01/02
1.............01/01/02
2.............01/01/02
2...........02/01/02


Query1 from my suggestion will pull the lines w/ room 1 & 2. But then Query2 finds dups for the date (02/01/02 in 1st and 4st 'line' and 01/01/02 in 2nd & 3rd 'line') => the information that the whole record has to be douplicate got lost.

But....

If you create a find douplicate query in A2000 with the assistant and include all fields with possible dups, the query seems to get it right. It's returnin only records in which all respective elements are equal and - best of all - its easy to create..

HTH with your A97-problem,
Barbarossa II

PS: The code the A2000-Assistant created is ("ppm" is a table, the 'columns' are named "Feld1" to "Feld4" and "AnzahlVonDuplikaten" means "quantity of dups":
SELECT First(ppm.Feld1) AS [Feld1 Feld], First(ppm.Feld2) AS [Feld2 Feld], First(ppm.Feld3) AS [Feld3 Feld], First(ppm.Feld4) AS [Feld4 Feld], Count(ppm.Feld1) AS AnzahlVonDuplikaten
FROM ppm
GROUP BY ppm.Feld1, ppm.Feld2, ppm.Feld3, ppm.Feld4
HAVING (((Count(ppm.Feld1))>1) AND ((Count(ppm.Feld4))>1));

 

[mous]

Thanks for The Suggestion

Thanks for this. We are due to transfer to A200 soon so I'll try it then.

If anyone has anoy other suggestion for A97 I would be grateful.

Thanks Again

Dawn