?

Log in

No account? Create an account
Solution to tricky problem requires automation - A Suburbs Boy Living a Country Life [My Flickr Photos]
December 24th, 2007
12:41 pm

[Link]

Previous Entry Share Next Entry
Solution to tricky problem requires automation

(6 comments | Leave a comment)

Comments
 
[User Picture]
From:awfief
Date:December 24th, 2007 05:43 pm (UTC)
(Link)
*hugs* let me know what you need......

DELETE from ADDRESS where correctFlag=0;

:)
From:(Anonymous)
Date:December 24th, 2007 05:48 pm (UTC)
(Link)
[User Picture]
From:happypete
Date:December 24th, 2007 05:57 pm (UTC)

Another problem

(Link)
I actually can't do the query with this, because I really need some sort of sequence or unique ID for the update query side of things. "First, last" is not a valid primary key [is not unique, and is null for some business entries without a name field].

However, this example might give you ideas.

The only likely unique id I can find is an OLE Object "EntryID." Apparently, though, you can't include OLE Objects in UNION queries.
[User Picture]
From:happypete
Date:December 24th, 2007 05:51 pm (UTC)

Well, it's like this...

(Link)
Here's the UNION Query [All Phone Numbers]

SELECT First, Last, Phone, 1 As Type
FROM Contacts Where Phone is not null
UNION
SELECT First, Last, [Mobile Phone], 2
FROM Contacts Where [Mobile Phone] is not null
UNION
SELECT First, Last, [Pager Phone], 3
FROM Contacts Where [Pager Phone] is not null
UNION
SELECT First, Last, [Home2 Phone], 4
FROM Contacts Where [Home2 Phone] is not null
UNION
SELECT First, Last, [Assistant Phone Number], 5
FROM Contacts Where [Assistant Phone Number] is not null
UNION
SELECT First, Last, [Business Fax], 6
FROM Contacts where [Business Fax] is not null
UNION
SELECT First, Last, [Home Fax], 7
FROM Contacts where [Home Fax] is not null
UNION
SELECT First, Last, [Other Fax], 8
FROM Contacts where [Other Fax] is not null
UNION SELECT First, Last, [Telex Number], 9
FROM Contacts where [Telex Number] is not null;

From that, I can find duplicates to investigate, as follows:
SELECT [All Phone Numbers].Phone, [All Phone Numbers].First, [All Phone Numbers].Last, [All Phone Numbers].Type
FROM [All Phone Numbers]
WHERE ((([All Phone Numbers].Phone) In (SELECT [Phone] FROM [All Phone Numbers] As Tmp GROUP BY [Phone] HAVING Count(*)>1 )))
ORDER BY [All Phone Numbers].Phone;

Now, though is the tricky part...I need to design the form and automation to present duplicate sets to me one at a time, let me pick out the good one [or ones], and delete the rest. Some duplicates are okay--for example, it's okay for the "main number" for a bunch of people who work at the same firm to be okay--but many are a result of persistent corruption from bad sync software.
[User Picture]
From:happypete
Date:December 25th, 2007 02:52 am (UTC)

skeery, eh?

(Link)
merry xmas...
Powered by LiveJournal.com