Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Grady is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    18

    Please help me change the primary key data type or other possible solutions

    Hello,



    I have become the de-facto database "expert" in my office as my small amount of knowledge trumps everyone else's. I took a couple of introductory community college courses in MS access and have played around with VBA in excel and to a lesser degree in access. We have a database to collect and manage data collected from farms that we work with. The database was developed before my time and the person who designed and implemented it has since passed away. Based on my knowledge of database design, I might have designed it differently if it was up to me, but we have to work with what we have in the short to medium term. The database was designed to handle a set number of farm visits or "interviews" prior to the farm being "finished". Of course our program has evolved and we now have additional interviews that need to take place. I would like to modify the database to allow for the possibility of additional interviews. Having looked at the code, I feel a certain amount of optimism that I can do this. My initial question is this (something tells me I might be asking a few other questions ):

    I need to add additional "interviewType" records in tblInterviewType to accommodate additional interviews. The primary keys currently in tblInterviewType are sequential short integers (see image below) and they need to stay that way because they are related to the "interviewType" field in the tblInterview. When I attempt to add additional records to tblInterviewType, it auto generates a long random integer for the primary key. I attempted to delete relationships between this table and the others and change the primary key data type but access would not allow me to do so. I'm guessing there might be a work around for this. Does anyone have any suggestions? Sorry for the long winded description. Thanks.


    Click image for larger version. 

Name:	Interview relationships.png 
Views:	46 
Size:	17.7 KB 
ID:	37266Click image for larger version. 

Name:	tblInterviewType.png 
Views:	47 
Size:	15.9 KB 
ID:	37267

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you show us the table design for tblInterviewType?

    What are the new interview types to be added?

    The purpose of the PK interviewTypeID is to provide unique number for each record in the table. So whether those numbers are sequential is not/should not be a concern generally.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    It looks like your autonumber field counter is set a way forward for this table.

    Open the database in design mode, and compact the database. after that the next autonumber PK for table must be currently biggest PK value +1.

  4. #4
    Grady is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    18
    Thanks for the quick reply Orange;

    >>Can you show us the table design for tblInterviewType?

    I hope this is what you mean...

    Click image for larger version. 

