how to search and compare the data 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

[Luuk]

- It's unclear how 'capacity' and 'rate' are related

"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."
- It's unclear what 'the matched result' is, because there are 3 columns with 'capacity' in TableB

Tip: give your table names a meaningful name, in stead of 'Table A' you could user 'Truck'

[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

[twinnyfo]

1. It sounds like your tables may not be properly normalized. Please review this article (Database Normalization)

2. It seems like you keep wanting to use DLookup when you don't have to. This is highly inefficient. If your Combo Box selects a truck, why not just include the capacity of that truck in your list of columns? There should never be a need for you to select a truck (from a list of trucks in your table), and then have that value use a DLookup to find other details about that same truck from the same table.

3. If this is the case, however, after you have selected the truck, you should repopulate the Combo box for the consignee based on the size of the truck (reset the row source for the combo box). Then, when you select the consignee--use a combo box again--don't "type in" the consignee id--the value returned in the combo box will be the rate for the same size of truck. Then, update that value into your final rate.

[irslan rafique]

Your article is quiet nice. Can you advice me what to do now please? Sorry I am very new to access.
Please assist me accordingly

[twinnyfo]

Here is my quick recommendation for your tables:

tblTrucks - Your Table A

Field    Type              Notes
TruckID  Long, AutoNumber  Primary Key
TruckNo  Text              Could be "10", or "10A" depending on your truck number convention--or, it could be an Integer data type
TruckCap Long              Truck Capacity in Liters

tblConsigneeRates - Your Table B

Field          Type              Notes
RateID         Long, AutoNumber  Primary Key
ConsigneeID    Long, AutoNumber  Foreign Key to tblConsignees
TruckCap       Long              Truck Capacity in Liters
ConsigneeRate  Currency          How much you charge this Consignee for this size truck

tblInvoices - Your Table C

Field        Type              Notes
InvoiceID    Long, AutoNumber  Primary Key
ConsigneeID  Long, AutoNumber  Foreign Key to tblConsignees
FinalRate    Currency          How much this Consignee is charged on this Invoice
InvoiceDate  Date              Date of Invoice
etc......

tblConsignees - New Table

Field             Type              Notes
ConsigneeID       Long, AutoNumber  Primary Key
ConsigneeName     Text
ConsigneeAddress  Text
Etc.....

Your Form should be bound to tblInvoices. When you move to a new record, you can use the following Combo Boxes.

On your Form, have a combo Box (cboTruck) with three columns, Row Source is:

SELECT * FROM tblTrucks;

When you select a truck, you will want to be able to select consignees, based on the size of the selected truck. So, you will also have another Combo Box (cboConsignee) with three columns.

In the AfterUpdate Event of that Combo Box, use the following VBA:

Private Sub cboTruck_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT RateID, ConsigneeID, ConsigneeRate " & _
        "FROM tblConsigneeRates " & _
        "WHERE TruckCap = " & Me.cboTruck.Column(2) & ";"
    With Me.cboConsignee
        .RowSource = strSQL
        .Requery
    End With
End Sub

Then, when you select a Consignee from that Combo Box, you want to save the Consignee data to the Table, along with the rate charged on this invoice.

In the AfterUpdate Event of that Combo Box, use the following VBA:

Private Sub cboConsignee_AfterUpdate()
    Me.cboInvoiceConsignee = Me.cboConsignee.Column(1)
    Me.txtFinalRate = Me.cboConsignee.Column(2)
End Sub

This assumes that your form has a bound combo box (cboInvoiceConsignee) that uses ConsigneeID as a foreign Key and a bound Text Box for the FinalRate. Then just add other controls for the date of the invoice, etc.

I hope this made sense.

It is a much more direct method for assigning proper invoice values.

Please note, I have not tested this code, and only freehanded it, but it should get you in the right direction with your project.