event - primary id link by search duplicates

I have a database where if a customer has more than one record, if the customers "id" number is input into a field called "primary id", the database will run an event procedure that populates an outcome status that prevents an advisor from marketing the customer twice. I am looking to update the field called "primary id" automatically when a customers telephone number is duplicated by the database either when inputting or by running a query. This should automatically be populated by the first id number that matches the telephone number in the "id" field.

Any clues?

When the user enters the phone number (which I think you'd have as a text field? - stripped of spaces), then search for that phone number.
If you get any records then you add the first records ID to your new record. Otherwise its a new customer.

For storing - if its not required immediately, you could perhaps run an update query that would put the ID in if there is one, again matched on the telephone number.

For queries, you just need to insert the table again (in querybuilder) and it will become an alias. Then link on the telephone number. You may need to make it a sub query to get just one, or put it into the selection of fields bit such as:

Code:

---

Select table1.id, nz((select [qt1].[id] from [table1] as [qt1] where [qt1].[telephone]=[telephone]),-1) as primaryID from table1

---

No idea if that would work, but you may be able to use it to try out.