RE: [MS_AccessPros] Delete a duplicate entry - sort of

Duane-

Here's the sql of my query, which is not doing what I need. What I want is to show the top ten fish of each type caught in each tournament, showing only the largest fish per fisherman per fish type per tournament. What am I doing wrong? And yes, a fisherman could have a top entry for more than one type of fish.

It would be a shame if you were not a fisherman - LOL!

SELECT tblDerbyCatch.TournamentID, tblDerbyCatch.Fish, tblDerbyCatch.ContestantID, Max(tblDerbyCatch.Weight) AS MaxOfWeight
FROM tblDerbyCatch
GROUP BY tblDerbyCatch.TournamentID, tblDerbyCatch.Fish, tblDerbyCatch.ContestantID
HAVING (((Max(tblDerbyCatch.Weight)) In (Select Top 10 [weight] From tblDerbyCatch)))
ORDER BY Max(tblDerbyCatch.Weight) DESC;

Leatha

To: ms_access_professionals@yahoogroups.com
From: duanehookom@hotmail.com
Date: Mon, 11 Feb 2013 23:34:34 -0600
Subject: RE: [MS_AccessPros] Delete a duplicate entry - sort of


























Can a single participant make the lists for more than one fish? If so, create a query by participant, tournament, type of fish, and max weight. You may need to join this query back to tblCatch to get the length of the fish since you might have some skinny ones.



Use this as the record source of your report.



Take this advice from a fisherman named Duey Hookom ;-)



Duane Hookom MVP

MS Access



----------------------------------------

> From: leathaj@hotmail.com

>

>

> I have the following tables: tblParticipants, with a key field ParticipantID (autonumber), Participant Name, Address, Phone, email; tblFish, with fishID and fish name (King Salmon, Brown Trout, etc.); tblTournament with tournamentID and tournament name (derby, kayak, pro, amateur); and tblCatch, with ParticipantID, TournamentID, fishID, fish length and fish weight. What I need to create is a report for each tournament which lists the top ten fish of each type by length and weight, along with the participant's name, with only the largest fish of each type per participant. I have a totals query which groups by tournament and lists the top ten fish of each type. What I need is to eliminate all but the largest fish caught by each participant. Would the totals query for the largest fish per fisherman be based on my existing totals query?

>

> Thanks again for your help!

> Leatha Johnson

>

> To: ms_access_professionals@yahoogroups.com

> From: duanehookom@hotmail.com

> Date: Mon, 11 Feb 2013 22:32:25 -0600

> Subject: RE: [MS_AccessPros] Delete a duplicate entry - sort of

>

> I think you should provide significant information about your tables, fields, and data. I expect you could create a totals query with the largest fish per fisherman.

>

> Duane Hookom MVP

>

> MS Access

>

> ----------------------------------------

>

> > To: ms_access_professionals@yahoogroups.com

>

> > From: leathaj@hotmail.com

>

> > Date: Mon, 11 Feb 2013 18:39:01 -0600

>

> > Subject: [MS_AccessPros] Delete a duplicate entry - sort of

>

> > I've been asked to develop a database to track the fish caught in a local fishing derby. One of the lists will show the top ten fish caught. The kicker is this - if one fisherman caught more than one of these top ten, only the largest fish should be included in the list. I can delete the duplicates, but I don't know how to eliminate all but the largest. How should I go about this?

>

> > TIA for all help (we live on Lake Michigan, so fishing is a big deal here!).














[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/