Importing Data and adding a prefix to txn numbers

[ChuckW]

Hi,

I have two Access databases that were created using a
program that takes data out of Quickbooks and puts it
into Access. Each database represents a store. There is
both a Customer Number and a Transaction Number for each
database. I would like to create a separate Master
Access Database and import certain fields from some of
the tables in each program. I also want to add a 3
letter prefix to both the customer numbers and the
transaction numbers in order to distinguish between the
two stores. For now, I would either have one of two
prefixes: LFC or ESS added to the customer numbers and
trx numbers. These are codes for the two stores. Can
anyone give me a starting point and how I go about doing
this?

Thanks,

Chuck

 

[Allen Browne]

Hi Chuck

Instead of appending a prefix to the number fields, the simplest solution
might be to add another field for StoreID. For the primary key, you can use
the combination of StoreID and the number, by selecting both fields in table
design view, and clicking the Key icion.

 

[ChuckW]

Allen,

Thanks for your help. Should I create this StoreID in my
original database that has all of the transactional
information and then pull it along with the my
transactional info into the new combined database? Also,
I have someone who can help me write some scripts.
Should I create macros in the new combined database along
with some macros that import data from the transactional
databases?

Thanks,

Chuck

 

[Allen Browne]

Up to you. It may make sense to keep the same structure between all the
databases, or it may make sense to use a Number field (in place of the
AutoNumber) along with the StoreID in your central one.

If you have someone to help you, that's great. The TransferXXX actions will
probably help. In my experience, you generally need VBA code rather than a
macro to do this efficiently, as there are lots of things to check and lots
of things that can go wrong with importing processes.