Re-use same id

[georgewash]

I have a need to determine if a customer already exists in the table occurence and if so to use it's original id. Here are the tables:

Customer:

Name

Associate Status

Coupon Codes:

Name:

Associate ID

Coupon $ Amt

Coupon Code

Currently one selects the name in Coupon Codes from those identified in Customers as with a positive Associate Status. The Associate ID is a serial id generated on creation. Then one enters the Coupon $ Amt manually and the Coupon Code is generated by calculation.

The problem is an associate customer can have more than one coupon code, but should retain a constant Associate ID. Obviously the Associate ID can't be a simply serial number.

My thought is an if then statement but I am stumped on how to avoid creating recurvsive problems. I am also unsure on how to test if the Name has already been used.

Thanks.

[Raybaudi]

Place the Associate ID into its own table: Customer

Obviously the Associate ID must be a simply serial number

[georgewash]

Not sure how that is going to help.

Is there a way to sum all of the customers with Associate status and then add one to the total to create the new ID?