Matching up import data to records

[Travis]

Importing data into Access isn't much of a problem, I've already
figured out Transfertext.

The data I previously imported was easy to use because the information
in the import table had the same primary key as the records I was
wanting to update.

e.g. when importing stock prices, if you use stock tickers as the
primary key (which is perfectly appropriate to do since stocks have
unique tickers), matching the imported data against the existing data
for the update record is simple.

But now I want to set up some imports which have different keys. I
want to import transaction data from various fund managers. The
primary key in the data I am importing is the account number given to
each client by a fund manager. Because it is possible that the same
account number could be used by different fund managers for different
clients, I can't use the fund manager's account number as a primary
key.

Instead, I use an autonumber primary key for my own records and in the
same table I have fields which include the fund manager and the account
number given by the fund manager.

I can, at the time of import, tell my application which fund manager it
is importing (in fact I have to, since the various data files
distributed by fund managers are all in different formats), so its a
matter of matching the fund manager's account number against my account
number so I can do the update or append.

But I've got no idea how to do it!

Any suggestions?

Travis

 

[John Vinson]

...
But now I want to set up some imports which have different keys. I
want to import transaction data from various fund managers. The
primary key in the data I am importing is the account number given to
each client by a fund manager. Because it is possible that the same
account number could be used by different fund managers for different
clients, I can't use the fund manager's account number as a primary
key.

You can use a *TWO FIELD* primary key consisting of the fund manager's
ID and their account number. Surely each manager has unique account
numbers within their system!

Instead, I use an autonumber primary key for my own records and in the
same table I have fields which include the fund manager and the account
number given by the fund manager.

That's certainly a reasonable approach.

I can, at the time of import, tell my application which fund manager it
is importing (in fact I have to, since the various data files
distributed by fund managers are all in different formats), so its a
matter of matching the fund manager's account number against my account
number so I can do the update or append.

But I've got no idea how to do it!

If you don't use the two-field primary key, at least define a unique
Index on the two fields (use the Indexes toolbar tool, looks like
lightning hitting a datasheet). Then you can simply join the two
tables on the two fields, dragging the fund ID to the fund ID, and the
fund account number to the fund account number.

John W. Vinson[MVP]

 

[Larry Linson]

Did you consider using a combination of the fund manager field and account
field as the key? That certainly makes a good deal more sense to me that
trying to match fund manager and account to an arbitrarily-assigned key such
as an AutoNumber.

Larry Linson
Microsoft Access MVP

 

[Travis]

Did you consider using a combination of the fund manager field and account
field as the key? That certainly makes a good deal more sense to me that
trying to match fund manager and account to an arbitrarily-assigned key such
as an AutoNumber.

Actually to be quite precise, I have no index field at all for
accounts.

What I do have is a table of transactions, where units or shares are
bought and sold. The table has the following fields:

ID Autonumber (primary key, this is the ID of a transaction)
InvestmentID Text (foreign key to a table of investments, the
investments table includes details of who the manager is)
OwnerID Number (foreign key to a table of clients
PercentOwned Number (percent format) (Percentage owned by that Owner, a
second record which is an almost duplicate may have another OwnerID, or
maybe there won't be a second record because it is possible to own a
part share in something and not to care who owns the other bits.)
AccountNumber Text (this is the fund manager's account code)
NumUnits Number (number of units transacted)
TransactPrice Currency (Price units were transacted at)
TransactType Text (Buy or Sell)

The manager is looked up from the Investments table.
The current unit price is in the Investments table and the value of the
investment plus capital gains and other performance data are all
calculated fields.

It should also be noted that sometimes I don't have a manager account
number. For instance if someone buys a property there won't be an
account number associated with the investment property, so either I'd
have to make something up or leave it blank.

I'm open to all suggestions about:
a) more logical ways to lay out the table.
b) whether a two field primary key would work in this case, and if so
how to do it.
c) how to achieve my goal of importing transaction data and other
account information (like commission payments, which I have to rebate)
given the nature of the data I'm using.

Travis