Update field when 2 fields match

[cdoyle]

Hi,
It's been a long time since I've really worked in Access.
But today I have a project that's been asked and trying to figure out how to do it.

I have a spreadsheet with several columns, 'll call this Table 1
columns:

Email Address | 10-18 | 10-22 | etc.

I then have some metrics I've pulled from another tool that is also in a spreadsheet. The spreadsheet (table 2) is users who opened a newsletter for the date I pulled it for. So let's say the first spreadsheet is 10-18

What I need to do is in Table 1, under the 10-18 column if the user is listed in (table 2) put a Y in that row.

I thought I could do this with some type of update query, but not sure how to build it.

I can do a select query and get the rows that match in both, just not sure how to get the update query to work.

 

[Uncle Gizmo]

>>> Email Address | 10-18 | 10-22 | etc <<<

Once you name some columns similarly like that then stick "etcetera" on the end then this flashes big warning signs all over the forum! Computer fans start humming loudly as the CPU goes into overdrive!

 

[cdoyle]

>>> Email Address | 10-18 | 10-22 | etc <<<

Once you name some columns similarly like that then stick "etcetera" on the end then this flashes big warning signs all over the forum! Computer fans start humming loudly as the CPU goes into overdrive!

These are multiple spreadsheets, that I need to compile into 1. One is an export from another program. Sorry, but that is the way it is. I need a way to automate the process of marking down if someone has read a newsletter or not for a time periods.

Both spreadsheets have an email column that I can join together. I thought importing these into Access to get the Select query (which works) was the way to go. I just now need to somehow update the column I specify in table 1 with a 'Y' for each row there is a match.

This isn't a database that needs to maintain data ongoing, it's for a one time report.

If you know of a way to do it in Access or even Excel that would be greatly appreciated.

 

[CJ_London]

have you tried using an update query?

UPDATE table1 INNER JOIN table2 ON table1.email = table2.email SET table1.[10-18] = "Y"

 

[cdoyle]

have you tried using an update query?

UPDATE table1 INNER JOIN table2 ON table1.email = table2.email SET table1.[10-18] = "Y"

Ya that's actually the exact query I used, but when I try and run it. I get an error that says 'Operation must use an updatable query'

I don't know what that means though.

 

[CJ_London]

well - google it to find out more. take a look at this link which might provide the solution

http://www.allenbrowne.com/ser-61.html

alternatively in excel use the if, isna and vlookup functions to populate your fields