Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Access 2007

    I have been asked to create a feature in our database that:
    1. Searches the database for a duplicate record based on one field
    2. If found, the user is asked if he wants the values of the original record to be copied into thenew record.

    For example, if this were a library database, assume that you have a record that stores information about a book (title, ISBN, author, etc). Let's say the library purchases a second copy of the same book. Enter the ISBN and if it matches one that already exists, ask the user if he wants to automatically import the information from the existing record into the record for the second copy of the same book, saving time and possible data entry errors.

    Is something like that possible?

    I assume that the code for this would be very complicated, but perhaps someone has already posted something similar somewhere (I searched this site but was didn't find anything.)

    Thanks,

    JoeK

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Why should you have duplicated info in you database ? It's against normalization. It seems that you database structure is not correct.
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you're willing to approach it slightly differently, you could do it without code:

    • Click in the control bound to the relevant field (e.g. ISBN).
    • Press Ctrl+F to open the Find dialog.
    • Type the value you want to search for.
    • Click Find Next or press Enter.
    • If found, select the entire record.
    • Copy it.
    • Paste Append the record.
    • If there is an AutoNumber field, it will not be copied - the new record will be assigned a new value.
    If you prefer the approach you outlined, you'll need code, and you'll need to indicate which fields should be copied. For example, if there is an AutoNumber field, this should not be copied. If there is another type of unique key, you'll have to specify how to set the value of this key for the new record.


  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Minnesota
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would suggest that you not add a second record as Gold Lounger pointed out, instead have field in your table for number of books. In your ISBN field on your form use the before update event when you add a new book and do a DLookup to see if the number exists. If it does, direct it to that record and update the quantity.
    “My father says almost the whole world’s asleep. Everybody you know, everybody you see, everybody you talk to. He says only a few people are awake. And they live in a state of constant total amazement.”

    “ From the Movie ‘Joe Vs The Volcano’

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Charlottesville, VA, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Assuming your example of library books is close to the mark, there is a potentially legitimate issue here. Whether you're running a library of books or tapes or videos, what you really want to track is the copies of each item, not just the item itself. My library may have four copies of "Gone with the Wind"; where is each copy? Who has a particular copy?

    While all the advice here is sound under particular circumstances, you really do need to evaluate the structure of your database vis a vis the business model you're trying to replicate. A list of books (tblTitles) is obviously necessary from your description, but you will also need database structure to identify copies of the books (tblBooks) and link it back to tblTitles. This kind of structure allows you to keep track of the status of each physical copy, how many copies you have, etc. and still allow you to update the attributes of a particular title without violating normalization.

    Hope this helps.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think the distinction between books and copies is important, but it is not clear (to me at least), just which fields belong in which of the tables, and just when something is a new book, and when it is another copy.

    Over time I have bought several copies of this book,
    • the first was exactly the same as the ones I already had,
    • the next was a reprint with a different date
    • the next had a new forward, so there were more pages
    • the next was a new edition with an extra author added
    • the next was a paperback
    • The next was the illustrated edition with lots of photos etc
    Which are these are just copies, and which count as a new book, but maybe with the same ISBN.

    So it seems quite reasonable to want to copy an existing record, then edit some of the data in it.
    Regards
    John



  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you all for your answers and comments. I decided to go back and review the structure of the database. It seems that I didn't design it in the most optimal way. I am now in the process of redeveloping it, making sure to stick to the rules of normalization - which is what I should have done in the beginning.

    It is a big job, but in the end it will be worth it.

    Thanks again,

    JoeK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •