Import Table / need to create a unique Key

[Lance]

Hello,

I am having an issue with creating an import table as an intermediary
to processing the data and inserting it into my main table. A bit of
brief info on the data imported, and where it is going.

I have all of my information coming in from CardScan, an online contact
management database.
The fields include First Name, Last Name, Organization Name, address
information, creation time, and modified time.

I am importing into a table, which I have so aptly called
tblCardScanImport.

I am then exporting this information in two ways. First, I pull every
organization name from the list, and add them to a table called tblOrg.
This assigns them an autoID internally. Following that, I then go back
to the tblCardScanImport, and add each individual (i have dictate this
by the presence of first name and last name fields not null) to a table
called tblClient. The reason I must create the Org first is because
each client has the option of referencing to their employer. Since my
links are autonumber associations, i obviously need to create the
organization before the client.

I am now running into one main problem.

1) I have multiple contacts with the same Organization in Cardscan.
Cardscan however, has no 'key' for organizations, since that's not its
focus. I was going to use the contact "creationdate" as a 'key', but
since i have multiple "creationdate" fields for each org, that is not
going to work. What should I do to create a key for the organization?
When I mean key, I am not talking about an internal key, autonumber
does that. I mean a way to track the organization as related to
cardscan, and have it update if someone updates the organization
information w/in cardscan (such as a phone number change).

Thanks

 

[Vincent Johns]

Hello,

I am having an issue with creating an import table as an intermediary
to processing the data and inserting it into my main table. A bit of
brief info on the data imported, and where it is going.

I have all of my information coming in from CardScan, an online contact
management database.
The fields include First Name, Last Name, Organization Name, address
information, creation time, and modified time.

I am importing into a table, which I have so aptly called
tblCardScanImport.

I am then exporting this information in two ways. First, I pull every
organization name from the list, and add them to a table called tblOrg.
This assigns them an autoID internally. Following that, I then go back
to the tblCardScanImport, and add each individual (i have dictate this
by the presence of first name and last name fields not null) to a table
called tblClient. The reason I must create the Org first is because
each client has the option of referencing to their employer. Since my
links are autonumber associations, i obviously need to create the
organization before the client.

Since you've already done this, I suppose this comment is unnecessary,
but you could have ...
- imported your data into the Table first (including the
[Organization Name] field and without the [autoID] field), then
- used a Make-Table Query to create the [tblOrg] Table, including
[Organization Name], and specifying Unique Values to avoid duplications,
- added the [autoID] Autonumber primary key to [tblOrg],
- added a Number type foreign key to [tblCardScanImport] to link to
[tblOrg],
- used an Update Query, linking [tblCardScanImport] and [tblOrg] on
their [Organization Name] fields, to make the Number-type foreign key in
[tblCardScanImport] match the [autoID] value in the matching [tblOrg]
record, and
- deleted the [tblCardScanImport].[Organization Name] field, as it's
no longer needed.

Follow similar steps for [tblClient], but using [first name] and
[last name] fields instead of [Organization Name].

It may seem tedious, but if you have lots of records, it's easier
than some other methods such as entering them via keyboard, and you may
need to do it only once. After the first time, you'd use the linked
Tables to enter [tblOrg].[Organization Name] values as needed.

I am now running into one main problem.

1) I have multiple contacts with the same Organization in Cardscan.
Cardscan however, has no 'key' for organizations, since that's not its
focus. I was going to use the contact "creationdate" as a 'key', but
since i have multiple "creationdate" fields for each org, that is not
going to work. What should I do to create a key for the organization?
When I mean key, I am not talking about an internal key, autonumber
does that. I mean a way to track the organization as related to
cardscan, and have it update if someone updates the organization
information w/in cardscan (such as a phone number change).

Thanks

I agree that it appears you have a problem, one not related to Access.
What happens if you have three records in Cardscan relating to "Red
Cross", and someone changes that organization's phone number in only ONE
of those three records? If I understand you correctly, this looks like
a problem in the Cardscan database's design.

Do you intend to keep the Cardscan version as an active database? If
so, then what you're doing in Access is likely to be wasted effort,
unless you can somehow link it to Cardscan. If not, then for duplicate
records, you'll need to examine the contents and decide which of the
duplicate data you want to store in [tblOrg] in Access. When I do this,
it often involves phone calls or emails to disambiguate the data. Good
luck.

If all the data match, you're lucky. Just use the [Organization Name]
field as if it were a primary key. (I know it's not unique, but by now
you've established that the data match, so you can ignore all but the
first matching record.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Lance]

Vincent,

Thank you for your lengthy reply, you put some work into it!

Yes, the CardScan database is a disaster, because it is very
short-sighted, and thinks the only thing that is important is the
contact name, and leaves no options for tracking anything else contact
related. Unfortunately for me, try as I might, my superiors seem to
think CardScan is an irreplaceable solution. So, I must work with it.

Thus far, I have somewhat alleviated the problem. Instead of assuming
that ANY data is attached to either the client, or the organization, i
have created a new set of contact fields in MY database strictly for
imports from CardScan. That way I don't have to deal with whether it is
organizational or personal data. It just gets attached to that person
as CardScan contact info. Then the end user (my company) can decide
which contact information that client wishes to use (personal,
corporate, or cardscan) to be contacted via some nifty toggle buttons.

My only problem now lies in the Organization name. I think since I
don't have to track any "information" with it, I am going to be able to
perhaps build a query of distinct Org names, and have that be the only
thing that gets imported. If I have any problems with this, I'm sure i
will be back.

Thanks again for your input.

Lance

 

[Vincent Johns]

Vincent,

Thank you for your lengthy reply, you put some work into it!

Yes, the CardScan database is a disaster, because it is very
short-sighted, and thinks the only thing that is important is the
contact name, and leaves no options for tracking anything else contact
related. Unfortunately for me, try as I might, my superiors seem to
think CardScan is an irreplaceable solution. So, I must work with it.

Thus far, I have somewhat alleviated the problem. Instead of assuming
that ANY data is attached to either the client, or the organization, i
have created a new set of contact fields in MY database strictly for
imports from CardScan. That way I don't have to deal with whether it is
organizational or personal data. It just gets attached to that person
as CardScan contact info. Then the end user (my company) can decide
which contact information that client wishes to use (personal,
corporate, or cardscan) to be contacted via some nifty toggle buttons.

My only problem now lies in the Organization name. I think since I
don't have to track any "information" with it, I am going to be able to
perhaps build a query of distinct Org names, and have that be the only
thing that gets imported. If I have any problems with this, I'm sure i
will be back.

Thanks again for your input.

Lance

Perhaps you don't need Access to do this, but your Access Table of
unique [Organization Name] values could also contain a key linking to
some selected CardScan record that contains the rest of the information
on that organization.

Have you considered revising the structure of the CardScan database
itself? Knowing nothing about CardScan, I can't give specific advice,
but if it's a relational database system, it ought to be able to support
its own table of [Organization Name] values and related information, to
which you could link information in your [Client] Table and elsewhere.

If CardScan won't let you do this easily, you might import the whole
mess into Access, make your corrections in Access, then export the
revised Tables back to CardScan and maintain them there. (Access will
have done its job, and you can pack it away until the next time it's
needed.) You might have to do this at 2:00 am some Sunday morning, so
you could put the CardScan system off line while you're doing your
Access magic, since any changes made after you copied the CardScan stuff
to Access would be lost. :-( It's easiest if you can be pretty sure
that there wouldn't be any such changes in the first place. You'd
probably want to run the process a time or two before the real
conversion, to be sure you could do it quickly and easily when the time
came to do it for real.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.