Split Duplicate Records

[Guest]

I started out with 2 tables and joined them together using a like field in
both tables. However, the Table 1 has 1 record with this like field, and the
Table 2 has 2, 3, 4, or 5 records with this like field. This has created a
Table that has many duplicate records. I need to take the extra records from
Table 2 and append them all to the same record in Table 1.

Please help!!!

 

[John Vinson]

On Mon, 17 Jan 2005 09:47:03 -0800, Jeff M <Jeff

I started out with 2 tables and joined them together using a like field in
both tables. However, the Table 1 has 1 record with this like field, and the
Table 2 has 2, 3, 4, or 5 records with this like field. This has created a
Table that has many duplicate records. I need to take the extra records from
Table 2 and append them all to the same record in Table 1.

Please help!!!

Please give an example of the data. In a relational database you
CANNOT store multiple values in one record, it simply makes no sense.

What data do you now have? How do you want it?

John W. Vinson[MVP]

 

[Jeff]

John,
Thank you for your response. I have 2 tables. The first
contains personal data
regarding an individual. As you would expect I have one
record per person. In the second table, I have
information regarding this persons representative within
the company. Each individual could have more than one
representative in this table.

What I am trying to do is produce one record that
combines all of the individuals personal information with
that of their reps (multiple). This record would be used
for an application outside of Access.

What is the best/easiest way to do this?

When I combine the tables using an append query, it simply
makes multiple records for each individual. (i.e. Cust#23-
Rep#25, Cust#23-Rep#26, Cust#23-Rep#27, etc.) In this
case I end up with 3 records for 1 individual, instead of
1 record detailing Rep#26, 27, & 28.

Best Regards,
Jeff

 

[John Vinson]

John,
Thank you for your response. I have 2 tables. The first
contains personal data
regarding an individual. As you would expect I have one
record per person. In the second table, I have
information regarding this persons representative within
the company. Each individual could have more than one
representative in this table.

That sounds reasonable.

What I am trying to do is produce one record that
combines all of the individuals personal information with
that of their reps (multiple). This record would be used
for an application outside of Access.

What is the best/easiest way to do this?

With a Query, with the aid of a bit of VBA code. You certainly should
NOT create a Table containing this non-normalized data; it's exactly
as easy to export a Query as it is a Table, and it will save all the
overhead of adding new records to a table.

See

http://www.mvps.org/access/modules/mdl0004.htm

for some sample code to generate a new calculated field containing all
the reps for a person.


John W. Vinson[MVP]