Fill field in table with data in field from another table... 2

[colem]

I have spent the last two weeks reading and trying to find a way to do this. Having said that here's the question.
Table 1 is filled with data and sent to another company with the telephone field blank. This other company fills in the phone numbers for the records they have numbers for, and send the table back in a completely different design. The name is split into 3 fields on our end, but on their end they merge the name fields into one. Address was two fields, and it comes back as 1. This company will not change the way they do this. We need it the way we do it to make reports and labels easy.
How do I get the phone numbers from the altered table into my original table in the corresponding records? Cannot use name as primary key because there might be duplicates often. Cannot use address because there may be duplicates there too.
Assigning an automatic ID field after the fact doesn't work because the two tables do not match up with their ID numbers.
Any ideas?

The more I learn, the more I know I don't know enough.
<order>chicken w/garlic sauce combo</order>
For those about to rock, we salute you.
NO DISASSEMBLE!

 

[jbpez]

Does the name and address together make it unique ?

John Borges

 

[jbpez]

If together they are unique, you could create a separate field on both tables with name and address combine and use it as a primary key.

John Borges

 

[colem]

Firstly, I should have stated I am using Access 2003 with the service pack applied.

Yes, the name and address fields together would make it unique, however for the sake of printing mailing labels from the records, the name really needs to be split into firstname, middle, lastname. The whole problem is that the format we had it in originally was ideal for labels. The way we get it back is totally not usable for labels. I did find a way to split the name back up into it's components, but even then I had no way to create a primary key in the altered copy, while having those records correspond to anything in my original table....
Up until now we have been manually typing in the phone numbers into our table from the info in the altered table.
It would save hours of time a week to be able to get those numbers back in to the original table OR format the incoming altered table into a label friendly format again. Either would work.

The more I learn, the more I know I don't know enough.
<order>chicken w/garlic sauce combo</order>
For those about to rock, we salute you.
NO DISASSEMBLE!

 

[ZmrAbdulla]

There are two chances..
Either use a combined(merged) field in your end to recognize the company name from the returned records.
or
Use a where clause to the altered record assigning where Company & Address = Company & Address in your record
Hope this helps

Zameer Abdulla
JVBP MDS
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[colem]

Hmm..see the problem there is that they also for some unknown reason change some of the addresses as well by abbreviating terms like street, lane etc. These people really make it hard for us to use this data when it returns. I see where you are going with it though. I think I should be able to find 2 fields to combine to create primary key. That sounds like a plan. For future purposes, if I create an autonumber ID for all of my tables, and tell company b not to delete or alter the ID field, then when their copy comes back, we should be able to join the tables based on the ID regardless of any changes they may have made to the names, addresses, etc right?

The more I learn, the more I know I don't know enough.
<order>chicken w/garlic sauce combo</order>
For those about to rock, we salute you.
NO DISASSEMBLE!

 

[ZmrAbdulla]

Don't create an Autonumber field as record recognizer to your records. Access 2000 has a problem on resetting the autonumber to deleted one..or something like that.. There was some KB with MS... I don't exactly remember.. It was a bug..
Use a unique ID to recognize the record in the future rather than autonumber.

Zameer Abdulla
JVBP MDS
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[colem]

Zmr,
I use Access 2003, but OK, thanks for the tip, i'll look that up to see what the problem is with it...
I'll see what I can combine to create a unique id.


The more I learn, the more I know I don't know enough.
<order>chicken w/garlic sauce combo</order>
For those about to rock, we salute you.
NO DISASSEMBLE!

 

[ZmrAbdulla]

Here is the KB article AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database

Zameer Abdulla
JVBP MDS
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[colem]

Zmr,
Thanks alot for that! The resolution apparently is a simple update to the Jet database engine. So I guess I can use the autonumber for my recognition, but thanks to you, I won't have the problem described.

The more I learn, the more I know I don't know enough.
<order>chicken w/garlic sauce combo</order>
For those about to rock, we salute you.
NO DISASSEMBLE!