Need to separate joint names

[soojosie]

I have a list with joint names and need to
1) Separate them
And then if not too difficult
2) Have Initials & Last Name in separate fields
Ideally
Lees Mark John & Lees Amy Kate -M J Field1, A K Field2 and Lees Field3

White Kate May - K M Field1, White Field2
Occassionally there are single name entries

Also need to separate address as Street address, City and Code all in single cell
Eg
1 May Street WELLINGTON WA 6425
City is in upper case but then so is the State, however state is only in about 50% of addresses. I need Street Address to be separate to City. City & Code together/seperate OK either way.
Any ideas?

 

[mph1]

Think the following may help.

http://www.mvps.org/access/strings/str0001.htm

Suggestion - once you have the data parsed, make individual fields for each. You can always put the fields together (concatenate) in any maner you wish.

HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[TheAceMan1]

How are ya soojosie . . . . .

[blue]I have a list with joint names[/blue]

I have no Idea what this means . . . be more specific please!

See Ya! . . . . . .

 

[soojosie]

Hej The AceMan1

I have scanned data from a PDF file onto an excel Spreadsheet. This is my first experience with scanned data. In most cells there are the names of 2 people, probably a married/common law couple.
Last Name, First and Middle all in a single cell, separated by an & sign.

Lees John Max & Lees Kate Sally all in a single excel cell
or
Smith Ryan & White Kelly Jackie

It is the same for the addresses. I need them separated e.g. Street Name, City Name, State Name and Code so I can put them each in thier own field so as to create queries.

This data is to be imported into an Access DB.

I understand how to bring them together in queries but not how to separate them.

I have looked up the article in

http://www.mvps.org/access/strings/str0001.htm

but am not sure how to go about this.

Cheers
Josie

 

[TheAceMan1]

soojosie . . . . .

Sorry to get back so late . . . . .

If I understand you correctly, you want one record per name ([blue]broken down into[/blue] FirstName, LastName, MiddleInitial or MiddleName), along with coresponding address ([blue]broken down into[/blue] Address, City, State, Zip). Is this correct?

Seperating the names is no problem. Seperating the two distinct addresses is no problem. Seperating the address into its component parts is!

Post a listing of every unique address to the best of your ability. By unique I mean those addresses that would require unique detedction!

See Ya! . . . . . .

 

[soojosie]

Hej AceMan1

Address
All names in a single cell share the same address. For this purpose the very few that have 2, 2nd address is unnecessary.

Name
LastName - full, FirstName & MiddleName - Initials only.

thanks
Josie

 

[TheAceMan1]

[blue]For this purpose the very few that have 2, 2nd address is unnecessary.[/blue]

Your at the point where you need to be specific about what you want. Where it occurs, if you want 1st name with 1st address & 2nd name with 2nd address, it can be made so. Information will be more discrete this way in th database. Remenber, wether one or two addresses, the only problem is breaking down the address into its component parts. I have a great many ideas of addresses, just need a few samples of what you have!

See Ya! . . . . . .

 

[soojosie]

Hej Aceman1,
Thanks for all your time, sorry I've not been more specific.
I should have said "2nd address cannot be used" not "2nd address is unnecessary".
Sorry to mess you about like this.

I only need the one address per name/s in cell.

Have attached a some samples.
The only Addresses I can use are the ones in the cells below the names. NOT the ones in the same cell and to the right of the names.

The ones in the same cell and to the right are not complete addresses, there are no PostCodes.
The ones underneath the names have PostCodes and sometimes State has been included - this is the address I need.

thanks
Josie


343671 1 Wesolowski Christopher Zdzislaw 7 Bounty DR Bounty

7 Bounty Drive BOUNTY 6452<<THIS IS NEEDED

544182 1 Grant Rob John & Grant Rhonda 13 Bounty DR Bounty

13 Bounty Drive BOUNTY WA 6452

544190 1 Onderwater Sonja 19 Bounty DR Bounty

19 Bounty Drive BOUNTY WA 6452

546807 2 Hantke Tim Rob & Hantke Anne 9 Bounty DR Bounty

6 Anstey Street CLAREMONT WA 6010

546815 2 Obrien John & Obrien Marie 11 Bounty DR Bounty

11 Bounty Drive BOUNTY 6452

547615 2 Jones Rob & Jones Jenny Delores 2 Bounty DR Bounty

2 Bounty Drive BOUNTY 6452

547789 2 Nicholas Alex & Nicholas Amy 12 Bounty DR Bounty

12 Bounty Drive BOUNTY WA 6452

 

[TheAceMan1]

soojosie . . . . .

We have reached the top of the mountain here. Were either gonna fall on the good side, or the bad. As an entry you have:

[blue]343671  1   Wesolowski Christopher Zdzislaw   7 Bounty DR Bounty
    
             7 Bounty Drive BOUNTY 6452<<THIS IS  NEEDED[/blue]

I need you to define specific/seperate cells here:

Is [purple]343671[/purple] a seperate cell?

Is [purple]1 Wesolowski Christopher Zdzislaw 7 Bounty DR Bounty[/purple] a seperate cell?

Is [purple]7 Bounty Drive BOUNTY 6452[/purple] a seperate cell?

Is [purple]1 Wesolowski Christopher Zdzislaw 7 Bounty DR Bounty

7 Bounty Drive BOUNTY 6452[/purple] a seperate cell?

See Ya! . . . . . .

 

[soojosie]

