New Id Generation

[natural]

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,

[NeoPa]

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).

[natural]

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.