Using an Update query to autofill boolean

[Fuzzyjello]

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

[Access_Blaster]

First thing I would do is strip out all the dupes. Is this a single table database with a phone field?




Richard

[Fuzzyjello]

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.

[Access_Blaster]

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

[Fuzzyjello]

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.

[Fuzzyjello]

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!

[Access_Blaster]

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

[Fuzzyjello]

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.

[Fuzzyjello]

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

[Access_Blaster]

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?

[Access_Blaster]

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

[Fuzzyjello]

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.

[Fuzzyjello]

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

[Fuzzyjello]

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.