Results 1 to 14 of 14
  1. #1
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9

    Using an Update query to autofill boolean

    Here is my issue, between Access and Excel I need to be able to export an e-mail contact list from my database. The problem is, my e-mail list has 130000 records, most of which are blanks or duplicates.



    I have multiple tables in my database that I use append queries to send all the e-mail records into one central table.

    E-mail Table (Field1 = E-mail, Field2 = E-Mail Bool, Field3 = E-mail Date Sent?)

    Comment: E-mail Bool is just a field that is either blank or says Yes.

    From this table, I use a select query to select just the e-mail field and the e-mail bool field. From there I need to extract all the e-mails that I have not sent e-mails to already and export that into excel. However, some e-mails are added in to my table as duplicates after I have already sent messages to those e-mails, so they are not marked sent.

    How is it I can select the records that are BOTH not duplicate e-mails AND that I have not sent messages to already.

    Thank you, please help

  2. #2
    Join Date
    May 2010
    Posts
    339
    First thing I would do is strip out all the dupes. Is this a single table database with a phone field?




    Richard

  3. #3
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    This is a 5 table database with many fields.


    Each table is a different source of contacts from other databases we have in our office. Then I append the fields I want (E-mail and E-mails Sent) to a sixth table by itself. In theory I would be able to export all of the contacts I have NOT e-mailed yet into my outlook and send my newsletter via distribution list from this sixth table.

    Again, the problem stems from that when I get contacts from my company's other databases, I have already sent e-mails to SOME of those same contacts and I am not allowed to send the same e-mail twice. I need to be able to filter out the duplicates and blanks while simultaneously filtering out e-mails I have already mailed.

  4. #4
    Join Date
    May 2010
    Posts
    339
    Here is a link that you can use on your central table it will help you to remove all the dupes and blanks. Once your dupes are gone then post back maybe we can take it step by step.



    http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html

  5. #5
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    Quote Originally Posted by Access_Blaster View Post
    First thing I would do is strip out all the dupes. Is this a single table database with a phone field?




    Richard

    I used a select query on my central table to display unique values, and here is what it gave me, from the 130 k records, it reduced them down to over 1000 unique records, but the records are not entirely unique:

    Sample Data:

    E-Mail(Text Datatype) E-Mail Sent?(Text Datatype)

    soandso@yahoo.net Yes
    soandso@yahoo.net *Blank*
    showersrus@bored.org *Blank*
    fishsticks@gmail.com Yes
    fishsticks@gmail.com *Blank*
    kenandbarbie@hotmail.com *Blank*
    kenandbarbie@hotmail.com Yes



    As you can see, the records are unique values in that the "e-mails sent?" field is giving a positive and a negative answer. NOtice showersrus@bored.org, it is unique AND displays a *Blank* in the "e-mails sent?" field. I need to be able to pull out all the e-mails that are not duplicates and ONLY display *Blank* in that field.

    I hope this helps.

  6. #6
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    Please note, I can run a find duplicates query on my previous SELECT query, but I am not convinced if this will help. I may have to make an append query first and make a while new table with my unique values first, then do a find duplicates query, where it searches for duplicates and in which I can delete those duplicates to leave the truly unique unsent records.

    There is just one problem, when I set the first Select Query to unique values property, what happens when I get two records with the same e-mail address and its "e-mails sent" field is blank for both records?

    Example with Sample Data:

    showersrus@bored.org *Blank*
    showersrus@bored.org *Blank*

    Wouldn't the above two records be filtered out with the Unique Values Property set to yes?

    Zomg this is soo tough!

  7. #7
    Join Date
    May 2010
    Posts
    339
    Don't worry about setting up a dupes find query that was just his example showing how many dupes he had to start with...

    Did you follow the instructions on the video and set primary keys across several fields on a new table?

    Example with Sample Data
    showersrus@bored.org *Blank*
    showersrus@bored.org *Blank*

    In your sample after you run the append query (from the link) one of those records would be gone.

    Is the *blank* where you have a yes/no field?

    http://www.datapigtechnologies.com/f...teproblem.html

    Don't forget to turn up the speakers


    Richard

  8. #8
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    Quote Originally Posted by Access_Blaster View Post
    Don't worry about setting up a dupes find query that was just his example showing how many dupes he had to start with...

    Did you follow the instructions on the video and set primary keys across several fields on a new table?

    Example with Sample Data
    showersrus@bored.org *Blank*
    showersrus@bored.org *Blank*

    In your sample after you run the append query (from the link) one of those records would be gone.

    Is the *blank* where you have a yes/no field?

    http://www.datapigtechnologies.com/f...teproblem.html

    Don't forget to turn up the speakers


    Richard
    Ty for your effort with me, I am still merely a beginner at this. The *Blank* is in theory a yes/no field even tho its a text datatype. Blank means no and yes means yes. I didn't want to use a true yes/no datatype because I didn't know how to update all my records with a no. Reason being, I had them display the actual date an e-mail was sent out, but that made it harder to deal with duplicates.

    Which of the two records would be gone after the append query?

    EDIT: I set my primary keys to the E-mail field and the yes/no field (note the yes/no is NOT a yes/no datatype, it is text)

    Now I can see unique records of all the e-mails I have sent, I need to see the unique records of e-mails I have not sent. This is the closest I have ever been except its showing me the opposite of what I want to see.

  9. #9
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    I have experimented with that method however I am having a hard time deciding which fields will be my primary keys.

    (Please see previous EDIT) above

  10. #10
    Join Date
    May 2010
    Posts
    339
    What I would do if I were in your shoes is make a backup copy of your database. Its not a problem that your a beginner, we have to get the data in our tables right or it makes our life soooooo much harder then it needs to be.
    Example with Sample Data
    showersrus@bored.org *Blank*
    showersrus@bored.org *Blank*
    I believe the first dupe it comes across will be deleted, I could be wrong. A check box yes/no field would in my opinion be much better then a textbox that’s either empty or not... I have a contacts database where I use a checkbox on my form. When I check the box the after update property fires and places the current dated in a field, its name is DateCalled the same thing can be done for the time. You can then query the checkboxes to see if there null and build reports or whatever. Are you using forms for input?

  11. #11
    Join Date
    May 2010
    Posts
    339
    If you would like to upload a copy of your DB converted to Access 2000 I would look at it. Or you can use my yahoo on the top right of this box.



    Richard

  12. #12
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    Quote Originally Posted by Access_Blaster View Post
    What I would do if I were in your shoes is make a backup copy of your database. Its not a problem that your a beginner, we have to get the data in our tables right or it makes our life soooooo much harder then it needs to be.

    I believe the first dupe it comes across will be deleted, I could be wrong. A check box yes/no field would in my opinion be much better then a textbox that’s either empty or not... I have a contacts database where I use a checkbox on my form. When I check the box the after update property fires and places the current dated in a field, its name is DateCalled the same thing can be done for the time. You can then query the checkboxes to see if there null and build reports or whatever. Are you using forms for input?
    No I am just using import macros or external data functions for input.

    I am going to change the datatype for the yes/no field and test out the primary keys once more.

  13. #13
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    Ok, I changed the datatype of my E-mail sent field to yes/no and updated every table in my database with an update query to reflect the correct yes and no.

    I used the datapig method but it still brought me to square one, that being, the unique values property set to yes on my original e-mail table.

    so this is what I see:

    showersrus@bored.org - Yes
    showersrus@bored.org - No

    when I shouldn't see either

    Thanks

  14. #14
    Fuzzyjello is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    9
    Woot,

    I set the primary key to just the e-mail and it is now showing me exactly what I need.

    Thanks a lot guys, your clues and tips allowed me to fix this problem.

    This was priceless.


    Consider this solved.

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

Similar Threads

  1. date calculation then autofill?
    By nktrygg in forum Access
    Replies: 6
    Last Post: 01-19-2010, 11:04 AM
  2. Update Query- selective update?
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-29-2009, 11:15 AM
  3. Autofill with data from another form
    By jacrum in forum Forms
    Replies: 2
    Last Post: 08-11-2009, 11:17 AM
  4. Autofill form fields
    By roboticsguy1988 in forum Forms
    Replies: 1
    Last Post: 08-10-2009, 08:56 AM
  5. How to query boolean values from table
    By kevdmiller in forum Queries
    Replies: 2
    Last Post: 11-30-2006, 07:41 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