Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Location
    New York
    Posts
    2

    Question Unanswered: Be Gentle: Newbie Question

    Using Access 2000/02, would like to set a primary key so that it has a format similar to:

    CONF02xxxx, where "CONF02" is a constant prefix and xxxx is an auto-incremented number.

    So, records would be:

    CONF020001, field1, field2
    CONF020002, field1, field2,
    yada, yada, yada.

    How would I do this? Autonumber gets me a unique, incremented number, but my db has alot of tables, and I'd like each ID to have a text prefix that would let you see during a quick eyeball what id relates to.

    At first I thought I needed to do a composite key, but that seems to add a layer of complexity to this that's completely unneccessary. (PK1 = "CONF02"; PK2=autonumber)

    Could someone please send a newbie in the right direction?

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Well it looks like you want this field to be displayed in some form or the other which really defeats the purpose of it being a primary key.

    As a general rule of thumb, primary keys should never really be seen by users. It's simply there to uniquely identify each record.

    So, since you are wishing the field to contain an auto-incrementing numeric figure, then why not use the autonumber only as a primary key, and simply create another field to display whatever you want when displaying data?

    In terms of needing to 'quickly eyeball' what each record relates to, that should depend on whatever context you are displaying your data. Data should never be 'viewed' from tables. In a correctly normalised database, tables quite often make no sense whatsoever. Queries are the tool for that job.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Jul 2002
    Location
    New York
    Posts
    2
    True enough. Actually, what you suggest is where I was in the first place and then started thinking about my end users. Bad mistake. They are supremely untechnical and I was anticipating things they were going to ask - and heading in what you've confirmed is a bad direction.

    Thanks.

  4. #4
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    No Probs.

    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  5. #5
    Join Date
    Jul 2002
    Posts
    7

    solution

    Greetings from Ohio! I think this should work for you...

    Leave the PK as an autonumber. If it's going to be visible to the end-user for whatever reason, just format the controlsource property to where it displays YOUR string PLUS the primary key value. It should look like this:

    ="CONFO" & [PrimarykeyField]

    That should do it for ya...

  6. #6
    Join Date
    Jul 2002
    Posts
    7

    oops!

    I guess that WON'T help you, because I just reread where the whole purpose of the prefix is to give you a quick "eyeball" of its origin table. I guess I need to read the questions better...

Posting Permissions

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