Don't copy duplicates (1 Viewer)

[statsman]

Using Access 07.
I have a database I have been using for a few years to do our office hockey pool. In a nutshell, each participant may "draft" a team of 10 players from a list of 100 players split up into groups of 10 based on last seasons stats (you may draft 1 player from each group). This results in duplicate selections.

I append the drafted players from the table tblParticipant into the table tblDraft (after deleteing all the previous records in tblDraft). The PlayerName in tblDraft is set to indexed-no duplicates. From tblDraft I update the drafted players from the newspaper which lists the required stats each Tuesday. The settings for PlayerName means I only have to update each player once, rather than updating the same player 5 or 6 times. The tables tblDraft and tblParticipant are then compared in a query to give me the total points each Participant's drafted players have scored and hence the pool standings.

A friend has seen my database and would like to buy a copy. I have a few minor kinks in it that I have to fix before I can sell it. The major one is, when I append the drafted players into tblDraft I get a warning that not all the drafted players will be copied (since the PlayerName field in tblDraft won't accept duplicates). The warning occurs each time there is a duplicate record so I sometimes have to click "Yes" 10 or 12 times. Can I turn this warning off, or is there another way to do this so that I don't get a warning.

I did a search earlier and tried appending the records from tblDraft to another table with the no duplicate setting, but I still got the warning. Mind you I only got is once using this procedure.

 

[vbaInet]

I thought you said they were minor kinks but it seems to be a major one

I think you should be looking to deal with the problem rather than mask it. Have you tried the Find Unmatched Query Wizard which you can use to extract the non duplicates?

Maybe your copy and paste procedure needs to be revised as well because it seems to be copying and pasting unecessary records.

If you still want to mask the problem then use the Form's On Error event to trap that error number and acDataErrContinue as the Response. You can also use Docmd.SetWarnings to turn on and off warnings if it applies as well.

 

[statsman]

It's one of those things I've been meaning to do for a long time but just never got around to. Now I have to deal with it.

If I'm going to do it, I may as well make a proper job of it.

I've been using Access for pools in a number of sports for a while, but this is the only one where duplicates occur so I have little experience in dealing with dups. I will try the Find Unmatched solution first.

 

[vbaInet]

I know the feeling especially when it's only you using it and there hasn't been a need for it to go commercial

Good luck!

 

[gemma-the-husky]

think carefully before selling it - it may be better to give it away, as an mde

if you sell it, you are then at the mercy of users who may misuse it accidentally, corrupt the data, and then require support from you.

when you develop stuff for yourself, you know what it does, and how to deal with problems (eg - the case you mention) - your users wont.

 

[statsman]

I really am treading in uncharted waters (for me) and I have lost my way.

The first thing I do in this process is delete all the records from tblDraft as there may be trades and a certain drafted player is no longer a part of the pool. The macro then runs 10 append queries which appends the 10 drafted players from tblParticipant to tblDraft. qryDraft0 appends Player0 and Team0 to DraftPlayer and DraftTeam in tblDraft, then qryDraft1 does the same with Player1 and Team1 etc.

I tried the FindUnmatched query, but it did not append any of the duplicate Players. I need one of them appended.

I then changed the Design table design to allow duplicates and after the appends I ran a find duplicate query. It deleted all of the duplicates. Again I need one.

Is there a somthing I can add to my append queries that will avoid duplicates after the first one is appended?

 

[vbaInet]

The first thing I do in this process is delete all the records from tblDraft as there may be trades and a certain drafted player is no longer a part of the pool. The macro then runs 10 append queries which appends the 10 drafted players from tblParticipant to tblDraft. qryDraft0 appends Player0 and Team0 to DraftPlayer and DraftTeam in tblDraft, then qryDraft1 does the same with Player1 and Team1 etc.

Maybe a subquery to select one record per player might be useful here. See this link regarding subqueries.

Is there a somthing I can add to my append queries that will avoid duplicates after the first one is appended?

I think this has already been answered:

If you still want to mask the problem then use the Form's On Error event to trap that error number and acDataErrContinue as the Response. You can also use Docmd.SetWarnings to turn on and off warnings if it applies as well.

And here's link too.

I would highly recommend you revise the whole process you mentioned so duplicates aren't always caught.

 

[statsman]

Luckily, the hockey season doesn't start til October.

I will give it a try and let you know.

 

[vbaInet]

Lots of time to get it right then

See how you get on.

 

[statsman]

The simplest solutions are usually the best.

I changed the structure of tblDraft to add an autoID field and removed the no duplicates from DraftName.

I then created a delete subquery which removed the duplicate with the higher number per the link you supplied. I added that query to the end of the append macro.

The database is now purring like a kitten with no error messages when I do the appends.

You are truly an Access champion. Many thanks.

 

[vbaInet]

Nice. I'm bet you're beginning to smell money now

Good luck with the rest!