Referencing a lookup field in a separate table in Lookup field (1 Viewer)

[Anthony.DG]

Let me setup my issue. I have 3 tables labeled Carrier TruckNumber and Invoice Table TruckNumber has a lookup field Carrier referring to table Carrier field CarrierName.

Now what i want to do is in the 3rd table Invoice I want to create a lookup field TruckNumber to table TruckNumber displaying field TruckNumber in column 1 and field Carrier in column 2. I can do this just fine but the problem is that column 2, referencing lookup field Carrier displays the field CarrierID from table Carrier but i want to see what name the truck number is next to.

We have our own trucks/trucknumbers and we log other carriers trucks/trucknumbers that come and pick up material from our yard. We keep track in case something gets brought up and we can refer to who with what truck took what material. Some companies have the same numbers so we want to keep them separate. I want to make sure that the truck number selected in invoice goes to the correct carrier.

Can it be done or should i rethink my structuring?

 

[jdraw]

Avoid Lookup fields in Tables. Use a lookup table.

 

[mike60smart]

Hi Anthony

Can you upload a zipped copy of your Db??

 

[The_Doc_Man]

Although it seemed like a good idea at the time, the "lookup field" where you have a field that "contains" multiple values based on some internal index has proved to be very bad in complex operations. Using a JOIN query to manage a lookup is far better in long-term stability and manageability.

Using a JOIN query, you would link your tables using the ID field but in the query, you can pull the name or number of the thing that the ID references. Queries are your friend. If you have a formalized relationship between your tables, then the query design grid, which is a type of "wizard" in Access terms, will recognize and honor the relationship and will build the proper JOIN syntax for you.