Trouble creating Join

[dmyoungsal]

I have a view in which I want to join two tables, but each time I try doing a join, I get an error message saying it is not donw.

I have a table (tblTrailer) with 4 fields. TrailerID (auto); TrailerLic (Text, because License Plate have alpha); Trailer #; Trailer Carrier

The other table (tblWashInfo) I have a number of fields of which WashLic# (which is a text field), but in my DataPage, uses a pull down from tblTrailer).

What I ultimately want is for the user to select the License # and have the carrier name and trailer number appear on the screen.

But when I change my field types (in table) and then try to Join I get the error.

Thoughts on design? As I write this, I think my table design is incorrect, but your thoughts are appreciated

[Vitalikssssss]

Hi @dmyoungsal,

I think you will be able to resolve this case by creating a one-to-many relationship.

https://www.youtube.com/watch?v=IstAk982ntA

You can create a self-join View if your table have a one-to-many relationship.

https://howto.caspio.com/tables-and-views/what-are-views/self-join-views/

Hope this helps.

Regards,

vitalikssssss

[ManokNaPula]

Hi there.

I think the thing that you are missing is the foreign key. A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.

Say in your case, you wanted to create a relationship from tblTrailer to  tblWashInfo, you need to have a foreign key of the field ID to the other table. 

In addition, you may want to check out these links to quickly understand table relationships.

1. Data Normalization: https://www.youtube.com/watch?v=oexOYUUyQik  
2. One-to-Many Relationships: https://www.youtube.com/watch?v=IstAk982ntA
3. Many-to-Many Relationships: https://www.youtube.com/watch?v=7D8u6Lb2BKU

Also, if you want to change the display value, you can do so within the Table Relationships. You simply need to change this: http://prntscr.com/mdyjfj

Hope this helps!