Microsoft Access Database – How can I get a report to display names that are looked up, rather than their IDs?

I’m setting up a database for a DVD Rental shop in Microsoft Access 2007.

One of the tables called ‘DVDs’ in the database stores data about each DVD e.g. Title, Release Date etc. See picture: http://i33.tinypic.com/2qwokzk.png

One of the fields in this table is called ‘Producer’ and looks up a list of producers stored in a separate table called ‘Producers’. Picture: http://i34.tinypic.com/2ik5xj9.png

In the ‘DVDs’ table, the row source for this lookup value is: SELECT DISTINCT DVDs.Producer FROM DVDs ORDER BY DVDs.Producer;
(See picture: http://i37.tinypic.com/23r8uus.png)

I’ve created a form for the table (picture: http://i36.tinypic.com/2wpib2t.png), and for the producers’ names to be shown, rather than their IDs, the following row source is used in the form: SELECT Producers.ID, [firstname] & " " & [lastname] AS producer FROM Producers ORDER BY Producers.LastName;

I’d like to create a report to display the data stored in the ‘DVDs’ table, however I am unable to find a way to display the producers’ names rather than their IDs in the report. At the moment, with using the same row source as I did in the form, the report looks like this: http://i34.tinypic.com/28ukwur.png

I would be extremely grateful if you could find a way to display the producers’ names rather than their IDs in the report, as I have an upcoming deadline to meet with this project.

Many Thanks!

P.S. Here’s a screenshot of the relationship between the ‘Producers’ table and the ‘DVDs’ table, if it’s necessary to solve the problem: http://i33.tinypic.com/2446r1z.png

Regards

Other Sites Movie Rental New Releases Site Online

• Setting up a read-only DSN in Coldfusion « pukkared