restructuring database

Nicole Guy · May 16th, 2002, 02:39 PM

I have a database that needs restructuring. Currently it is a one to many relationship, ie. there is a license # that corresponds to many block #'s, each block # is in a separate field. I want all the data to be restructured into a one to one relationship database, ie. one license # for each block #. This will mean that each license # is repeated as many times as there is blocks, ie. one record for each unique license and block combination.

Can anyone think of a good, faster way to do this? The only way I can find is to copy each set of blocks and transpose them into another database and copy the license # for each. I would like to automate or speed this up a bit more.

Thanks.

Aladin Akyurek · May 16th, 2002, 02:55 PM

Quote:

On 2002-05-16 13:39, Nicole Guy wrote:
I have a database that needs restructuring. Currently it is a one to many relationship, ie. there is a license # that corresponds to many block #'s, each block # is in a separate field. I want all the data to be restructured into a one to one relationship database, ie. one license # for each block #. This will mean that each license # is repeated as many times as there is blocks, ie. one record for each unique license and block combination.

Can anyone think of a good, faster way to do this? The only way I can find is to copy each set of blocks and transpose them into another database and copy the license # for each. I would like to automate or speed this up a bit more.

Thanks.

In terms of what relational databases are, converting one to many to one to one would not be the right thing to do.

Would you care to elaborate, perhaps with a sample of the records that you want to convert?

Thanks

Brian · May 16th, 2002, 03:01 PM

Sounds like the database was structured properly the first time. You can extract the data to display any way you want. It the data says that there are many block numbers per license number than you cannot change that fact of life, just how effectively you store and represent the information.

Nicole Guy · May 16th, 2002, 03:12 PM

For my purposes, I need it to be a one to one database. I will be using it for some GIS analysis that I am doing that requires it to be one to one to work properly.

Example of original database:
License# Block#1 Block#2 Block#3 etc....
P1 1234 1235 1236
P2 6785 6786
P3 3456 3457 2358

New database (what I want to see):
License# Block#
P1 1234
P1 1235
P1 1236
P2 6785
P2 6786
P3 3456
P3 3457
P3 3458

Brian · May 16th, 2002, 06:45 PM

There may be fancire methods, but since you are only doing this once, a somewhat manual low-tech method is appropriate.

Assuming that each block is in a separate column then:
1) Find out the maximum number or blocks, ex. 4.
2) Then in rows 2 TO 5 of the new sheet, place the following:
ROW COL
1___ 2
1___ 3
1___ 4
1___ 5

3) In the next row, place the equns
=A1+1__ =B1

4) Drag this down and you will see the row & column repetition of the data you want.

5) Extract the data by placing the following equations in Col C & D:

LICENSE______ BLOCK
=INDIRECT("SHEET1!"&ADDRESS(A2,1))____ =INDIRECT("SHEET1!"&ADDRESS(A2,B2))

6) Drag the col C & D columns down.

The whole thing will look like:

ROW_ COL_ LICENSE_ BLOCK
1_ 2_ =INDIRECT("SHEET1!"&ADDRESS(A2,1))_ =INDIRECT("SHEET1!"&ADDRESS(A2,B2))
1_ 3_ =INDIRECT("SHEET1!"&ADDRESS(A3,1))_ =INDIRECT("SHEET1!"&ADDRESS(A3,B3))
1 4_ =INDIRECT("SHEET1!"&ADDRESS(A4,1))_ =INDIRECT("SHEET1!"&ADDRESS(A4,B4))
1_ 5_ =INDIRECT("SHEET1!"&ADDRESS(A5,1))_ =INDIRECT("SHEET1!"&ADDRESS(A5,B5))
=A2+1_ =B2_ =INDIRECT("SHEET1!"&ADDRESS(A6,1))_ =INDIRECT("SHEET1!"&ADDRESS(A6,B6))
=A3+1_ =B3_ =INDIRECT("SHEET1!"&ADDRESS(A7,1))_ =INDIRECT("SHEET1!"&ADDRESS(A7,B7))
=A4+1_ =B4_ =INDIRECT("SHEET1!"&ADDRESS(A8,1))_ =INDIRECT("SHEET1!"&ADDRESS(A8,B8))
=A5+1_ =B5_ =INDIRECT("SHEET1!"&ADDRESS(A9,1))_ =INDIRECT("SHEET1!"&ADDRESS(A9,B9))
=A6+1_ =B6_ =INDIRECT("SHEET1!"&ADDRESS(A10,1))_ =INDIRECT("SHEET1!"&ADDRESS(A10,B10))
=A7+1_ =B7_ =INDIRECT("SHEET1!"&ADDRESS(A11,1))_ =INDIRECT("SHEET1!"&ADDRESS(A11,B11))
=A8+1_ =B8_ =INDIRECT("SHEET1!"&ADDRESS(A12,1))_ =INDIRECT("SHEET1!"&ADDRESS(A12,B12))

