Duplicates

[steve.fay]

While not a newbie with Access, I'm just very ignorant with it, so
please be gentle.

Someone could really make my day if I could get some help with a
problem I have. I do the results/scoring for a non-profit dance
competition and I'm having some troubles with identifiying duplicates.
Let me explain.

I have a very simple database design with Access 2003 - basically a
"Dancer" table and a "Results" table. The only connection between them
is a "dancerid" field. That all works well and good. One dancer can
have many results, each with the same "dancerid" but with different
"CompNum" (competition numbers).

I have three fields in my "Results" table, that signify scores from
three judges. "ScoreA", "ScoreB", "ScoreC". Different competitors in
the same competition can get the same ScoreA, ScoreB, and/or ScoreC.
These fields are numbers. Is there a way I can run a query against a
compeition that will look at all the competitors, and check to see if
anyone else has the same "ScoreA" and mark a field called "TieA" as YES
if there is a tie in ScoreA or NO if not?

Here's an example, comma separated:

Competitor, Competition#, ScoreA, ScoreB, ScoreC
123, 900, 88, 89, 91
234, 900, 82, 89, 90
345, 900, 88, 90, 91

Again, I have three fields called "TieA", "TieB", and "TieC" that are
set up as Yes/No and I'd like 123's TieA to be Yes, TieB = yes, TieC =
yes. 234 should be: TieA = no, TieB = yes, TieC = no. 345 should be
TieA = yes, TieB = no, TieC = yes.

Hope this makes sense. Any help with this would guarantee a Christmas
card or a Holiday card of your religous preference.

Thanks again so much!
Steve

 

[steve.fay]

anyone?
....


anyone?
....


Bueller?
....


Bueller?
....

 

[Michel Walsh]

Hi,

untested, but something like:



SELECT a.competitor,
a.competitionNumber,
a.ScoreA,
EXISTS( SELECT COUNT(*)
FROM myTable As b
WHERE b.competitionNumber=a.competitionNumber
AND b.ScoreA = a.ScoreA
AND b.competitor <> a.competitor) As tieA,
a.ScoreB,
EXISTS( SELECT COUNT(*)
FROM myTable As b
WHERE b.competitionNumber=a.competitionNumber
AND b.ScoreB = a.ScoreB
AND b.competitor <> a.competitor) As tieB,
a.ScoreC,
EXISTS( SELECT COUNT(*)
FROM myTable As b
WHERE b.competitionNumber=a.competitionNumber
AND b.ScoreC = a.ScoreC
AND b.competitor <> a.competitor) As tieC

FROM myTable As a





Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

This is probably not the best solution but it would work.
Create a query like
select count([competitor]) as CompCount, score A
From YourTable
Where Compition # = 900
Group by scoreA


Then create an update query
Update YourTable Right Join YourQuery on YourTable.scoreA = YourQuery.scoreA
set TieA = true
where ((YourQuery.CompCount > 1) and (YourTable.Compition# = 900))

You can develop a form with combo boxes to select the compition and change
your query to look at the combo box instead of hard coding the compition#.
You would have to do this for all three score. This is not tested but
hopefully will get you in the ballpark. Somebody will probably have a
cleaner solution.