in reply to How do I avoid inserting duplicate numbers into an Access table?

The code could be tidied up a little but it looks like it should work. It isn't checking anything that might already be in the table, however, just the set of data you are currently inserting, is that intentional?

Some tips:
You can replace: 

while ($count<=@IDnumber) {
  if ($match == $IDnumber[$count]){
    goto NEXTLINE1;
  }
  $count++;
}

[download]
with: 
next if(grep {$_ eq $match} @IDnumber);

[download]
and you don't really need the label/goto, the command 'next' does exactly that, ie jump back to the top of the loop and start the next iteration.

As to the IDs, first, you should probably check the current values in the table, to see if the one you want to insert is present, assuming there are any. But a much easier way to do this would be to make the field in the table unique ('no duplicates', in Access), in which case it will simply refuse to insert that line, and you can carry on to the next.

C.

Replies are listed 'Best First'.
Re: Re: How do I avoid inserting duplicate numbers into an Access table?
by Anonymous Monk on Mar 06, 2004 at 18:44 UTC
    Hi there, thank you for your advice. I really learned from what you wrote. But I still have some prolem. When I was trying to set the Access table to refuse duplicate values, the program simply stopped and gave error message when there were duplicated values. So I want to first read what are already in the table into an array, lets say array A1, and compare the values I intent to insert into the table with the values in A1. If a value has already been in A1, I will discard it and go to the next value. If the next value is not in A1, I will put it into an array A2. After this process ends, I then will insert the values in A2 into the Access table. My problem is that the values in A2 contains some values in A1. I do not know why.
[reply]
      Here is the line that is exiting your script:

      die qq(SQL fail "$rc":), $db->Error(),qq(n) if $rc;

      Just handle the error differently. Check $rc or $db->Error(), and continue with the next insert if the error points to a duplicate value. 

      # untested
if ($rc == WHATEVER_ERROR_IS_GENERATED_BY_DUPLICATE) {
    # you can also test ($db->Error() =~ /some value/)
    next;
} else {
    die qq(SQL fail "$rc":), $db->Error(),qq(n);
}

      [download]
      Your script will still die if the insert fails for a different reason.

      HTH...

[reply]
[d/l]
[select]
      Access has a query wizard to find duplicate values in a table.
      First, add an autoincrement column to the table like others suggested.
      Second, run the Find Duplicates query wizard to generate a query that finds all the duplicate rows.

      Use that  Find Duplicates query as the basis to generate and fine tune a  Delete query that gets rid of the duplicate rows by eliminating the rows having an autoincrement value higher the lowest one found for each duplicate.

      Then delete the autoincrement column from the table and then re-add it again. This should work as long as you haven't used the column or table in a Relationship.

      Now you should have consecutively numbered rows with clean nonduplicated data in a table that generates it's unique row autocount ID automatically.

[reply]

In Section Seekers of Perl Wisdom