Results 1 to 5 of 5
  1. #1
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7

    Totals Query and Empty Fields

    I'm trying to use a Totals Query on a table that has a few records that I can define as duplicates.

    Scenario:

    Table A has 4 fields: FirstName, LastName, email, Company

    There are 50 rows in the table.



    The table was first created when 40 leads filled out a simple form and provided their info. 10 forgot to include an e-mail address. 10 forgot to write their names, but did provide an e-mail. Then, the 10 individuals who hadn't given an e-mail address to match their names returned, and they submitted all the required data this time. I end up with a few rows where FirstName and LastName are duplicates of previous rows, but the later email field has data that the prior one does not (for those 10 individuals who essentially submitted two forms), and I would simply like to take that data from the later submissions and mash the duplicates into a single record.

    HOWEVER. The fact that some people did not provide first names or last names at all means that the totals function is counting everyone with no first and last name provided as a duplicate (because the FirstName and LastName fields for all of them match: they are blank). So if Jane and John Doe both gave me their email addresses but didn't write their first and last names, my database counts them as duplicates when I "Group By" the two fields of first and last name... when of course they are not duplicates.

    I feel like there is some easy way to do this that I'm missing, but I can't figure what I'm doing wrong at the moment.

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    Only 50 records? Just go edit them and delete the duplicates. That's the easiest and simplest.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    Only 50 records? Just go edit them and delete the duplicates. That's the easiest and simplest.
    Heh that's why I said "Scenario."

    In reality, I have 4000 base records, and I'm adding another few hundred, some of which are dupes. So going through by hand is not realistic. The 50 example was just for explanatory purposes. But I assume there is a simple function for this process; it's kind of a basic update of records. I just don't know how to do it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    As the man said, "Just the facts, ma'am" - the real facts.

    No, I don't think there is an 'easy' way. If a record has names and no email and another record has the email and no names then what would be the linking identifier? Is there an employeeID or something?

    You can do queries that:

    1. Count the occurrence of names

    2. Retrieve all records without email

    3. Retrieve all records without names

    With those queries as your guides, go review the records and manually reconcile. Ask (by snail mail?) the people on records without email to provide their email again. Send messages to the emails without names and tell them you need name info or will be removed from database.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    AccessNub is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    As the man said, "Just the facts, ma'am" - the real facts.

    No, I don't think there is an 'easy' way. If a record has names and no email and another record has the email and no names then what would be the linking identifier? Is there an employeeID or something?

    You can do queries that:

    1. Count the occurrence of names

    2. Retrieve all records without email

    3. Retrieve all records without names

    With those queries as your guides, go review the records and manually reconcile. Ask (by snail mail?) the people on records without email to provide their email again. Send messages to the emails without names and tell them you need name info or will be removed from database.
    I think you actually identified a working starting point. Main Table is the old data, New Records is my new data (some totally new people, some updated email info for already existing people):

    Find records in Main Table with an empty name field, create a table for them, Table A.

    Find records with a name, create a table for them, Table B.

    Append New Records table to Table B to add all new data.

    Totals Query newly appended Table B, and Group By name while filling only the latest email to mash records with the same names and end up with the latest email.

    Append Table A (name fields empty) to Table B.

    Totals Query newly appended Table B, and Group By email while filling latest names to weed out any potential duplicate individuals in any of the lists.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Three Fields One can be Empty
    By bgold01 in forum Queries
    Replies: 2
    Last Post: 04-16-2013, 10:50 AM
  2. Replies: 2
    Last Post: 04-23-2012, 10:13 PM
  3. Replies: 4
    Last Post: 11-20-2011, 01:08 PM
  4. Update Query on empty fields
    By bryan in forum Access
    Replies: 3
    Last Post: 01-18-2011, 11:36 AM
  5. Replies: 1
    Last Post: 12-12-2010, 05:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums