showing a primary key field data in the foreign key field source table

[HENRY ONYEAGBAKO]

In the sample Northwind database the products and orders tables are linked
in a many to many relationship by the junction table order details. The
order details table rather than showing the product ID number it displays
the products name. I have created a similar relationship with two of my
tables in my database they being Films and Actors linked by the junction
table Film Actors but have been unable to copy Northwind by displaying the
Actors name in Film Actors. Any suggestions welcome

 

[Tim Ferguson]

I have created a similar relationship with two of my
tables in my database they being Films and Actors linked by the
junction table Film Actors but have been unable to copy Northwind by
displaying the Actors name in Film Actors.

Well, for a start will you humour me by calling the third table something
sensible like "AppearsIn" -- reasons become obvious later on.

Now, assuming you have a form based on the Actors table and a subform to
display the appearances. This subform should be based on a query that
gathers the actual appearance information from AppearsIn and the film
information from Films, like so:

SELECT AppearsIn.ActorID,
AppearsIn.FilmNumber,
Films.FullTitle
FROM AppearsIn LEFT JOIN Films
ON AppearsIn.FilmNumber = Films.CatalogNumber
ORDER BY AppearsIn.ActorID, Films.FullTitle

Note that you have to include the AppearsIn version of the FilmNumber field
(not the Films.CatalogNumber), and that Access will happily fill in the
film title when you insert a valid FilmNumber.

If you want to make the entering of the FilmNumber easy, you can create a
combo box that is _bound_ _to_ the AppearsIn.FilmNumber, but gathers its
RowSource from the Films table, specifically the CatalogNumber and
FullTitle fields. The bound column is CatalogNumber, and hidden, and that
leaves the FullTitle field visible. (Of course, if you do decide to do
this, then you can get rid of all the Films stuff in the subform query,
which makes life easier too.)

Hope that helps



Tim F

 

[Steve Schapel]

Henry,

The way this has been done in Northwind is that the CustomerID field is
a Lookup field. As you will see from
http://www.mvps.org/access/lookupfields.htm, this is not a recommended
method. In any case, since tables should really be considered as data
storage, how the data is displayed is not of much consequence anyway.
If you have a form (or subform, more likely) based on your Film Actors
table, you can have a combobox bound to the ActorID field, and the
Actors table as the Row Source of this combobox. If, for example, the
ActorID field is the first field in your Actors table, and ActorName is
the second, you would set up the properties of the combobox like this...
Bound Column: 1
Column Count: 2
Column Widths: 0;x
(where x is a suitable width for the display of the actor's name.)