7) select the entire sheet > Copy > Paste Special > Values into a new sheet

sort on block and delete all of the "0" rows.

9) sort back on license

I have VBA that can do this directly, but it is faster just to do the above.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 02:39 PM	#1
Nicole Guy New Member Join Date: May 2002 Posts: 2	I have a database that needs restructuring. Currently it is a one to many relationship, ie. there is a license # that corresponds to many block #'s, each block # is in a separate field. I want all the data to be restructured into a one to one relationship database, ie. one license # for each block #. This will mean that each license # is repeated as many times as there is blocks, ie. one record for each unique license and block combination. Can anyone think of a good, faster way to do this? The only way I can find is to copy each set of blocks and transpose them into another database and copy the license # for each. I would like to automate or speed this up a bit more. Thanks.

May 16th, 2002, 03:01 PM	#3
Brian Board Regular Join Date: Apr 2002 Posts: 113	Sounds like the database was structured properly the first time. You can extract the data to display any way you want. It the data says that there are many block numbers per license number than you cannot change that fact of life, just how effectively you store and represent the information.

May 16th, 2002, 03:12 PM	#4
Nicole Guy New Member Join Date: May 2002 Posts: 2	For my purposes, I need it to be a one to one database. I will be using it for some GIS analysis that I am doing that requires it to be one to one to work properly. Example of original database: License# Block#1 Block#2 Block#3 etc.... P1 1234 1235 1236 P2 6785 6786 P3 3456 3457 2358 New database (what I want to see): License# Block# P1 1234 P1 1235 P1 1236 P2 6785 P2 6786 P3 3456 P3 3457 P3 3458

May 16th, 2002, 06:45 PM	#5
Brian Board Regular Join Date: Apr 2002 Posts: 113	There may be fancire methods, but since you are only doing this once, a somewhat manual low-tech method is appropriate. Assuming that each block is in a separate column then: 1) Find out the maximum number or blocks, ex. 4. 2) Then in rows 2 TO 5 of the new sheet, place the following: ROW COL 1___ 2 1___ 3 1___ 4 1___ 5 3) In the next row, place the equns =A1+1__ =B1 4) Drag this down and you will see the row & column repetition of the data you want. 5) Extract the data by placing the following equations in Col C & D: LICENSE______ BLOCK =INDIRECT("SHEET1!"&ADDRESS(A2,1))____ =INDIRECT("SHEET1!"&ADDRESS(A2,B2)) 6) Drag the col C & D columns down. The whole thing will look like: ROW_ COL_ LICENSE_ BLOCK 1_ 2_ =INDIRECT("SHEET1!"&ADDRESS(A2,1))_ =INDIRECT("SHEET1!"&ADDRESS(A2,B2)) 1_ 3_ =INDIRECT("SHEET1!"&ADDRESS(A3,1))_ =INDIRECT("SHEET1!"&ADDRESS(A3,B3)) 1 4_ =INDIRECT("SHEET1!"&ADDRESS(A4,1))_ =INDIRECT("SHEET1!"&ADDRESS(A4,B4)) 1_ 5_ =INDIRECT("SHEET1!"&ADDRESS(A5,1))_ =INDIRECT("SHEET1!"&ADDRESS(A5,B5)) =A2+1_ =B2_ =INDIRECT("SHEET1!"&ADDRESS(A6,1))_ =INDIRECT("SHEET1!"&ADDRESS(A6,B6)) =A3+1_ =B3_ =INDIRECT("SHEET1!"&ADDRESS(A7,1))_ =INDIRECT("SHEET1!"&ADDRESS(A7,B7)) =A4+1_ =B4_ =INDIRECT("SHEET1!"&ADDRESS(A8,1))_ =INDIRECT("SHEET1!"&ADDRESS(A8,B8)) =A5+1_ =B5_ =INDIRECT("SHEET1!"&ADDRESS(A9,1))_ =INDIRECT("SHEET1!"&ADDRESS(A9,B9)) =A6+1_ =B6_ =INDIRECT("SHEET1!"&ADDRESS(A10,1))_ =INDIRECT("SHEET1!"&ADDRESS(A10,B10)) =A7+1_ =B7_ =INDIRECT("SHEET1!"&ADDRESS(A11,1))_ =INDIRECT("SHEET1!"&ADDRESS(A11,B11)) =A8+1_ =B8_ =INDIRECT("SHEET1!"&ADDRESS(A12,1))_ =INDIRECT("SHEET1!"&ADDRESS(A12,B12)) 7) select the entire sheet > Copy > Paste Special > Values into a new sheet sort on block and delete all of the "0" rows. 9) sort back on license I have VBA that can do this directly, but it is faster just to do the above.