Avoiding displaying an autonumber field in a report?

[C Tate]

I have created a small database which is really just a modified version of
the contact management template. Most of the contacts I am recording are
organisations we work with. These organisations usually have a 'link
officer' who is a member of our team. I have therefore created another
table, officers, which has an employeeID (autonumber), then firstname and
lastname fields (which are text). EmployeeID is a foreign key in the
contacts table.

I am now trying to create a report which lists all the projects an officer
looks after. I have based the report on a query. I want to list the projects
under the relevant officer's name but, at the moment, I only seem to be able
to do it by employeeID which (as an autonumber), of course, is meaningless.

Now! I succeeded in displaying the officer name in the contacts table by
using the following statement in the row source of the lookup tab: SELECT
DISTINCTROW [Officers].[EmployeeID], [LastName] & ", " & [FirstName] AS Name
FROM Officers ORDER BY [Officers].[LastName], [Officers].[FirstName];

Is there any way I can use the same statement to display the officer's name
in the report? If not how can I ensure I do display their name rather than
the meaningless employeeID?

 

[Allen Browne]

Create a query that includes both the Officers table and the Projects table.
Use this query as the source for your report. You can choose fields from
both tables.