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

[Grady]

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

June7, I agree in principle, but I am starting to realize that the table wasn't designed properly. I think I need to re-consider my approach and may need to settle on a fix that is not perfect.

[orange]

OK.
Then consider this as a possible "fix". This is just typing as I'm thinking and considering the importance of this field to you and users. So, anyone jump in an critique/refute/revise as necessary.

Suppose you take your current database -before any new Records are inserted with the Random including negative values.
(even if you have to go to a recent backup)
Make a copy of the table, set that new field to autonumber incremental ( whatever but not Random). Then copy your existing records -those with values 0-9; then add your new records.

I haven't tried this or experimented, but it might work and keep your numbers/values in line with your current processes.
I agree that numbers like -1427565 don't have any meaning to the users and will likely lead to confusion.

Let us know how you proceed/resolve this.
Good luck.

[Grady]

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.

Orange, I hear what you are saying. I will attempt to rejig my thinking on this. Not sure how I will proceed, but at least I have a new perspective. Thanks.

[Grady]

OK.
Then consider this as a possible "fix". This is just typing as I'm thinking and considering the importance of this field to you and users. So, anyone jump in an critique/refute/revise as necessary.

Suppose you take your current database -before any new Records are inserted with the Random including negative values.
(even if you have to go to a recent backup)
Make a copy of the table, set that new field to autonumber incremental ( whatever but not Random). Then copy your existing records -those with values 0-9; then add your new records.

I haven't tried this or experimented, but it might work and keep your numbers/values in line with your current processes.
I agree that numbers like -1427565 don't have any meaning to the users and will likely lead to confusion.

Let us know how you proceed/resolve this.
Good luck.

Yeah, this is closer to what I was thinking. I will give this a go first and see how it works. I will report back. Thanks for taking the time to consider my issue!

[Micron]

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.

No, the way you have it is correct IF you want to see InterviewTypeName (First Contact) and not "1" in your form. If the number has more meaning to you, then you're missing a numeric field, which might just duplicate the autonumber ID field. The ONLY way PK id's, autonumber or not, should be employed is to relate data in one table to another table. That's it. If you insist on breaking that Commandment, you deserve the trouble you get.

What hasn't been commented on is the duplication of farm number in tblInterview.

[Micron]

Make a copy of the table, set that new field to autonumber incremental ( whatever but not Random). Then copy your existing records -those with values 0-9; then add your new records.
Good luck.

You can only paste one field. If you copy all records and attempt to paste into a new table, you might get unexpected results at worst, and will have to type in all the missing fields. Better to copy the table with data option, remove the autonumber pk field, save, close (just to be sure) reopen, ensure the data is in ascending order and add the pk field back as autonumber but not random. By doing so, you reseed the table. This doesn't mean I changed my mind - a numeric interview field is missing if the number is important.

EDIT - idea flash. The number field could be added to tblInterviewType. User could choose "First Contact" from combo (assuming there is one) and get the number from that table. Might be less work as a fix.

[Grady]

No, the way you have it is correct IF you want to see InterviewTypeName (First Contact) and not "1" in your form.

Yes I understand this and agree that IF the only thing I needed to use was InterviewTypeName. As I mentioned above, rightly or wrongly there are reports that use InterviewType to describe the stage a given farm is at in the process (e.g. Farm A is at stage 5). Also our database has a form called dashboard where you can select a specific farm and "progress" it to the next stage. There is VBA code that does this:

[Forms] ! [frmMaster].setInterviewType (contextState +1)

This code actually increments the InterviewType to the next "stage" by adding 1.

If the number has more meaning to you, then you're missing a numeric field, which might just duplicate the autonumber ID field. The ONLY way PK id's, autonumber or not, should be employed is to relate data in one table to another table.

Again I agree. I have spent a lot of time thinking about how I would design this beast if I had to do it all over again. I am not a database designer and working with this database is a small part of my job. Do you agree that under some circumstances a "good enough" solution rather than a technically perfect solution is preferable?

That's it. If you insist on breaking that Commandment, you deserve the trouble you get.

Hahaha. This is very true but funny at the same time.

What hasn't been commented on is the duplication of farm number in tblInterview.

Good observation. I hadn't noticed this before. I am thinking this is poor design and redundant.

[CJ_London]

I

also am not sure how the table got sequential values for the PK given that the setting is for random

my guess would be the fields was originally a number datatype of long or integer and someone along the way after it have been populated changed it to an autonumber. Autonumbers cannot be changed back once the field has been populated.

Do you agree that under some circumstances a "good enough" solution rather than a technically perfect solution is preferable?

the fact you have given the field meaning and now you are experiencing problems says to me the circumstances are not good enough

[Micron]

Do you agree that under some circumstances a "good enough" solution rather than a technically perfect solution is preferable?

