How can I copy the value of a cell from Table2 into Table1

[gurbaitis]

Hello -

A quick overview: I have a Table1 which reports on accounts that had a varrience. I have a Table2 which reports on accounts that managed their account online.
I want to add a column to Table1 called eCommerce, and if they exist in Table2, put "Online" in that field in Table1.

Table1 is created through a long process they don't want to change, Table2 I created through a query. Then both have acct. #s to match on.

I did one small change and added: DoCmd.RunSQL "ALTER TABLE Table1 ADD eCommerce STRING" to at least create the column, which now comes up blank.

But - how do I populate that field if it exists in Table2? Note that Table1 & Table2 can have some matching records or no matching records.

Thanks!!!

 

[HarleyQuinn]

Use an UPDATE query that join's the matching fields and UPDATE's the new column. Something like:

UPDATE table1 SET eCommerce = 'Online'
WHERE table1.MatchingField = table2.MatchingField

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gurbaitis]

HQ -

If I add that, it prompts me for table2.MatchingField, instead of looking at table2 & finding all the matches. It's as if it needs to run through table2 for each record in table1.

 

[HarleyQuinn]

What I meant by MatchingField was the fields in your two tables that link to each other (having missed it in your original post I now see it's acct no.).

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gurbaitis]

HQ -

Thanks. Yes, I understood what you meant - in my case the line is: DoCmd.RunSQL "UPDATE Variance_Report SET eCommerce = 'Online' WHERE Variance_Report.Acct_Num = tblReportForms_eComm.COCDB_NUM". I've made sure I don't have a typo - the table is indeed tblReportForms_eComm & the column is COCDB_NUM. If I enter one COCDB_NUM that matched an Acct_Num when prompted it does put in 'Online' - but how to get it to not prompt is the problem.

 

[HarleyQuinn]

Sorry, I guess you sometimes get too used to people taking what you post too literally (which I myself was guilty of there .

Also, apologies, I don't know where I pulled that syntax from but I can guess...

Something like this might be more appropriate:

UPDATE Variance_Report INNER JOIN tblReportForms_eComm ON Variance_Report.Acct_Num = tblReportForms_eComm.COCDB_NUM SET Variance_Report.eCommerce = "Online";

Sorry for for leading you up the wrong path there.

Hope this actually helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gurbaitis]

HQ -

Please! Don't be sorry - I appreciate the help!

That worked perfectly. Thanks!!

 

[HarleyQuinn]

Thanks, glas I could help in the end

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Thanks, glad I could help in the end

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.