Update Query - Zero Rows Updated

[WillM]

I have two tables that I am combining together.

tbl1_combined has all the newest information.
tbl2_import has the old information.

I have a column, census, in table 2 that I need to get into table 1.

table 1 is structured a little weird, but here is what it looks like

Contract Number (number)
FQ (Fiscal Quarter and Fiscal year, text field)
Census (number)

*fields types are the same in both tables*

So I would have in table 1
contractNo: 1000
FQ: 2015 Q3
Census: 50


However, not by my design, the original census for prior quarters was not copied into the new table. So I need to bring the original census into the new table where it matches the FQ and Contract.

Example:
t1
contractNo: 1000
FQ: 2015 Q3
Census: 50

t2 (with the correct census)
contractNo: 1000
FQ 2015 Q3
Census: 38

I cannot do a straight copy because there are multiple entries per contract with varying FQs and they all contain their own census as well.

Example:
Contract 1000 might have a result from 2014 Q1, 2014 Q3, and 2015 Q1...with each of these results, there is a census and other information that must remain with the result.

This is the code I am trying to use, but I keep getting the "this will update 0 rows" prompt when I run the query. I know that the data in both tables matches as far as the FQ column and ContractNo goes.

UPDATE tbl1_Combined, tbl2_Import

SET tbl1_Combined!Census =  tbl2_Import!Q1Census

WHERE ((tbl1_Combined!FQ = tbl2_Import!Q1FQ));

My first question: Is it even possible to do an update query based on a text field without making it a string literal? If so, what I am missing? If not, how do I do that?

My second question: Is there a better/easier way of doing this?

Thanks again, you guys rock!
Will

 

[The_Doc_Man]

As long as referential integrity is not your problem (and I can't tell that from what you have posted), you might be able to do this:

UPDATE tbl1_Combined JOIN tbl2_Import

ON tbl1_Combined!FQ = tbl2_Import!Q1FQ

SET tbl1_Combined!Census =  tbl2_Import!Q1Census ;

The other way appeared to be an attempt to update two tables at once in a Cartesian JOIN and probably confused the heck out of Access.

If the UPDATE/JOIN doesn't work (and Access is known to be picky about UPDATES), you could also write a SELECT query that did this JOIN separately and then do an update query on the SELECT/JOIN query. The only catch in either approach would be if relational integrity were declared involving the updated field on tbl1.

 

[WillM]

Thank you, I will give that a shot.