Copying data between duplicate records

[Jack Myers]

Hello All,

In a single table there are some records that are duplicates based on an
account number. The only difference in the duplicate records (for a
particular account number) is a LoanNumber value. All the other values in a
record are the same, such as the address fields.

What I need to do is take the top duplicate record and copy/paste the
LoanNumber values from the other duplicate records (for a particular account
number) into other LoanNumber fields I will create in the top record.

Once I have all the values copied to the top record I will then find these
records (along with records that had only one loan number) and export them as
a text file.

Any feedback would be great.

Thank.

 

[Armen Stein]

On Mon, 27 Apr 2009 15:08:01 -0700, Jack Myers <Jack

In a single table there are some records that are duplicates based on an
account number. The only difference in the duplicate records (for a
particular account number) is a LoanNumber value. All the other values in a
record are the same, such as the address fields.

What I need to do is take the top duplicate record and copy/paste the
LoanNumber values from the other duplicate records (for a particular account
number) into other LoanNumber fields I will create in the top record.

It seems as though you will need multiple LoanNumber fields in the
same record, which is a poor database design. If you're going to need
to use this table in the future, it would be better to refine your
structure. You should really have one table for a Person (or Client,
or whatever you call them) and another child table for Loan (with each
LoanNumber and related fields in each row).

Once I have all the values copied to the top record I will then find these
records (along with records that had only one loan number) and export them as
a text file.

For what purpose? That text file will not be well structured either.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jack Myers]

On Mon, 27 Apr 2009 15:08:01 -0700, Jack Myers <Jack


It seems as though you will need multiple LoanNumber fields in the
same record, which is a poor database design. If you're going to need
to use this table in the future, it would be better to refine your
structure. You should really have one table for a Person (or Client,
or whatever you call them) and another child table for Loan (with each
LoanNumber and related fields in each row).


For what purpose? That text file will not be well structured either.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Thanks for the reply, Armen,

The ultimate goal is to export a text file from Access and bring it into
PostalSoft, our direct mail application. PostalSoft does not give us an
option to build tables. It simply displays data in one flat file.

We receive data from our clients in various formats, usually Excel, .csv.,
..dbf or .txt. In this case, the client gave us an Excel file where some of
the records were exactly the same, except for the LoanNumber. I just need to
grab the top record for a particular record/account number and put the
LoanNumber values from the other "related" records into their respective
fields I will create for that purpose (LoanNumber2, LoanNumber3...). in the
top record.

I realize it's poor database design to have those LoanNumber fields in one
table, but I don't have an option to split them out to another table.

Jack