Appending between rows

[Guest]

Several hundred records in my database are duplicates except for the data in
one field. I would like to create a new field or fields and somehow "append"
the different values all into one record, then being able to delete the
excess records. See below for an example.

Three records with IDENTICAL data except for one field. The data looks
something like this:

Record 1 = xxx
Record 2 = yyy
Record 3 = zzz

I would like to combine everything into one record by one of two ways. I can
either create three new fields, in which case xxx, yyy, and zzz appear in all
different fields, or just one new field where it would look like this: xxx,
yyy, zzz (seperated by a comma).

In the end, I want to be able to delete two of the three records, as they
would now be redundant.

Thanks for your help.

 

[Pat Hartman\(MVP\)]

Sounds like what you are looking at is a flattened 1-many relationship. You
can normalize it by creating two tables. (You might need three depending on
what you are actually talking about) One table will contain the unique
instances of what you are calling the duplicate data and the second table
will contain separate rows for each of the unique values for the "duplicate"
data. You don't indicate what type of data you have so I'll use the example
of a student and his classes.
John Main St OurTown .... Algebra1
John Main St OurTown .... EnglishLit
John Main St OurTown .... Biology
Mary Elm St OurTown .... Algebra1
Mary Elm St OurTown .... AdvancedWriting
Mary Elm St OurTown .... PhysEd
Toni South St OurTown .... AdvancedWriting

for this data, I would use three tables.
tblClass
ClassID (autonumber primary key)
ClassName
etc.
It would contain this data:
1 Algebra1
2 EnglishLit
3 Biology
4 AdvancedWriting
5 PhysEd

tblStudent
StudentID (autonumber primary key)
FirstName
StreetAddress
City
etc.
This table would contain
1 John Main St OurTown
2 Mary Elm St OurTown
3 Toni South St OurTown
tblStudentClasses
ClassID (foreign key to tblClass)
StudentID (foreign key to tblStudent)
this table would contain:
1 1
1 2
1 3
2 1
2 4
2 5
3 4

If you provide a little more information regarding your data, someone can
suggest a method to load the necessary tables.

 

[Guest]

The following alternative is a better way out than the suggested options:

Say the field with different data in each record is called Field F. Create
Table 1 with all the fields excluding Field F; also define a primary key PK.
Create Table 2 consisting of primary key PK and Field F. Subsequently delete
duplicates from Table 1.
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?

 

[Guest]

I understand how to perform the process you suggested, but I don't see how
that would help me. Maybe I'm just not understanding you? Let me attempt
another example.

I have three records as follows. Each space separates the different fields.

abcdefghijk 12345 zyxwvut california
abcdefghijk 12345 zyxwvut arizona
abcdefghijk 12345 zyxwvut illinois

Since everything to the left of the state names is identical, it is
redundant. I want to end up with one record that looks like this:

abcdefghijk 12345 zyxwvut california,arizona,illinois

....or this, by adding two more fields (remember the space indicates a
different field):

abcdefghijk 12345 zyxwvut california arizona illinois

 

[Duane Hookom]

That's not quite how I would handle redundant data. I would use a main table
with a unique primary key

tblMainData
1 abcdefghijk 12345 zyxwvut

Then create a related table that includes the primary key value from the
main table and the "changing value"

tblRelatedData
1 1 california
2 1 arizona
3 1 illinois

 

[Guest]

Duane, that is my ultimate goal. For the time being though, I need to break
the rules and have those field values together with the parent records in the
main table. This is a dilemma (with the nature of the data itself) that was
caused by someone else in my department.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You could use this in a totals query to get your results.
--
Duane Hookom
MS Access MVP

Duane, that is my ultimate goal. For the time being though, I need to
break
the rules and have those field values together with the parent records in
the
main table. This is a dilemma (with the nature of the data itself) that
was
caused by someone else in my department.