If Function

[Robert]

I hope this isn't a dumb question and I hope I explain it a way you
can understand.

I have two tables in Access which I am trying to transfer to a new
database. The tables are in the following format. The ID fields are
autonumber fields. The reason the numbers are no longer in sequence is
because rows have been deleted over time. More than one contact can be
assoicated with a companyid, hence two contacts having a value of 23
in the companyidlookup field in the original contacts table.

original companies table original contacts table
companyid company contactid contactname companyidlookup
3 companyA 4 john smith 3
23 companyB 5 jane smith 23
24 companyC 9 john doe 23
30 companyD 10 jane doe 24
.....

new companies table new contacts table
companyid company contactid contactname companyIDlookup
1 companyA 1 john smith 1
2 companyB 2 jane smith 2
3 companyC 3 john doe 2
4 companyD 4 jane doe 3
......

I can create the new companyid field and the contactid field by
autofilling the fields after the data is transferred to the new
database. My problem is I have to change the companyIDlookup value in
the "new contacts table" to match the new companyID value in the "new
companies table" based on the companyid value in the original table.

Basically I am trying to figure out a way to use an Excel formula to
match the CompanyID value in the new contacts table to the CompanyID
value in the new companies table based on the values in the original
companies and contacts tables.

Is this possible? I'm sure it is just not very good with Excel
formulas. Any assistance is greatly appreciated.

Robert

 

[onedaywhen]

I hope you don't mind me agreeing with you that your question is a bit
'dumb'. I don't understand why you need to reassign your IDENTITY
(autonumber) keys.

IDENTITY is a last resort when there is no natural key to uniquely
identify your data. However, when the data is people there usually
*is* a natural key e.g. social security number, email address, Windows
userID, employer's ID number, etc. And aren't companies required to
have unique names anyhow? When an IDENTITY column is used it is merely
to give arbitrary uniqueness to your data, a primary key for the sake
of having one. It serves no other meaningful purpose and would not be
exposed to the user.

So what's the point in changing 3, 23, 24, 30 to 1, 2, 3, 4 in your
new database? As you point out, the sequence will not remain over time
anyhow because of rows being deleted, the IDENTITY losing track during
transaction rollbacks and making huge leaps to compensate, etc.

You have the chance to start over, so concentrate your efforts in
replacing your meaningless INDENTITY columns with real keys.

--

 

[Robert]

I realize I could leave the numbers as is. I was asking so I could
learn how to do something like this in Excel not so much from a
database design aspect.

I really see no need to create a different unique key. All I'm keeping
is a database of a couple of thousand company names and addresses and
their associated contacts. So it works in this situation.

Thanks anyway.h



(e-mail address removed) (onedaywhen) wrote in message

 

[Jonathan Rynd]

(e-mail address removed) (Robert) wrote in

My problem is I have to change the companyIDlookup value in
the "new contacts table" to match the new companyID value in the "new
companies table" based on the companyid value in the original table.

You want the MATCH function.

assuming that each table is in its own sheet, and data starts in cell a2 of
each sheet.
Put this in NewContacts!C2
=MATCH(Contacts!C2,Companies!$B$2:$B$100,0)

and fill down.
You'll then have to copy and paste special/values.