New Id Generation

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • natural
    New Member
    • Aug 2007
    • 21
    #1

    New Id Generation

    Good Mornining

    I was hoping someone could possilbe assist.

    I have three Table

    1 - Candidates, (Where New Candidates Will be entered)

    2- Offer (Where successfull candidates has been appended - Or new Offer Created without being in the Candidates Table

    3- Where successfull candidates and offer records has been appended But also new record can be created from this table.

    I currently have the ID field for all three tables on Autonumber, But as not very wise desicion,

    If my candidates get appended to the offer table And if a new offer has been generated, i might have duplicates etc...

    I was wondering if someone could please just guide me in the right direction.

    I was hoping that i could generate a id for all three tables from one location. Due to the fact that the records move around,

    Is it possible to do that,

    Maybe have a NewID table and when a new record is being generated, it gets one that is not yet in use.

    I have to keep the tables seperate (Know it is best practice to keep them all in on, but with different status, but unfortunately cant)

    Any adivise would be appreciated,
    Tags: None
    • NeoPa
      Recognized Expert Moderator MVP
      • Oct 2006
      • 32669
      #2
      AutoNumber values cannot be transferred from one table to another anyway. Each table they are added to would create a new value for the record related to that table.

      Yes, storing them all in a single table is the first piece of advice I would give. Assuming that is a no-go though, let's move on.

      When adding a new record, include a short bit of code (or even logic in SQL) that finds the Max() of the existing records for the class you're interested in (that may be across more than one table), and handling the situation where none is found of course, and add 1.

      If you need more precise help you will need to explain your situation a bit more clearly I'm afraid (be careful to express your ideas without contradictions) .

        Comment

        • natural
          New Member
          • Aug 2007
          • 21
          #3
          Thank you very much for the response, and my apologies for the vaugness.

          So if I have a table called TblNewId
          I will have 3 tables that will be looking at it.

          TblCandidate
          TblOffer
          TblEmployee

          All three would once "NewRecord had been Selected" Look at the table NewID and then make us of max +1

          But what if tblCandidate used the last max


          How would i achieve this, via a query..

          Any assistance is greatly appreciated.

            Comment

            Previous template Next
            Working...