A question about tables

[Chalkie42]

Hi again,

Could someone give me some advice about tables please?

My database (A2010) has a table that contains client details with a primary key that is the ClientID. The ClientID is automatically generated in the form when the client is built. The customer wants the database to reflect as closely as possible the clerical system that will be replaced. I need to create a waiting list for potential clients (not all of whom are converted to clients) but the ID attached to them is different, typically prefixed by "WL". So the question I have is this: do I create two separate tables and find a way of moving a record from 'waiting list' to 'client' programatically or do I include all the records in one table and simply use a field to identify the status? The issue I have is that to do the latter I would have to re-allocate the ID which isn't possible as it is the primary key.

Sorry for the wordy post - I can't think of a shorter way to explain what I am looking for.

 

[catalin.petrut]

I suggest one table, a status field (waiting list/approved). I don't understand why you need to re-allocate the ID. I'm confused.

 

[Chalkie42]

Yeh, I suspected this might cause some confusion. As I said, the customer wants the database to mirror their current clerical system. They don't allocate client IDs until they transfer from the waiting list to the client list. I know it doesn't make a lot of sense.

 

[catalin.petrut]

In that case, for avoidin redoing the db, you can add a field where you will insert the id given by the customer. The autonumber remains pk and the customer id will be present only for activ clients, field required if client status is changed from waiting list to active.