Linking an ID number to a name

[bkurz]

I want to have an autonumber ID number assigned to every entry, however is it
possible to recognize when the name is repeated and put the original ID
number in without having to do this all manually?

 

[KARL DEWEY]

You can create an index in the table and set to unique.

 

[Jeff Boyce]

It sounds like you are saying you want Access to "know" when a field in a
new record has the same value as that field in an old/existing record, then
re-use the AutonumberID.

Not only isn't this possible (autonumbers are unique), but not desireable in
a well-normalized relational database.

You're asking a "how" question (how do I do this?) without telling us much
about "what" (what data structure do you have? what are some examples of
the data stored? what domain are you working in -- teaching, oil field
maintenance, astrophysics, ...).

It helps folks who volunteer here to have a bit more context...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[TedMi]

Define what you mean by "the name is repeated". Do you want to assume that
every John Smith is the same person?
-TedMi

 

[bkurz]

I work in a school and keep records of students I have seen, each time I see
them. I assign a number to each one for tracking purposes. I do not want to
use their student ID as assigned by the school for confidentiality reasons.
Students may be seen several times, and each time they are seen it is
entered. I want to know if it is possible instead of me looking back for
each student and what their number is to input it, if there is a way for
their first and last name to be recognized and their ID number automatically
inputted. Because we are a school of less than 600, we have not yet had to
worry about duplicated names.

 

[TedMi]

Well, duplicates are likely to happen at some point, so the wise move is to
be ready for them. Here's what I use in a similar situation where I need to
look for existing names:
You should have:
A Student table containing names and the autonumber ID's asssigned by you,
and any other non-changing information about the student (e.g. date of
birth, parents' contact info, etc. - nothing about your meetings with
students)
A Session table which contains a record for every time you "see" a student.
This should contain the numeric ID and other data pertinent to *this
meeting* e.g date/time scheduled, date/time started, date/time ended,
reason, outcome, follow-up, etc. To ensure that you enter the data for each
session only once, you might want to create a compound unique index on ID
and date/time scheduled.

Create a form for entering and displaying session information. To enter
session data, open the Session form and put it in data entry mode to create
a new instance of a session record. Enter the student's name and click a
command button.
The Click event searches the student table for name matches, and if not
found, creates a new entry in the student table, assigning it the next
autonumber, and returns that number to your Session form. If found, the
procedure populates a listbox with *all* names that match the input name,
along with ID's. You then select the appropiate one to be entered into the
session form.

-TedMi