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
|
|||
|
|||
concatinating fields
I have a table that I import from an outside source. This table contains
information on products by serial number. Because of the way it comes from the original source, the serial numbers are often repeated with different information attached. For instance the first occurance may have customer name and address, the second may have the customer's email, phone and fax. Is there an way to merger each of these occurances, so that the end product does not have duplicate serial numbers? I am using Microsoft Access 2000 Thanks |
#2
|
|||
|
|||
concatinating fields
On Thu, 17 Nov 2005 05:05:39 GMT, wrote:
I have a table that I import from an outside source. This table contains information on products by serial number. Because of the way it comes from the original source, the serial numbers are often repeated with different information attached. For instance the first occurance may have customer name and address, the second may have the customer's email, phone and fax. Is there an way to merger each of these occurances, so that the end product does not have duplicate serial numbers? I am using Microsoft Access 2000 Thanks You'll need a table with the Serial Number as a Primary Key; as each imported table comes in, you'll first run an Append query to add altogether new records to your table, and then run an Update query joining the import table to the master table by serial number, and updating the master table's fields to the corresponding import table. Can you assume that if (say) one import file has a customer phone, and a second import file has a DIFFERENT phone for the same customer, that it is a correction which should override the data you have already stored? John W. Vinson[MVP] |
#3
|
|||
|
|||
concatinating fields
Thanks for the reply
no and therein lies part of the problem. The customer want to add fields to the table. I have some serial numbers that show two different companies own the item, but my customer isn't sure which one is correct. Is there a way to do that? Am I best off manipulating the table the first time and then do updates? "John Vinson" wrote in message ... On Thu, 17 Nov 2005 05:05:39 GMT, wrote: I have a table that I import from an outside source. This table contains information on products by serial number. Because of the way it comes from the original source, the serial numbers are often repeated with different information attached. For instance the first occurance may have customer name and address, the second may have the customer's email, phone and fax. Is there an way to merger each of these occurances, so that the end product does not have duplicate serial numbers? I am using Microsoft Access 2000 Thanks You'll need a table with the Serial Number as a Primary Key; as each imported table comes in, you'll first run an Append query to add altogether new records to your table, and then run an Update query joining the import table to the master table by serial number, and updating the master table's fields to the corresponding import table. Can you assume that if (say) one import file has a customer phone, and a second import file has a DIFFERENT phone for the same customer, that it is a correction which should override the data you have already stored? John W. Vinson[MVP] |
#4
|
|||
|
|||
concatinating fields
On Fri, 18 Nov 2005 00:17:24 GMT, wrote:
Thanks for the reply no and therein lies part of the problem. The customer want to add fields to the table. I have some serial numbers that show two different companies own the item, but my customer isn't sure which one is correct. Is there a way to do that? Am I best off manipulating the table the first time and then do updates? Sounds like you'll need at least two tables: the master table with the serial number as the PK, and a (larger?) table into which you append all the updates, with a non-unique foreign key for the serial number and a datestamp for when the update was sent. How you'll decide how to update the master table is a real puzzle - "isn't sure which one is correct" is worrisome! John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Invisible Multiple Fields in PageHeaders (Word 2003) | Roberto Villa Real | Mailmerge | 4 | September 24th, 2005 10:53 PM |
improving performance by indexing query criteria fields | Paul James | General Discussion | 20 | February 16th, 2005 07:55 PM |
improving performance by indexing query criteria fields | Paul James | Running & Setting Up Queries | 20 | February 16th, 2005 07:55 PM |
Can't Add Fields to Form | Jeff Miller | Using Forms | 4 | January 12th, 2005 03:42 AM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |