Search and compare the value from three tables in a form

[irslan rafique]

Hi,
I have three tables: Table A, Table B and Table C
Fields of Table A: Truck No, Capacity
Fields of Table B: Consignee ID, Capacity A, Capacity B and
Capacity C
Fields of Table C: Consignee ID, Final Rate

Input form is based on Table C:
1-I select Truck No using a combo box and it shows the Capacity from Table A using DLookup.
2- I enter the id no. in the field of Consignee ID and it returns the values of Capacity A, Capacity B and Capacity C using DLookup from Table B.
So I have to write manually one final rate in the field of Final Rate in the form.
So I want:
When I enter id no. in the field of Consignee ID, it matches with the Capacity in Table A and shows the matched result only in the field of Final Rate of Table C.

Prompt assistance will be highly appreciated.

Thanks

 

[irslan rafique]

I am so sorry for not being so clear. As I am new to Access so please bear me. I am trying my best to give the details so please advice accordingly.

I have three tables:
1-Truck details,
2-Consignees
3-Main
Fields of Truck details: Truck No, Capacity
Fields of Consignees: Consignee ID, 43000L, 27000L and
21000L
Fields of Table Main: Consignee ID, Final Rate

Input form(Main Form) is based on Table Main
Main Form:
1-I select Truck No using a combo box and it shows the Capacity from Table Truck Details using DLookup.
2- I enter the id no. in the field of Consignee ID and it returns all the values of 43000L, 27000L and 21000L using DLookup from Table Consignees.
So I have to write manually one final rate in the field of Final Rate in the form.
I want:
When I enter id no. in the field of Consignee ID, it matches with the Capacity in Table Truck details and show the related value from(43000L, 27000L and 21000L)Consignees in the field of Final Rate of Table Main.
Like:
If Consignee ID is = 1 and its Capacity is (43000L) from Truck details Then
Show only the rate of 43000L from Table Consignees in the Final Rate field of Table main. All are present on the Main Form

Once again sorry and please help

 

[irslan rafique]

combo box is bound to the field of Truck No in Truck details....So please advice

 

[irslan rafique]

I used a unbound text box with DLookup. When Truck No is selected from the Combo box so it will show the result of Capacity.

 

[irslan rafique]

Yes bro..You are thinking perfectly....I need same thing as u drew in the pic

 

[irslan rafique]

Sorry:
#9 is going in correct direction. Thanks for sparing time for me
I am copying ur link:
https://docs.google.com/presentatio...xS2TGN5wYeTrRYOZamSAQU4/edit?pli=1#slide=id.p


Thanks

 

[irslan rafique]

Every Consignee ID has rates for 43000L, 27000L and 21000L. So when we insert Consignee ID, one of the three rates (43000L, 27000L and 21000L) will match with the capacity of truck (suppose 43000L) and that only rate should come in a field(final rate) which is bound to table.
Thanks