Subject to the limitations of the db designer or administrator, or time constraints, yes. I could no more expect you to employ what I might deem an easy (not necessarily fast) fix if it's beyond the scope of your knowledge than anyone might expect me to fix something that was beyond my knowledge. To repeat, any easy temporary fix would be
- to copy the table with data and open in design
- add a number field (e.g. InspectNum) as long or as integer for interview numbers, save and switch to datasheet view
- add the numbers and ensure they are in the correct ascending order along with the interview descriptions
- go back to design, (maybe copy the field name to clipboard) remove PK autonumber field, save, close and reopen in design view
- put the autonumber PK field back (NOT random), save and go back to datasheet view
Your old autonumber field should be back and those values should duplicate the numbers in the field you added. This will allow you to ensure the PK values continue to line up with the number values you added (they are sort of a bookmark check) If you add type 6 and the autonumber doesn't jive, you should see that right away.
- then you rename the old table (I prefix with xx so that it moves to the bottom of the nav pane) and give the new table the old table name.

This would buy you some time so that you can change the queries/forms/reports to use the InspectNum field instead in your development front end. You ARE working with a split db, right? Thus changing front end design won't impact users because you have a development front end to work on whenever you have the time.

[Grady]

Imy guess would be the fields was originally a number datatype of long or integer and someone along the way after it have been populated changed it to an autonumber. Autonumbers cannot be changed back once the field has been populated.

This seems feasible.

the fact you have given the field meaning and now you are experiencing problems says to me the circumstances are not good enough

I see your point, however, the fact that the field has meaning was built into the database from the beginning. I aspire to fix this problem the correct way and I appreciate all the input everyone has given me towards this end. I can imagine it must be frustrating dealing with people who know just enough to be dangerous, which is the category I find myself in.

[June7]

Then maybe don't use autonumber in tblInterviewType, just a number type.

If you change "Not Assigned" to have ID 11, will have to run an update on tblInterview to correct records that have ID 9. Do this before using the 2 new categories for any records.

So fix the records in tblInterviewType then run the update (SQL or Find/Replace) on tblInterview.

[Grady]

Subject to the limitations of the db designer or administrator, or time constraints, yes. I could no more expect you to employ what I might deem an easy (not necessarily fast) fix if it's beyond the scope of your knowledge than anyone might expect me to fix something that was beyond my knowledge.

This is my challenge, doing the best I can with the resources I've got.

To repeat, any easy temporary fix would be
- to copy the table with data and open in design
- add a number field (e.g. InspectNum) as long or as integer for interview numbers, save and switch to datasheet view
- add the numbers and ensure they are in the correct ascending order along with the interview descriptions
- go back to design, (maybe copy the field name to clipboard) remove PK autonumber field, save, close and reopen in design view
- put the autonumber PK field back (NOT random), save and go back to datasheet view
Your old autonumber field should be back and those values should duplicate the numbers in the field you added. This will allow you to ensure the PK values continue to line up with the number values you added (they are sort of a bookmark check) If you add type 6 and the autonumber doesn't jive, you should see that right away.
- then you rename the old table (I prefix with xx so that it moves to the bottom of the nav pane) and give the new table the old table name.

Thanks for breaking this down for me into specific steps. I am going to try this today.

This would buy you some time so that you can change the queries/forms/reports to use the InspectNum field instead in your development front end. You ARE working with a split db, right? Thus changing front end design won't impact users because you have a development front end to work on whenever you have the time.

As far as I know, our database is NOT split. The way it was set up was with a Design Master on our shared network drive and individual replicas on individual work stations that we synchronize with the Master regularly. The database has the front end and back end in the same file. Having now just read a bit about splitting Access databases, it seems like it would be a good idea offering many benefits. Currently all changes need to be done in the design master, so anytime I am working on it, I have to ensure nobody synchronizes to the Master. Perhaps I will look into the possibility of splitting the database as another project after I solve this initial problem.

Thanks so much to everyone for all of your constructive input. I will report back if/when I am able to get this nailed down.

[orange]

Confirm whether or not you have a split --frontend/backend-- database. This is a critical structural issue to avoid/reduce corruption. It is more than a good idea.
Do you have a separate development/testing area for working with the revisions you anticipate? You can design and test/verify possible approaches before committing to a specific implementation.
Good luck. Let us know how things progress.

[June7]

The satellite files have UI and tables? Exactly how are you synchronizing satellite and main files? If this is not split design and you do revise tables in the main file and then 'synchronize', will surely corrupt the data.

[Micron]

I responded to a pm to assist by looking at the db, which is far too large to post here even if zipped. It has 47 tables (but I think I lost some in the conversion process), 239 queries, 82 forms and 90 reports. Concentrating on the issue at hand, the basic solution recommended was to
- copy the table and open in design view
- remove the autonumber field (as shown in a previous post, all existing entries of concern were ascending and consecutive numbers)
- save and switch to datasheet view and add the needed values
- open again in design view and reinstate the autonumber PK field with random option

To keep me from making a long story about the db, I'll just say that it's a replication db thus the need for random autonumber. I saw no evidence of this field being used as meaningful user data; just being related to tblInterview in the customary fashion. Thus I still don't see the importance of the PK autonumber field being sequential. Any place that the number is observed in use might actually be the InterviewTypeNumber and not the PK field - they just happen to have the same values.

Regardless, I've proposed the above solution. It must be noted that the only reason the approach could be taken is that every current InterviewType value was the same as the existing autonumber (e.g. type 3 was ID 3). It seems the behaviour of a random autonumber field added to existing data is to make the values sequential at first.