One contact per site

[pagey47]

Good Morning all,

I have a large volume of data which has names, addresses and telephone numbers. I need to condense the list down to one contact per site. In MS Access I could simply put the telephone number as the primary key and "squash" the data in, but SQL doesn't like that. I would also like to pick the record with a populated title and surname field over one that hasn't so that I end up with a bunch of unique telephone numbers, with a sigle address and hopefully a contact. Can anyone point me in the right direction??

Thanks people!!!!

 

[M8KWR]

this is the way i have done this type of thing in the past, but i do not know if there is a better way of doing, so bare with me.

Take you original table, from this create another table only the fields you want to condense it down to, so company name and phone number.

From this you may still have a multiple companies names, due to the phone numbers been different - so you could manually go through this to delete those, if you wish.


Then in the table you have created, create the additional fields you need from the original table.

Create a query, join the original table and the one you create via the company field.

Then drag the fields from the new table that you need to gather information for into the window below. Change the query type to an Update Query.

In the "Update to:" under each field you need to update, you will then need to type something like [Original Table]![address1] etc

Run it, and then you will have only 1 records. This just put the information in at random so to speak, you could be more logical about it, by only putting in people with a lastname and job title, once this is run, and if you still have gaps, run it again, not overwritting the ones already inserted etc, and so on until you fill up the new table.

I hope i have explained myslef ok, if not let me know.

 

[pagey47]

Thanks for your response, I think I know what you are getting at. I was thinking more along the lines of some kind of IF statement that looks for telephone numbers it has already inserted and inputs the most populated record with that number. The database I have contains many millions of rows and has up to 15 contacts witht the same address and telephone number.

In the end I luckily only needed 10,000 records so I exported it to Access and created a new table, set the telephone number to primary key and then appended my rows in sorting by surname desc, forename desc and title desc