| | |
|
|
|
Update, Append, and ID field |
|---|
| message from Techsupport on 24 May 2004 |
Good morning, all.
Here's my question: I have a CustomerDB table, which lists all the
customers and titles they subscribe to. There is of course, a CustomerID
field (autonumber). I created an Update query to search all the customers
whose subscription has expired. If it is, it would insert a checkmark into
a Inactive Account checkbox and then I created an Append query to copy these
customers to an ExpiredAccounts table and a Delete query to remove these
accounts from the CustomerDB table. Everything is working fine, but here is
the dilemma: if one of the customers whose account is inactive decides to
renew their subscription, I would need to copy the record back to the
CustomerDB table, which is fine using the Append query, but the problem
would seem to be the CustomerID field. Once the record has been deleted
from the CustomerDB table, and new records have been added, if I want to
copy the record back from the ExpiredAccounts table to the CustomerDB table,
it would create another CustomerID, which might already be in use. There
are other tables which use the CustomerID to relate customers' information.
Does anyone have an idea on how I can setup Access to 'remember' all the
CustomerIDs used (even if they are in other tables)? Thanks, Antonio
|
| John Vinson replied to Techsupport on 24 May 2004 |
Two suggestions:
- In your archive table, append the Autonumber value into a Long
Integer CustomerID field (rather than using another autonumber). If
you then run a second append query to append it back, you can append
the existing Long Integer value into the autonumber field and it will
store that value rather than generating a new autonumber.
- Possibly better: don't bother with the second table at all. Just
mark inactive customers in the customer table using the yes/no field;
base your forms and reports on a Query selecting only active
customers. Then reactivating a customer becomes a simple matter of
flipping that field back to Yes. If there is a non-unique Index on the
field, you won't appreciably lose performance.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
|
Archived message: Update, Append, and ID field (MS Access Forms)
