Update a Checkbox from an Excel List

I come from PHP / MySQL so I don't understand how to do this in Access.

Client has an access database with two tables. One of those tables (contacts) has an email address field.

He has an excel spreadsheet with a list of 843 email addresses that are known bad (bounced, does not exist, etc...)

What I want is to do a For loop on the list, and update the table. "For each value in the excel list, update table and set bademail=true where email=[email in the excel list]".

How do I do this in Access?


 Contact Bounce

query-results.png
LVL 32
DrDamnitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
run an update query

update contact
inner join [bounce emails] as bm on contact.cemail=bm.cemail
set cbademail=-1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G_Hosa_PhatCommented:
Here's a SQL command that should work.  Note that it will update ALL of the records in the CONTACT table that are found in the bounce emails 3-14-11 table and set the CBADEMAIL flag to "1" (or "True")
UPDATE (CONTACT c INNER JOIN [bounce emails 3-14-11] b ON c.CEmail = b.CEmail) SET CBADEMAIL = 1

Open in new window


The quickest way I've found to create such a query is to simply start the Query Wizard, but don't add any tables when prompted.  Then you can quickly change the View to the SQL view (in MS Access 2007, it should be directly under the Microsoft button), and enter the above SQL statement.
G_Hosa_PhatCommented:
I failed to note a couple of things in my reply above that I just wanted to clarify.  DrDamnit, I'm sure you're probably aware of most of these, especially having been working with PHP/MySQL.  Still, just for the sake of those who may still have questions  I thought I'd try to pre-empt some that I can foresee:

1) In order to use the above query, you will need to have the Excel data imported to a table in the Access DB.  This can be quickly and easily done from the External Data tab on the Office Ribbon (I hate that I can't remember exactly where it is in 2000/2003).

2) In order to implement a For... Next type of thing, you'd really need to use either a script/macro, or build an application that would loop through the records of the Excel sheet, then look for a matching record in the Access database.  If you've already imported the data, a single UDPATE statement is much more efficient at that point for merging the data than a loop - especially when you're dealing with a large number of rows.

3) The nice thing about a single UPDATE query like this is that you can save the query, then simply change the name of the second table ([bounce emails 3-14-11]) the next time you import a new Excel sheet and just hit the "Run" button.  Again, it'll update all of the matching records in the CONTACT table in one quick shot, without having to mess with a script/macro or external application.
GRayLCommented:
I think we are losing sight of the fact that we should not be saving derivable data.  The query should point out those addresses which are bad:

SELECT a.CFirstName, a.CLastName, a.CEmail, Format(a.CEmail = b.CEmail, "Yes/No") AS GoodEmail FROM Contact a LEFT JOIN [bounce Emails 3-14-11] b ON a.CEmail = b.CEmail;

By using aliases a, and b, you can reuse the query by changing just the file name of the bounced emails in one place.
DrDamnitAuthor Commented:
The point ( as bad form ad it might be) is to save the derived data.

Thank you all for the solutions. Got it working.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.