Name:	tblInterviewType_design.png 
Views:	42 
Size:	40.4 KB 
ID:	37276

    What are the new interview types to be added?
    I realize the language of these various follow-ups is a bit awkward but...

    9 2nd Subsequent Follow-up
    10 2nd Subsequent Follow-up Report
    11 Not Assigned

    The purpose of the PK interviewTypeID is to provide unique number for each record in the table. So whether those numbers are sequential is not/should not be a concern generally.
    I agree with you that in general the PK value ought not to matter, however as you can see in the relationship diagram above, there is a relationship between the field interviewType in tblInterview (which have values between 1-9) and the PK interviewTypeID for tblInterviewType

    ArvilLaanemets, thanks for the quick reply and suggestion.

    Open the database in design mode, and compact the database. after that the next autonumber PK for table must be currently biggest PK value +1.
    I tried this and it didn't work. Access automatically assigns a random long integer for the PK.
    Attached Thumbnails Attached Thumbnails tblInterviewType_design.png  

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No, that isn't what that means.
    You have values 1 thru 9 for existing records. Your graphic also shows that new values for interviewTypeID will be Random. That simply means they will not necessarily be sequential. The main concern is that they are unique.
    I don't think there is an issue --you new records will automatically be assigned a new value (random) by Access.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    I never have used Random for autonumeric id's (and I can't imagine a situation where something like this is useful), but when your index is generated as random, then in no way it can be sequential. To get what you wanted in post #1, you have to change this index to common autonumber when this is possible (I haven't Access available at moment, so I can't test this), or you have to create a new empty table with normal autonumeric id, enter the info from your old table into new one (Having only 9 records there the fastest and simplest way will be enter values manually - in order of values. In case there are gaps in id values, you have to enter records for missing entries, and as last step delete abundant rows.), delete the old table, and rename the new table with name of old one.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I never have used Random for autonumeric id's (and I can't imagine a situation where something like this is useful),
    four reasons I can think of

    1. to totally discourage users trying to assign any sort of meaning to the value - in particular order of entry (I appreciate user should never need to see the PK, but sometimes it helps to show it in some discreet location on a form - usually together with 'statistical' data such as creation date, date of last update, number of updates, last user to update etc)
    2. when synchronising with other dbs (usually in conjunction with a replicationID field)
    3. when you have an extremely large number of records. Random uses negative as well as positive numbers, so will have twice as many values as increment which only uses positive (although I guess you could reseed to a negative number which will increment towards zero) - but probably that number of records would exceed the 2Gb limit of access. if your backend is sql server that can cope with that number of records, perhaps more of a requirement there.
    4. when there is a requirement to archive off data to other db's, it is easier to maintain referential integrity when records are deleted - but depends on your archive policy - the increment will be reset on a compact and repair

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I too am not seeing any issue regarding having new PK values for added interviewtype records. The randomness (or not) of the PK values should be of no concern with respect to those values being stored in tblInterview as FK values. The only thing I do suspect is not quite right is repeating the farmNumber in tblInterview. If one farm PK ID is one farm, which can have only one farmName thus one farmNumber, then I don't see the need to repeat the data.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The autonumber PK is just for uniqueness of records and can be considered necessary for the database management system. It is not intended to be meaningful to the user. If you want/need a number that is meaningful to you, then define one and manage it.
    I do not use random either, but Ajax makes good argument for certain cases.
    I'm not sure why/how you have sequential numbers 1--9 if the NewValues is Random----unless it has just been reset to Random??

    See this link for more about Autonumbers.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I agree, when a new interview type record is entered, the ID generated only must be unique, sequential is not important.

    9 2nd Subsequent Follow-up
    10 2nd Subsequent Follow-up Report
    11 Not Assigned
    Why do you show "Not Assigned" as having ID 11 and ID 9 is reassigned? Adding new records should not change existing records. Should be:

    9 Not Assigned
    10 2nd Subsequent Follow-up
    11 2nd Subsequent Follow-up Report
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Grady is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    18
    Quote Originally Posted by ArviLaanemets View Post
    I never have used Random for autonumeric id's (and I can't imagine a situation where something like this is useful), but when your index is generated as random, then in no way it can be sequential. To get what you wanted in post #1, you have to change this index to common autonumber when this is possible (I haven't Access available at moment, so I can't test this), or you have to create a new empty table with normal autonumeric id, enter the info from your old table into new one (Having only 9 records there the fastest and simplest way will be enter values manually - in order of values. In case there are gaps in id values, you have to enter records for missing entries, and as last step delete abundant rows.), delete the old table, and rename the new table with name of old one.
    ArvilLaanemets, it seems that most replies from people are telling me that I shouldn't have to worry about the PK in tblInterviewType being sequential. I haven't been convinced that this is the case yet. I really appreciate your suggested work around of creating another table as I would like it to be and then deleting the original table before renaming the new one. I will use this approach if that is the route I decide to go.

  12. #12
    Grady is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    18
    Quote Originally Posted by Ajax View Post
    four reasons I can think of

    1. to totally discourage users trying to assign any sort of meaning to the value - in particular order of entry (I appreciate user should never need to see the PK, but sometimes it helps to show it in some discreet location on a form - usually together with 'statistical' data such as creation date, date of last update, number of updates, last user to update etc)
    2. when synchronising with other dbs (usually in conjunction with a replicationID field)
    3. when you have an extremely large number of records. Random uses negative as well as positive numbers, so will have twice as many values as increment which only uses positive (although I guess you could reseed to a negative number which will increment towards zero) - but probably that number of records would exceed the 2Gb limit of access. if your backend is sql server that can cope with that number of records, perhaps more of a requirement there.
    4. when there is a requirement to archive off data to other db's, it is easier to maintain referential integrity when records are deleted - but depends on your archive policy - the increment will be reset on a compact and repair
    You make good points, Thanks.

  13. #13
    Grady is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    18
    Quote Originally Posted by Micron View Post
    I too am not seeing any issue regarding having new PK values for added interviewtype records. The randomness (or not) of the PK values should be of no concern with respect to those values being stored in tblInterview as FK values. The only thing I do suspect is not quite right is repeating the farmNumber in tblInterview. If one farm PK ID is one farm, which can have only one farmName thus one farmNumber, then I don't see the need to repeat the data.
    Micron, the only issue I see with random non sequential PK values in tblInterviewType, (I don't feel I haven't articulated it well, or, I am wearing blinders), is that the interview types in the InterviewType field in tblInterview actually are numbers that have meaning to our process. There are several reports we generate that identify the stage of our process by number. E.g. ABC Farm has just completed stage 5 of our process. I don't think it would be useful for us to say the farm just completed stage -8923492837 of our process. I think the way it is set up is poor design, but we need to work with what we have. The FK in tblInterview ought to have been an ID (random, long, short, replication ID, ...) and labelled InterviewTypeID and the InterviewType ought to have been a separate field.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you need sequential numbers to identify sequential steps in a procedure or process, then design that field, populate it, and manage it via you database related coding/processes. A field that has meaning to you and users with values that signify stages in a process is a separate field (in my view). The database needs a unique identifier to manage records and relationships in your database. You need a sequential identifier of some sort for business reasons. 2 different concepts in my mind; both needed; each serving a different purpose.

    I don't agree with " I think the way it is set up is poor design". It seems a little strange but the issue is your thinking that the PK should mean something to you and users. It could be a dual purpose field; but it doesn't have to be. And your set up clearly illustrates that this PK does not have meaning to the user. Add a field --eg StepSeqNo or whatever, and carry on.

    What if a process changes (as they often do) and you have to add 1, 2 or 3 steps. And what if the new steps have to be inserted in random positions with that or multiple processes.

    Reread the link I posted on Autonumbers in post #9.

    Good luck.

  15. #15
    Grady is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    18
    Quote Originally Posted by orange View Post
    The autonumber PK is just for uniqueness of records and can be considered necessary for the database management system. It is not intended to be meaningful to the user. If you want/need a number that is meaningful to you, then define one and manage it.
    I do not use random either, but Ajax makes good argument for certain cases.
    I'm not sure why/how you have sequential numbers 1--9 if the NewValues is Random----unless it has just been reset to Random??

    See this link for more about Autonumbers.
    Orange, I agree that the PK ought not to be meaningful to the user. Unfortunately, the way this database was constructed, it IS meaningful to the user. Please see my reply to micron above.

    I also am not sure how the table got sequential values for the PK given that the setting is for random. All I can say is that this database was developed in 2010 and likely in an earlier version of Access. It went through quite a few changes over time that I wasn't around for. This may or may not explain the reason.

    Thanks for the link to the information about autonumbers, the information was useful.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change Field Data Type
    By DubCap01 in forum Programming
    Replies: 11
    Last Post: 02-07-2017, 08:08 AM
  2. Replies: 8
    Last Post: 12-14-2015, 07:02 AM
  3. How Do I Change Data Type
    By LeadTechIG in forum Database Design
    Replies: 9
    Last Post: 01-07-2015, 03:05 PM
  4. While Importing Change in data type
    By drunkenneo in forum Import/Export Data
    Replies: 1
    Last Post: 09-01-2014, 06:49 AM
  5. Replies: 2
    Last Post: 10-19-2011, 06:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums