Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2009
    Posts
    11

    Unanswered: Error Message Duplicate Values

    I thought I would give you an idea of what I am trying to do before I explain my problem.

    On starting in my role (Nothing to do with databases by the way ) there was a database used to record information that comes in to us on a form. The database holds the exact information that is on the form along with some extra fields. The original database had one table with nearly 255 fields in it, as you can imagine this is not ideal. The form that comes in is like an application and if successful the applicant may be awarded a contract so further information will need to be recorded. Once it was realised that the original database was not just going to be stand alone data it was advised the one large table be split in to a number of tables as it is not the ideal way to run a database. As I showed a basic (should say very basic) access knowledge I have been tasked with this job.

    The table has now been split into a number of tables with linked groups of data. I have created a master form containing a number of sub forms, the relationship seems to be working ok (One to One) as when I enter data and run queries all of the data is linked.

    On trying to enter data in some of the fields I receive the following error message "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationships. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    In the table the fields are set to NO in the indexes, I have changed this to YES ALLOW DUPLICATES butr it didn't make any difference.

    I don't know whether this information is of any relevance but the way the forms are set up means that a number of subforms link back to one table.

    I hope this is enough information for someone to help me, if not just let me know what you need and I will try and find it.

    Many thanks in advance.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Sashacarna
    On trying to enter data in some of the fields I receive the following error message "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationships. Change the data in the field or fields that contain the duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    In the table the fields are set to NO in the indexes, I have changed this to YES ALLOW DUPLICATES butr it didn't make any difference.
    Is one of the fields that you're trying to enter the primary key for the table? If so, this has to be unique. What does the structure of the table(s) that's causing the trouble look like?
    Me.Geek = True

  3. #3
    Join Date
    Apr 2009
    Posts
    11
    Hi Nick the field isn't a primary key that I am trying to update. I should have added to the statement above that I have now cahnged the indexes back to NO.

    The tables are all very basic with the primary key (Ref No) as an autonumber (Long Interger) and then the data that I need listed. I'm not really sure how else I can describe it.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That message only ever appears when you try to put a duplicate value in a unique field. It doesn't have to be a primary key, just an uniquely indexed one.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2009
    Posts
    11
    Sorry for the stupid question but if it's a unique fied will it say Yes No Duplictaes in the indexes field in the table? They all seem to say No here should I change them to Yes allow duplicates?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't really believe in stupid questions -- if you don't know something and have a question, it cannot be stupid to ask it!!

    No, if they all say No in Indexed, then you don't have unique fields.

    Can you upload the database? I am curious as to what else might cause this error!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2009
    Posts
    11
    Thanks for your help with this and I am sorry for it taking me so long to come back online, I was pulled off this to work on another tight deadline, but I am now able to look at this again......

    I have come back to it hoping a fresh pair of eyes might do it but after two days I am pulling my hair out!!!! I have redone the dtabase from scratch using just two tables but I am still getting the same problem. There are two subforms pulling information from the same table so I thought this might be the problem as the error comes up when I try to enter data on the second subform, to get round this I made 2 queries with the info I needed and linked to this but I still get the message.

    I have tried to upload the database to this website but it says it is an invalid file - is the only way for me to do it to winzip the file?

    Thanks again

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the error message you are getting indicates that you are trying to insert a row and its being rejected as something in that row is duplicating exisiting data and your design has said that cannot be duplicated

    usually thats the primary key in the row you are inserting, but other indexes can be made unique / 'no duplicates'.

    so its to do with either your data, ie you are trying to insert a duplicate row or its to do with your design, duplicates are expected but not in the columns as you have currently specified them.

    a primary key must be unique, it uniquely identifies each and every row.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sashacarna
    is the only way for me to do it to winzip the file?
    Yes I'm afraid it is.

    Although - never tried it but I guess you could change the extension to .txt
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, don't do that, the filesize will be larger than needed

    You don't have to use WinZip if you have Windows XP or later. Instead you can right click file(s) and use Send To -► Compressed (zipped) Folder.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Apr 2009
    Posts
    11
    Thanks StartreKker, that's worked.

    I am really hoping that one of you can help me, I have asked someone else in work that is more experianced with databases and he can't spot anything obvious.

    He has suggested rather than using a one to one rlationship use a one to many and take the scroll bar off the form so that people can't add more than one record - do you advise this?

    Thanks again
    Attached Files Attached Files

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so you have a 1:1 relationship between two tables and you are creating a subform. No point. This is the reason why you are getting the error message. For a main/subform to work there must be a 1 to M relationship between the tables.

    My advice is to merge the two tables in to one. You will then be able to do what you want with ease and no disadvantages. That is only IF there really is a 1 to 1 relationship between Finance and Organisation.

    Look I have to be honest here, your table design doesn't look good to me. Field names with question marks and spaces is going to cause some dramas down the track... and that just scratching the surface.

    Fieldnames such as contact 1, contact 2, contact phone 1, contact phone 2 etc just scream out to me that you need to go back to the drawing board of designing your tables [correctly] and stop developing your form.

    Looking at your fields, I would guess that you at least need a table for Organisation plus one for Contacts and one for Registration and probably one for Job or Project. Once your table design is correct, you will find developing logical forms for them is quite easy. With all the fields in one table, you are going to suffer all the problems with redundancy and poor table design.

    I don't mean to be harsh here, I really am trying my best to help you, nothing more.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Geez, I wish I had the time to finish my Access training website, it would help here a lot.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Apr 2009
    Posts
    11
    Thanks Startrekker - don't think ur being harsh at all I completely understand what you are saying. At the end of the day I am not a database developer and am really trying to feel my way in the dark. This database will not be doing anything other than holding information from a form submitted to us with a few extra office only use fields, which is why I was going down the one to one route. Each organisation will only ever have one record in each table. There will never be an instance where there is a org in the organisation table with 3 records in the finance table it just won't happen.

    The database will never become anything more than it is once I have finished this, it is just to store data with easy access to run certain queries and reports. There will also never be a vast amont of data in it maybe 700 records max. The database that I uploaded only contains two of the tables that it will eventually hold, there will be 8. Originally when I started here the database had all these fields in one table so it was nearly 250 fields, after I have looked I have identified more fieldsthat needs to be added it definately exceeds this, which is why I can not combine the fields into one table. The main form will eventually be made up of 15 pages containing a number of subforms.

    I understand that these types of things must frustrate you as experts in your field and you must get so many people trying to get your services free and I want to assure you I am not doing that, I am just looking for guidance beleive me when I say I have searched the internet high and low for help but when you don't really know what you are doing it is hard.

    Would a work around for this be to make all the relationships one to many and remove the scroll bar so no extra records can be added to each organisation?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sashacarna
    Thanks Startrekker - don't think ur being harsh at all I completely understand what you are saying. At the end of the day I am not a database developer and am really trying to feel my way in the dark. This database will not be doing anything other than holding information from a form submitted to us with a few extra office only use fields, which is why I was going down the one to one route. Each organisation will only ever have one record in each table. There will never be an instance where there is a org in the organisation table with 3 records in the finance table it just won't happen.
    ..heard that one before

    Quote Originally Posted by Sashacarna
    The database will never become anything more than it is once I have finished this, it is just to store data with easy access to run certain queries and reports. There will also never be a vast amont of data in it maybe 700 records max.
    Quote Originally Posted by Sashacarna
    Would a work around for this be to make all the relationships one to many and remove the scroll bar so no extra records can be added to each organisation?
    if a thing is worth doing then its worth doing correctly. theres lots of variants of correctly, and ultimately only you can decide what is right for you.

    however I too believe that a redesign is required, I also believe that you will get huge benefit from the standard tomes we refer to here on normalisation and table design
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design

    I think you will be better off reading and understanding the above BEFORE doing anything else on this requirement

    theres thiunkgs in your table design which frankly are odd
    ferinstance the 10 discrete qualification of the entity (ranging from "Registered Cahrity" to "If other status") these are mutually exclusive, you can be one not the other so in effect you have 9 wasted columns. thats a prime candiate to be pushed into another table and have a foreign key to that table in Organisation
    you repeat a whole lot of information in the tail end of the organsiation table the stuff ending in act.. that could be pushed down to a sub table


    I'd also emphasise you are building a rod for your back by having columns with spaces and other non standard characters in. get into good habits early. if yiou want to include the human text place it in the Caption property for each column

    some developers prefer CamelCase, some prefer under_scores

    eg OrgDetailsRef, PostCode, IDRISRef
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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