Appending something to an auto incrementing field.

Question

Appending something to an auto incrementing field.

Michael Wilkins

SSC Enthusiast

Points: 109
More actions
December 16, 2008 at 1:44 pm

#197315

I'm trying to find a good way to use an auto incrementing field as a PK but I need to append something to it. The reason why is because I have 2 tables that will be using auto incrementing fields as their primary keys but I want to append each one with a different letter or set of letters so I can differentiate between the two.
I'm pretty new to creating complex databases so if there's an easier but not too complicated way of doing this I welcome all suggestions.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

It depends on why you want to do that.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

tgarland Hall of Fame Points: 3367 More actions · Answer 2

If I understand your question correctly, I would start the incremental column on table A at 1 and increment by 2. I would start the incremental column on table B at 2 and increment by 2. This would give you all odd numbers in table A and all even numbers in table B.

Michael Wilkins SSC Enthusiast Points: 109 More actions · Answer 3

Well, I have different types of assets I want to track in each table, I don't want them all in a single table.

If that doesn't answer your question, could you be a little more specific?

Michael Wilkins SSC Enthusiast Points: 109 More actions · Answer 4

Yeah, that makes sense, and it's simple. I really hate it when these things aren't as complicated as I think they are.

Thanks, I will try that.

Steve Jones - SSC Editor SSC Guru Points: 736101 More actions · Answer 5

You could use a GUID, which would keep things separate, though not terribly readable.

You could also use a computed column, take the PK, add an "A", "B", whatever to it.

I do like the even/odd method if you are 100% sure you won't get a third one.

Michael Wilkins SSC Enthusiast Points: 109 More actions · Answer 6

These two tables will contain the bulk of my data, any additional tables could use the combined PK method.

If I decided to go with the combined PK method, how exactly would I do that? Most likely what I'll end up with is an Access 2007 front end so folks can use the forms they're use to and such.

I was thinking I could have a form with an "Add" button on it so when they click add SQL is performed that would insert the data into the SQL Server table.

Problem is I don't know how to do that, and I'm not sure how I would take two entries in the form and combine them into one item into the PK column. I realize I'd concatenate the two fields into one, but inserting them into the PK column is new to me.

Steve Jones - SSC Editor SSC Guru Points: 736101 More actions · Answer 7

If you mean the computed column, you'd define that in SQL Server as the combination of the PK and another column or a static value and that goes into a new column. Access wouldn't insert into the identity, PK, field or the computed one.

IF you use identities with two ranges (one increment by 2 starting from 1, the other incrementing from 2 starting at 2, then you define the identity increment when you build the table (and the seed, or initial value) and don't have Access insert into that field.