how to update a filed of table A by entirng data on Table B

[armankln]

Dear Friends,

It's my bad luck or not I dont know. Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. The Sample file is attached.

Here is my problem.


I have 3 Table on mdb file, named Table: A, B, Status
Table Status have One Filed with 1 Data: Dishonor

Table A have three fields

Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number

Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)


After Entering Some data on Table A it's Look like as:

A

sl number	status	amount
1	Honor	5222
2	Honor	855
3	Honor	988
4	Honor	7777
5	Honor	777
6	Honor	9999
7	Honor	6666
8	Honor	7777
9	Honor	666
10	Honor	76767
11	Honor	99
12	Honor	6
13	Honor	6
14	Honor	5
15	Honor	5
16	Honor	7
17	Honor	5
18	Honor	4
19	Honor	5

Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following

sl number	status
5	Dishonor

Now how I can get the result as following by using query:

sl number	status	amount
1	Honor	5222
2	Honor	855
3	Honor	988
4	Honor	7777
5	Dishonor	777
6	Honor	9999
7	Honor	6666
8	Honor	7777
9	Honor	666

Please help me:
ArMaN

[live2ride]

what you have is fairly confusing. what are you trying to do? do you just need an update query? if so you need relationship of some sort. here you have two many tables. table stats needs to be eliminated. table b should be changed to slNumber auto number, and status needs records such as Honor and Dishonor. then you need to make relationship between table A and table B or table A "status" field to have lookup of table B

[armankln]

can you send me a sample file? according to your solution @ live2ride



N.B: I need a query where relation shows the last table as shown as on my post

[live2ride]

Example.mdb
attached is the file with few different examples. example is one where you store an id of status field and exmple 2 is where you store name of status. i have added update query as an example of how to update fields. not sure what version youre using, this is build in 2010 and converted to mdb so not sure how it will look like but let me know if you have any questions