Text of foreign keys

[mainerain]

tblEvent contains foreign keys from multiple tables. The foreign key in tbleEvent is the autonumber from the "reference" table. I then run a query on tblEvent and get a result. How do I display on a report the underling text of the foreign key and not the autonumber of the foreign key?

[orange]

How about posting a copy of your relationships window? It would be helpful to readers to see your table designs and relationships in context.
It would also be helpful if you would post the SQL of your query.

[mainerain]

Thanks for the reply. I can see I need to work on my relationships... to be continued

[orange]

Here's a link that may offer more info on foreign keys.

Here's another on relationships.

[Welshgasman]

tblEvent contains foreign keys from multiple tables. The foreign key in tbleEvent is the autonumber from the "reference" table. I then run a query on tblEvent and get a result. How do I display on a report the underling text of the foreign key and not the autonumber of the foreign key?

You join to the tables that have the foreign key as their primary key?
That is the whole point of foreign keys?

My links table does the same thing. It contains NOTHING except links to other tables and it's own primary key.

[June7]

Whether or not you build relationships in Relationships window, report RecordSource would be a query that joins tables and binds textboxes to appropriate fields.

[davegri]

In regards to your original question, one way is to use a query to include the desired fields, like this:



You can even omit the foreign key fields if not needed by the query end result.

[June7]

Dave, why does the link arrow for tblGenre appear backwards?

[davegri]

Dave, why does the link arrow for tblGenre appear backwards?

Appear? It is backwards! Good eye. I pulled the query out of an existing DB. I'll have to check out that DB and see what's going on. '¯\_(ツ)_/¯

Edit: Checked it out. The example is correct for the situation. Genre is not present for all books, so the join type shown will pull all books regardless.

[June7]

Okay. Had to explore that relationship so built tables and query. It's actually tblAuthors link that I would expect to see the other direction, in other words, both links the same type - RIGHT JOIN. However, seems to produce same output if tblAuthors link is LEFT JOIN.

Why would a book not have genre? They all have author?

[davegri]

Why would a book not have genre? They all have author?

Genre was added a few months after go-live. A lot of work to add the Genre data to hundreds of books. Still not done.