Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: (MS Access 2007) Linking 2 Tables


(!)

blue77's Avatar
blue77 blue77 is offline
Member with 32 posts.
THREAD STARTER
 
Join Date: Aug 2012
17-Sep-2012, 05:52 AM #1
Solved: (MS Access 2007) Linking 2 Tables
Hello, I am using Windows 7 and MS Access 2007. I have 2 tables. Both tables have the "NAME" field. I also have the "OK" field with data type of "Yes/No" in the second table. These two tables have different records of NAME. But sometimes, these NAMEs match each other. What I want to ask is, how can I link these two tables so when there are records with the field of "NAME" that match in two tables, the field of "OK" will be filled with value "Yes". Otherwise, the value is "No".

Any help would be appreciated. Thanks.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,441 posts.
 
Join Date: Mar 2005
Location: UK
17-Sep-2012, 07:00 AM #2
You would use 1 or 2 queries to do this, the first query to match the names and the second to "Update" the "OK" field, it may be possible to do it with just one "Update" query.where the names are matched.
Mhouser's Avatar
Mhouser Mhouser is offline
Computer Specs
Member with 136 posts.
 
Join Date: Jan 2008
Location: USA
Experience: Advanced
17-Sep-2012, 07:33 AM #3
would something like this work for you?

UPDATE TABLENAME
SET OK=Yes
WHERE NAME IN (SELECT NAME FROM OTHERTABLE)

UPDATE TABLENAME
SET OK=No
WHERE NAME NOT IN (SELECT NAME FROM OTHERTABLE)
blue77's Avatar
blue77 blue77 is offline
Member with 32 posts.
THREAD STARTER
 
Join Date: Aug 2012
18-Sep-2012, 12:39 AM #4
I create 1 query consists of NAME from TABLE1, NAME from TABLE2, and OK from TABLE2. Here is how I fill the query:
1. Fill the Criteria under the column of NAME from TABLE2 with: "=[TABLE1].[NAME]"
2. Fill the Update To under the column of OK with: "Yes"

Try running the query and it shows only the Field of OK with NO from top to the bottom even though there are several records in two tables that match each other in term of NAME.

Oh, and by the way, is this method worked if I the NAME is not in the same record but have the same value? For example, there is "Bryan" in the first record from TABLE1 and there is another "Bryan" in the eleventh record from TABLE2.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,441 posts.
 
Join Date: Mar 2005
Location: UK
18-Sep-2012, 08:11 AM #5
Can you post a dummy database?
blue77's Avatar
blue77 blue77 is offline
Member with 32 posts.
THREAD STARTER
 
Join Date: Aug 2012
21-Sep-2012, 12:31 AM #6
I have attached the dummy database.
Oh, in the query, I just put both table inside and have not done any update because whenever I make the update and then close the database, the query is not open-able saying that it is in Disable Mode.
Attached Files
File Type: rar Dummy.rar (13.3 KB, 4 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,441 posts.
 
Join Date: Mar 2005
Location: UK
21-Sep-2012, 06:04 AM #7
OK, the query now shows 2 common names between the 2 tables, Bryan & Charlie.
It updates the OK field for those 2 to Ticked, the rest stay Unticked.
Attached Files
File Type: zip Dummy v1.accdb.zip (16.6 KB, 8 views)
blue77's Avatar
blue77 blue77 is offline
Member with 32 posts.
THREAD STARTER
 
Join Date: Aug 2012
22-Sep-2012, 05:34 AM #8
Woah!!! I don't know that it would be this easy.
Thank you very much, OBP.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