Solved

Make Report field show as name instead of ID

Posted on 2010-09-10
6
1,183 Views
Last Modified: 2013-11-28
I have a database that is tracking employee skillsets. There is a table called Employees and a table called Assignments as well as several other tables. Assignments as a foreign key to Employees so when records are added, the EmpID is added instead of the Employee's actual name.

I then created a report which I have listing out Assignments by Employee. The problem is the report is showing the EmpID instead of the employee's first and last name. I used the wizard to create this report. (Yes I am new to Access.) I have messed with the Control Source for the field till I cannot see straight.

Suggestions for making it display the Employee Lname, Fname?
0
Comment
Question by:smsdesigns
  • 3
  • 3
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you have to use a query as the record source of the report, that will join the main table to the employee table using the employeeID and bringing the employee name instead of the employeeId as one of the fields.
0
 

Author Comment

by:smsdesigns
Comment Utility
Does the SQL statement go in the control source field? When I click its 3 dot button it opens teh Expression Builder.

I created a query that just works within the Employees table so the EmpID is displayed as Lname, Fname. But I am not quite following how to do as you suggest. Teh report is showing the correct EmpID. I just want it aliased as the Lname, Fname. Sorry I know I am being dense...

SELECT [Lname] & ", " & [Fname] AS Expr1
FROM tbl_Employees;
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what are you using as the Record Source of the report?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:smsdesigns
Comment Utility
tbl_Assignments
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
create a query, something like this

select tbl_Assignments.*, tbl_Employees.[Lname] & ", " & tbl_Employees.[Fname] As EmpName
From tbl_Assignments Inner join tbl_Employees On tbl_Employees.EmpID= tbl_Assignments.EmpID


* use this query as the Record Source of the report,
* use the created EMPName as the Control Source of the textbox to display the name of employee



0
 

Author Closing Comment

by:smsdesigns
Comment Utility
THANK YOU VERY MUCH. I think I love you! :) Have a fantastic weekend!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now