How do i normalize repeated fields in access?

[Guest]

I need to append the data in the repeated fields, so that i have repeated
records instead and i can take it from there.

Thanks

 

[Vincent Johns]

I need to append the data in the repeated fields, so that i have repeated
records instead and i can take it from there.

Thanks

If I understand you correctly, you have a Table with contents something
like these:


[Table1]
Table1ID Name1 Name2 Name3
---------- ----- ------ -----
350057186 John Jim Joy
1856257467 Mary Martha

and you want all the [Name_] fields, which are of the same (or similar)
data types, to share one field in your normalized Table. I suggest
setting up a new Table and an Append Query that might look like this:

[Q_CopyRepeatedFields] SQL:

INSERT INTO [Normalized Table] ( Name, Series, Table1ID )
SELECT Table1.Name2, 2 AS Series, Table1.Table1ID
FROM Table1
WHERE (((Table1.Name2) Is Not Null And (Table1.Name2)<>""));

Running that against the (initially empty) [Normalized Table] will put
some of the fields into new records:

[Normalized Table]
Normalized TableID Name Series Table1ID
------------------ ------ ------ ----------
-1905407500 Jim 2 350057186
1706932197 Martha 2 1856257467

The [Normalized TableID] field is an Autonumber field that I put there
to serve as the primary key, but the other fields will be empty unless
your Query puts a value there.

Next, change [Q_CopyRepeatedFields] to grab a different field from your
original Table; we'll get [Name1] this time:

[Q_CopyRepeatedFields] SQL:
INSERT INTO [Normalized Table] ( Name, Series, Table1ID )
SELECT Table1.Name1, 1 AS Series, Table1.Table1ID
FROM Table1
WHERE (((Table1.Name1) Is Not Null And (Table1.Name1)<>""));

The Table now looks like this:

[Normalized Table]
Normalized TableID Name Series Table1ID
------------------ ------ ------ ----------
-1905407500 Jim 2 350057186
-1781460670 John 1 350057186
-1312729957 Mary 1 1856257467
1706932197 Martha 2 1856257467

Change the Query again and run it ...

[Q_CopyRepeatedFields] SQL:
INSERT INTO [Normalized Table] ( Name, Series, Table1ID )
SELECT Table1.Name3, 3 AS Series, Table1.Table1ID
FROM Table1
WHERE (((Table1.Name3) Is Not Null And (Table1.Name3)<>""));

.... and so forth, until you've copied all of the values into the proper
(combined) field. You may not need the [Series] field; I used that to
show which column the name came from, and sometimes it's useful to know
that. You probably will need to copy the original Table's primary key,
in this case [Table1].[Table1ID], as you'll need it to refer to other
fields in the original Table.

The end result will look like this:

[Normalized Table]
Normalized TableID Name Series Table1ID
------------------ ------ ------ ----------
-1905407500 Jim 2 350057186
-1781460670 John 1 350057186
-1312729957 Mary 1 1856257467
1354703168 Joy 3 350057186
1706932197 Martha 2 1856257467

Notice that we did not copy the blank field, because the WHERE clause in
the Query omitted empty values. The copied [Table1ID] field can be used
to link this Table to [Table1]. After constructing the new Table,
[Normalized Table], you may delete the repeated [Name1], [Name2], and
[Name3] fields from [Table1], as well as the Append Query, which you
will no longer need.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

I need to append the data in the repeated fields, so that i have repeated
records instead and i can take it from there.

Thanks

You'll have to explain what you're trying to do, Mani.

If you have repeated fields, you need another table.

For a more specific answer, please ask a more specific question.

John W. Vinson[MVP]