By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,637 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,637 IT Pros & Developers. It's quick & easy.

how to search and compare the data from three tables in a form

P: 40
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
Oct 19 '14 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,035
- 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'
Oct 19 '14 #2

P: 40
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
Oct 19 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.
Oct 19 '14 #4

P: 40
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
Oct 20 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,282
Here is my quick recommendation for your tables:

tblTrucks - Your Table A
Expand|Select|Wrap|Line Numbers
  1. Field    Type              Notes
  2. TruckID  Long, AutoNumber  Primary Key
  3. TruckNo  Text              Could be "10", or "10A" depending on your truck number convention--or, it could be an Integer data type
  4. TruckCap Long              Truck Capacity in Liters


tblConsigneeRates - Your Table B
Expand|Select|Wrap|Line Numbers
  1. Field          Type              Notes
  2. RateID         Long, AutoNumber  Primary Key
  3. ConsigneeID    Long, AutoNumber  Foreign Key to tblConsignees
  4. TruckCap       Long              Truck Capacity in Liters
  5. ConsigneeRate  Currency          How much you charge this Consignee for this size truck


tblInvoices - Your Table C
Expand|Select|Wrap|Line Numbers
  1. Field        Type              Notes
  2. InvoiceID    Long, AutoNumber  Primary Key
  3. ConsigneeID  Long, AutoNumber  Foreign Key to tblConsignees
  4. FinalRate    Currency          How much this Consignee is charged on this Invoice
  5. InvoiceDate  Date              Date of Invoice
  6. etc......


tblConsignees - New Table
Expand|Select|Wrap|Line Numbers
  1. Field             Type              Notes
  2. ConsigneeID       Long, AutoNumber  Primary Key
  3. ConsigneeName     Text
  4. ConsigneeAddress  Text
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboTruck_AfterUpdate()
  2.     Dim strSQL As String
  3.     strSQL = "SELECT RateID, ConsigneeID, ConsigneeRate " & _
  4.         "FROM tblConsigneeRates " & _
  5.         "WHERE TruckCap = " & Me.cboTruck.Column(2) & ";"
  6.     With Me.cboConsignee
  7.         .RowSource = strSQL
  8.         .Requery
  9.     End With
  10. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboConsignee_AfterUpdate()
  2.     Me.cboInvoiceConsignee = Me.cboConsignee.Column(1)
  3.     Me.txtFinalRate = Me.cboConsignee.Column(2)
  4. 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.
Oct 20 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.