?

Log in

No account? Create an account
Solution to tricky problem requires automation - A Suburbs Boy Living a Country Life [Pete and Pam's pages (photos and some commentary)] [Pam's journal] [Our company] [My Flickr Photos] [Arisia]
December 24th, 2007
12:41 pm

[Link]

Previous Entry Share Next Entry
Solution to tricky problem requires automation
Okay, I've constructed a union query that gives me every phone number visible to Access from my Outlook contacts, and an associated ID. I suppose what I need to do now is to come up with automation to handle the "inverse query." I want to be able to "delete all occurrences of a phone number aside from those that I indicate are correct.

Current Mood: annoyedannoyed

(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:happypete
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...
[User Picture]
From:tonyvila
Date:December 26th, 2007 02:18 pm (UTC)
(Link)
So, looking at your query, it seems you're using it to create a list of contacts to keep, thus marking the rest for deletion. So you're creating two lists of unique IDs and want to remove the ones in the full list that are not on the keep list. There's two ways to do this.

The best way is to simplify matters by reprogramming your interface and just mark the ones you want to delete: Then it's DELETE FROM table WHERE id IN (1,2,3);.

The less optimal way (because you have to pull the list twice) is to say DELETE FROM table WHERE id IN (select statement to get the full list) AND id NOT IN (your list of selected IDs);

Helps?
[User Picture]
From:happypete
Date:December 26th, 2007 05:00 pm (UTC)

problem is...

(Link)
that in fact I'm not getting a unique ID, just an ID which I could later use to determine from which field that particular number.

So far as I can tell, the unique ID is a field called EntryID...which can't be included in a union query.

I could build up a recordset manually in VBA rather than using a union query...that might be the way to go.
Pete, Pam and Quinn's pages Powered by LiveJournal.com