How to create special auto numbering

[Slink]

I've got a challenge that many may have when importing a database in
mysql and not sure which is the better tool to use as I'm using Excel
but can use Access. I have a great list of doctors that also has their
specialties. Every doctor listing has a listing ID. The problem is as
follows - if a doctor has more than 1 specialty, there is a second
listing that has its own unique ID. For this to work, this needs to be
a one to many relationship where there is one unique ID for listing
and that listing is used to display in different categories.

Let's say my spreadsheet is set up as follows:

ListingID ListingName Address ListingSpecialty
111 Joe 1 Park Heart
112 Jane 2 Broadway Blood
113 Jim 3 Main Nose
114 Jim 3 Main Ear
115 Jim 3 Main Throat


So I'd like to create a function or query that will see if the cell
for the name of the doctor is the same (ListingName and Address), then
it would use the same listing IDnumber. This way if I import it into
an application, the application will read in the same listing number
and will automatically associate the "duplicate" IDs as being a second
specialty. This application already knows what to do with the
duplicate ID and can read the ListingSpeicalty field and create the
multiple association of that listing. So in this example, Jim is
listed three times. The numbers are unimportant and can be renumbered.
But what I want to do is have the numbers renumbered as 111, 112, 113,
113, 113 because Jim is the same listing.

At first I thought of converting names and address field and combine
and convert to integers but I'd have to think that through and there
must be a better way, macro, query, etc. I've got about 200,00 doctors
so that's why manual solutions wont work. Any ideas? Suggestions much
appreciated!

 

[Larry Linson]

Actually, Slink, you don't have a one-to-many relationship; you have a
many-to-many. Many doctors can have the same specialty, and many specialties
apply to many doctors. And, to implement that in Access, you'll use a
"junction" or "intersection" table with records each pointing to ("with a
foreign key to") a single doctor record and a single specialty record.

Larry Linson
Microsoft Office Access MVP

 

[Slinky]

If possible, I would do this upstream in the database. Create a separate
view (select query) VW_1 to return MIN(ListingID) GROUP BY ListingName,
Address;

In another view VW_2, join VW_1 to the main table on ListingName and
Address. Return Listing ID from VW_1 and the remaining fields from the
main table as your final data.

Note if the ID numbers are truly unimportant (and they shouldn't be),
you won't mind that they will no longer be sequential. But they will be
more meaningful than arbitrarily renumbering, as you can tie them back
to a record in the source data.

Guys - thanks so much!!! But I actually did something very, very easy.
It worked on my first sheet - I just used an if statement:

=IF(AND(A5=A4, B5=B4),D4+0,D4+1)

This translates if the name column is equal and the address column is
equal then the record is the same so don't increment the number. If it
is different, new record so add one. That worked well.

To get the records the same on the flat file, it was also easy. Create
two new columns and run the formula. If number is the same (same
record) copy the value of the specialty cell to the cell adjacent to
the record so it looks like:

Jim 3Main Nose Ear

Now I can import all the values this way.

The only problem I'm having now is that while the formula worked in
one spreadsheet, it's not working now. It makes no sense because it's
as if the formula is ignoring the value in the cells... weird.