Hej Aceman1
The maintain and the view both look great!
Nearly all the data is in the one cell - yeah I know, it was a pathetic transfer, must learn if there is a better way to do it.
Is 343671 a seperate cell? NO

Is 1 NO

Wesolowski Christopher Zdzislaw 7 Bounty DR Bounty a seperate cell? NO

Is 7 Bounty Drive BOUNTY 6452 a seperate cell? YES

thanks
Josie

 

[soojosie]

Hej Aceman1
i was rushing and forgot to add that I had planned to separate most of the data in the cell with "data > transfer to columns .." so it is the names and address that are causing me grief.

Is 343671 a seperate cell? NO "data > transfer to columns"

Is 1 NO "data > transfer to columns"

Wesolowski Christopher Zdzislaw 7 Bounty DR Bounty a seperate cell? NO "data > transfer to columns" to seperate address to new cell

Is 7 Bounty Drive BOUNTY 6452 a seperate cell? YES

cya
Josie

 

[TheAceMan1]

OK soojosie . . . .

Let see if I have the conversion proper:

343671  1   Wesolowski Christopher Zdzislaw   7 Bounty DR Bounty
    
             7 Bounty Drive BOUNTY 6452

See Ya! . . . . . .

 

[TheAceMan1]

Forget the previous post . . . . . hit submit by mistake!

See Ya! . . . . . .

 

[TheAceMan1]

OK soojosie . . . . .

[blue]343671  1   Wesolowski Christopher Zdzislaw   7 Bounty DR Bounty
    
             7 Bounty Drive BOUNTY 6452

[purple]Should convert to:

 LastName   FirstName  Mid/Name          Address
---------- ----------- -------- -------------------------- 
Wesolowski Christopher Zdzislaw 7 Bounty Drive BOUNTY 6452[/purple][/blue]

Is this correct?

Now . . . if there's a second name, do you want it in the [blue]same record[/blue], or a [blue]new record[/blue] with same address?

Also in [blue]343671 1 Wesolowski Christopher Zdzislaw [/blue], does the 6 digit number have any significance to you? Do you need it transferred as well?

Also, post data for one of the few with two addresses.

See Ya! . . . . . .

 

[soojosie]

343671 Field Identification # YES Required
1 Field Type
Wesolowski Field LastName
C Z Field Initials1
7 Bounty DR Bounty Field Address2 – not to be separated
7 Bounty Drive Field Street
BOUNTY Field City
6452 Field PostCode

546807 2 Hantke Tim Rob & Hantke Anne 9 Bounty DR Bounty
6 Anstey Street CLAREMONT WA 6010

546807 Field
2 Field
Hantke Field LastName
T R Field Initials1
A Field Initials2
9 Bounty DR Bounty Field Address2 - not to be separated
6 Anstey Street Field Street
CLAREMONT Field City
WA 6010 Field (No difference if WA is included or just dropped)

547797 2 Morgan Gary John & Harders Susanna Jane 14 Bouvard DR BOUVARD

14 First Avenue MOUNT LAWLEY WA 6050

 

[soojosie]

Hej Aceman1,

Thanks heaps for all this

sorry that last reply was a mess I've tried to improve the spaceing this time so it will be clear.

Yes I do need the 6#s.
I've tried to list all of the fields as I will be using them
all the data needs to be separated except for address2, I need that whole as the only times I will be using that will be as a reference.

343671 Field Identification # YES Required
1 Field Type
Wesolowski Field LastName1
C Z Field Initials1
7 Bounty DR Bounty Field Address2 – not to be separated
7 Bounty Drive Field Street
BOUNTY Field City
6452 Field PostCode

546807 2 Hantke Tim Rob & Hantke Anne 9 Bounty DR Bounty
6 Anstey Street CLAREMONT WA 6010

546807 Field
2 Field
Hantke Field LastName1
T R Field Initials1
A Field Initials2
9 Bounty DR Bounty Field Address2 - not to be separated
6 Anstey Street Field Street
CLAREMONT Field City
WA 6010 Field (No difference if WA is included or just dropped)

547797 2 Morgan Gary John & Harders Sue Jane 14 Bouvard DR BOUVARD
4 First Avenue MOUNT LAWLEY WA 6050

here i need
Morgan Field LastName1
G J Field Initials1
Harders Field LastName2
S J Field Initials2
4 First Avenue Field Street
MOUNT LAWLEY Field City
WA 6050 Field PostCode

Hantke and Morgan/Harders are samples of 2 addresses

thanks
Josie

 

[TheAceMan1]

soojosie . . . .

Can you send me an sample of the Database as it is?

Send it as an attachment . . . See my profile.

Note : all correspondence is to be carried on here at tek-Tips.

See Ya! . . . . . .

 

[soojosie]

It is difficult visually with such small spaces.
thanks
Josie

 

[TheAceMan1]

soojosie . . . .

Files received. Currently at work and won't be able to give a good look til this evening. See ya then . . . . .

See Ya! . . . . . .

 

[TheAceMan1]

OK soojosie . . . . .

First, [blue]it can be done![/blue] . . . . [purple]but its gonna take alot of work and testing[/purple]. There are too many inconsistancies (static spacing is not as it should be, cities can comprise more than one word . . . ect). All of these have to be dealt with, and its not gonna be done in just a few days by my estimations.

Who ever did this origionally, practically blew it out the back door as far as Access is concerned. If only they had included [blue]delimiters![/blue]

See Ya! . . . . . .