Query assistance

[ChewDoggie]

G'morning all!

I have an Access View called "vDupRacers" that attempts to find all duplicate racerids that occur in the registration table. This is the code I have currently:

vDupRacers

SELECT DISTINCT eventid, status, racerid
FROM Registration
GROUP BY eventid, status, racerid
HAVING Count(racerid) > 1 And status <> 'D';

The vDupRacer View is proving to be unreliable. It doesn't catch KNOWN duplicate racerids. If anyone can be of assistance in re-formatting the vDupRacers View, I'd really appreciate it. I need to, somehow, include a classid with the above query but the classids will be unique, so the above query fails to produce any results.


Thanks!


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[PHV]

Anyway, using the DISTINCT predicate in an aggregate query is irrelevant...
It doesn't catch KNOWN duplicate racerids
Any chance you could post some samples ?

Your posted code don't display all duplicate racerid but all duplicate eventid & status & racerid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Your query looks for a duplication in the combination of the three fields ... not just RacerID. If it's just duplicate racerid fields that you want then

SELECT racerid
FROM Registration
WHERE status <> 'D'
GROUP BY racerid
HAVING Count(*) > 1

If you need the other fields then

SELECT racerid
       , MAX(eventid) As MaxEvent
       , MAX(status) As MaxStatus
FROM Registration
WHERE status <> 'D'
GROUP BY racerid
HAVING Count(*) > 1

 

[ChewDoggie]

thanks PHV and Golom!

Golom...you're everywhere...when do you find time to work?

Am testing this now....will report back.

Thanks!



AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

Thanks Golom !


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org