Primary key indexing problem

[SoClueless]

I have merged a few excel sheets into my access table, which is a flat list of names with a variety of characteristics. Since then, it was decided that this new list would only be used annually and that new names would only be added to the original, smaller list for our normal mailings. So, I created a field named "active", gave it a checkbox on the form so that we could pull only those records desired for our next regular mailing.

Since then, if I try to add a new name to the large list, the auto primary key comes up as the next number would be on the smaller table, which looks like a duplicate in the primary key field, so new record cannot be added. Does anyone know where I can go to fix this problem?

Thank you.

 

[CJTyo]

You should have just the one list and fields that indicate which mailing the names go to - that way you don't have duplicate entries, which is kind of the point of a DB.

For instance.

You have your name, address fields. Then you have a field called Annual that can be a yes or no, then a field called Monthly (or whatever) that can, again, be a yes or no. If you have more mailings you have more fields. You run queries based on the mailing fields that contain a YES for that mailing type.

Make sense?

You don't want two tables, a big one of everybody and a small one of just some people.

C
*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[SoClueless]

Yes, CJ, thank you and I understand. This is why I do not want to use the old, smaller table anymore. But for some reason, when I try now to add new names to the new, larger table, the primary key for the new name is, for ex. 5,556 (as if it's being added to the original table)when it should be 11,668. So of course access will not allow me to add it to the list, b/c that id is already in there. Do you see the problem?

 

[CJTyo]

Yes, when you add the new names from smallTable to bigTable DON'T move the autogenerated ID field - just everything else. That way bigTable will just give them whatever number is next and smallTable will have its numbers.

If you need a constant across both tables it should NOT be the primary key auto number for either table - it should only be generated on the small side and just be a regular old number field on the big side.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[SoClueless]

Well, somewhere along the line I must have done the big no-no and this is what's happening. So, is there any way that you know of to fix the situation???? i hope?

 

[CJTyo]

The easiest thing to do is move/copy (whichever you're doing) the records from the smallTable into the bigTable WITHOUT the smallTable autogenerated number and just let the bigTable give them a number.

Do you have to have a unique number that is the same across both tables?

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[SoClueless]

You see, CJ, the problem is that I have ALREADY moved the records from the small table to the large table. I now cannot add any new individual records to the large table, b/c it automatically assigns it a number that is already in the table. I'm not sure exactly what I did to cause this, because it did not happen immediately after the merge.

 

[CJTyo]

What I meant to tell you to do after setting your auto number to long integer is to make a NEW FIELD, set that to AUTO, and let Access number everything. THEN delete the old field, or keep it for posterity/as your non-primary key ID or whatever.

Maybe it's lunch time.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[SoClueless]

Thank you cj, I will try tonight and let you know what happens.... i'm hopeful

 

[CJTyo]

Sorry for getting scrambled. We're training a new person so my mind is on a hundred different things. I probably shouldn't be trying to be helpful in this muddy mental state!

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[SoClueless]

You are really sweet to spend all this time helping me for nothing, have to go to dinner and will be back and try tonight if i can. If this works, I'll owe you that coffee!! Thanks.

 

[CJTyo]

No problem. Lots of super helpful people have worked with me around here so I'm happy to give back, so to speak.

Enjoy your dinner and I'll think enviously of steaks and other wonderful meals while I munch my bologna and cheese at my desk. <wistful dreamy look>

I'll take that virtual coffee when we get this figured out. ;-)

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[SoClueless]

Hi CJ,

I'm smiling! What I wound up doing, after changing to regular number and going back, is deleting the primary key field, since no relationships were dependent on it and then i added a new auto number field back in and made it a primary key and zing it seems all to be behaving ok when I put in a new record....and you get a jumbo coffee with whipped cream and a donut!!! Thank you so much for saving the day!! You can't even imagine how much you helped...

Peg

 

[CJTyo]

Peg,

I'm glad it worked out.

Be well,
C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.