?

Log in

No account? Create an account
Progress - 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]
February 22nd, 2006
10:44 am

[Link]

Previous Entry Share Next Entry
Progress
Requirements: See last post.
Concept of operations:
  1. Retrieve contact information from Outlook store.
  2. Create "link table" to associate contacts to numbers, preserving information about which type of number it was.
  3. Populate link table from contact data.
  4. Create query to filter phone numbers that appear only once.
  5. Collect information about which numbers to delete.
  6. Make a backup of original contacts.  (Yes, gentle reader, I did think of this before I actually touched my "live data." Data corruption is how we got in to this mess to begin with. I don't want to make it worse.)
  7. Make updates to Outlook store
  8. Rejoice.

Elaboration:  Item 5 suggests some sort of user interface, a form to present "candidate" bogus numbers

Status: 

  1. Retrieve contact information from Outlook store
    Linked to Contacts--this was actually trivial.
  2. Create "link table" to associate contacts to numbers, preserving information about which type of number it was.
    My first UNION query:

    SELECT [Display Name], Phone As [Number] , "Phone" As [Type] From Contacts Where Phone is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Mobile Phone], "Mobile Phone" From Contacts Where [Mobile Phone] is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Pager Phone], "Pager Phone" From Contacts Where [Pager Phone] is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Home2 Phone], "Home2 Phone" From Contacts Where [Home2 Phone] is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Assistant Phone Number], "Assistant Phone Number" From Contacts Where [Assistant Phone Number] is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Business Fax], "Business Fax" From Contacts where [Business Fax] is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Home Fax], "Home Fax" From Contacts Where [Home Fax] is Not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Other Fax], "Other Fax" From Contacts Where [Other Fax] Is not null and [display name] is not null
    UNION ALL SELECT [Display Name], [Telex number], "Telex Fax" From Contacts where [telex number] is not null and [display name] is not null
    ORDER BY Number;

    [n.b. the ALL keyword is not required, because my [Type] field and the implicit knowledge that [Display Names] will should guarantees uniqueness among the results of the UNION clauses, but Bill Gates the documentation assures me that ALL is faster because it avoids even checking for duplicates.]  Have to think about Display Names potentially not being unique--it's guaranteed in Outlook Address Books, but not in IPM.Contact items.

  3. Populate link table from contact data.
    The populate table is a trivial 1-1 append from the UNION.
  4. Create query to filter phone numbers that appear only once.
    The duplicate detection is a little weirder, but actually not horrible; not my first "inner query," but I haven't actually had a use for HAVING before :

    SELECT NameToNumber.[Phone Number], NameToNumber.LinkID
    FROM NameToNumber
    WHERE (((NameToNumber.[Phone Number]) In (SELECT [Phone Number] FROM [NameToNumber] As Tmp GROUP BY [Phone Number] HAVING Count(*)>1 )))
    ORDER BY NameToNumber.[Phone Number];

  5. Collect information about which numbers to delete.
    Now I have to start thinking. Hard. Time to go to my real work.
  6. Make a backup of original contacts.
  7. Make updates to Outlook store
  8. Rejoice.
    May be while before rejoicing>

(1 comment | Leave a comment)

Comments
 
[User Picture]
From:gardenfey
Date:February 22nd, 2006 10:10 pm (UTC)
(Link)
My first UNION query

Welcome to the dark side! Seriously, if you need any SQL help, just yell.
Pete, Pam and Quinn's pages Powered by LiveJournal.com