Sorting Data (1 Viewer)

[tbasher]

I've thought about array's and recordsets, but was wondering if there is an easier way.

I have table like this:
Name Address
Bob 123 Main Street
Jane 89654 Elm Street
Bob 3547 Island Circle St.
Jane 900 River Drive

I would like to turn the above data into this:
Name Final
Bob 123 Main Street, 3547 Island Circle St.
Jane 89654 Elm Street, 900 River Drive

Thanks for all your help.

 

[Len Boorman]

Basically before you can make much progress you really should reformat your data so that within the table you are dealing with atomic values.

If you create a table like

Field Value
Name Bob Jones
Address_1 123 Main St
Address_2 Capital City
Address_3 New York
Zip/Code 123456

Okay so you now have 5 fields that make up the name and address but since the elements are atomic you can sort, select and concatonate them in any way,combination you wish.

Len B

 

[tbasher]

Thanks for the post Len.

That would work great, the only problem I see is that the reformat table process needs to be dynamic because Bob may have 5 addresses representing 5 address fields and Jane may have 2 addresses representing the first 2 address fields.

Not sure how to produce this dynamic process....?

 

[Len Boorman]

Didn't explain myself sufficiently.

Atomic values for composite data is a standard practice. There is also a methodology within relational databases to handle situations whereby data item has Many relations.


Basically the same table construction applies. Address_1 represents not say Bob's first address but represents the first line of his address. You may decided that address_4 will be State and Address_5 will be Country. So if Bob has 5 addresses then he would appear 5 times. Jane may only appear twice but her addresses each take a seperate line.


Name----Address_1------Address_2------Address_3----Address_4 etc
Bob------Flat 2----------Main St---------Miami---------USA
Jane-----1125 St Pauls---51st South-----Oregon--------USA
George---Big House-------Pensylvania Av-New York------USA
Bob------Another Flat-----Elsewhere------Alaska---------USA
Bob------Again
Jane-----Again
George---Homeless ?

You now have a table that will allow you to search , extract and concatonate data in virtually any sequence.

All people in a particular State, or a particular country or all states bwginning with T etc

Len B

Edited because display after posting was confusing. I have put -'s in to provide separation of the data elements so you can see hopefully how the table would look.

L

 

[tbasher]

I now understand what you are saying. I actually misread what you were trying to get across and I didn't explain myself very good.

The problem is that I can't actually reformat the table. The table of data That I have to work with contains 1000's of records with the data already there. The goal is to have 1 record for each person and have all of the addresses concatentated into one field of that person.

Example:
Record# | Field1 | Field2
1 | Bob | 123 Main Street
2 | Jane | 89654 Elm Street
3 | Bob | 3547 Island Circle St.
4 | Jane | 900 River Drive

.....into......

Record# |Field1 | Field2
1 | Bob | 123 Main Street, 3547 Island Circle St.
2 | Jane | 89654 Elm Street, River Drive

I assume the solution is to run some type of advanced query or use VB to run the data into arrays or recordsets to compare Names and then concatenate the addresses if the names match.

Thanks and sorry for the confusion.

 

[Pat Hartman]

You ABSOLUTELY do NOT want to concatenate multiple addresses in a single field. You already have something of a problem with the entire address mushed into one field rather than each part of an address occupying a separate field. Mushing multiple addresses into a single field simply compounds the problem and will make working with the data very difficult. If it is valid for someone to have more than one address then you need to define a proper 1-to-many relationship. That means that the addresses will be stored in a separate table. The table will contain only three fields unless your address field really is made up of several separate fields.

tblAddress
AddressID (autonumber primary key)
PersonID (foreign key to person table)
Address (it would be best if this were actually a minimum of 5 fields - Address1, Address2, City, State, Zip)