If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing Data and adding a prefix to txn numbers
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 |
#2
|
|||
|
|||
Importing Data and adding a prefix to txn numbers
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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 |
#3
|
|||
|
|||
Importing Data and adding a prefix to txn numbers
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 -----Original Message----- 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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 . |
#4
|
|||
|
|||
Importing Data and adding a prefix to txn numbers
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|