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.
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
Rounder says
you can do one of two things
the first will require more work and potential bugs but you would need to change the lookup for each field you have that you want something other than the currently selected ID field to display. Change the bound column in the lookup to whatever column you want displayed (make sure the field types match.
the second and my recommend way would be to create a query and include the appropriate reference to the ‘description’ field you want displayed. then use the new query as your data source for the report.