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

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]

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]

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]