INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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!!!


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

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

CODE

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: How to get the best answers before posting.

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

(OP)
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.

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

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: How to get the best answers before posting.

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

(OP)
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.

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

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

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

Something like this might be more appropriate:

CODE

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 wink

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: How to get the best answers before posting.

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

(OP)
HQ -

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

That worked perfectly. Thanks!!

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

Thanks, glas I could help in the end smile

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: How to get the best answers before posting.

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

Thanks, glad I could help in the end smile

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: How to get the best answers before posting.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close