Count total fields in a record with null values

[nharrison]

This situation requires some explanation.

I am trying to set up an importing routine for syncing contacts in an Access 2007 database with Outlook 2007, however I don't want to link the databases - I want to import from Outlook to Access.

I am importing all contacts using a .CSV file, into a new table called "Outlook Contacts". I then use an append query to add all records from Outlook Contacts to my main Contact table. This however produces duplicates, because I repeat the process once a week (to add in newly created contacts from Outlook). So in a given import, only 5 of the 100 imported contacts I want to actually retain.

Next I use a Find Duplicates query to find any duplicates in the Contact table with the same First and Last names (yes, I know it is possible to have 2 John Smiths, but this is a small business, and if this exception does occur I'll deal with it on a case to case basis).

What I want to do is create a Delete Query (Using the duplicate query as a source) that deletes all the duplicate records but one; however I want to retain the record that has the most fields with non-null values. My question is how would I go about counting non-null fields for a certain record, and how would I compare these in a query to designate the proper record to retain?

Thanks in advance.

 

[jjturner]

Hello and Welcome!

First, concerning your Duplicates situation, it sounds like you don't have a Unique ID to represent your Contacts (i.e., EmployeeID, etc) - but you may want to consider if there are a combination of elements which uniquely identifies each Contact (i.e., First and Last Name in combo with Address or Location, etc) and use that as an "Allow Duplicates = 'NO'" Index or as the PrimaryKey. Then the table does the work for you to screen out your duplicates.

But if you find it absolutely necessary to go through the validation you're proposing, you would need to start by creating a Calculated Field:

Step 1: NumberOfPopulatedFields: IIf([Field1] Is Null, 0, 1) + IIf([Field2] Is Null, 0, 1) + IIf([Field3] Is Null, 0, 1) + etc., etc., - (and this may need to be added as a column to your "Outlook Contacts" table)

Step 2: setup a Totals Query and GroupBy the First Name and Last Name fields, using a Count Aggregate function in a third field to exclude Contacts that don't have Duplicates (Count > 1)

Step 3: setup a second Totals Query joining Step 1 query to the query from Step 2 by the First and Last Name fields, GroupBy First and Last Name again, but taking the Min of NumberOfPopulatedFields as an Aggregate function.

Step 4: setting up the actual Delete query using query from Step 1 or the 'Updated' "Outlook Contacts" table joined to the 3-field recordset of validation criteria from Step 3 (With respect to Delete queries, I'm not sure how Access treats field joins to a grouped recordset offhand, but I believe this is possible if the primary recordset is still in atomic form)

Hope that helps

Regards,
John

 

[nharrison]

thanks, that will work perfect. Saved my ass