Solved

Make Report field show as name instead of ID

Posted on 2010-09-10
6
1,191 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33649203
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
ID: 33649297
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33649323
what are you using as the Record Source of the report?
0
Back Up Your Microsoft Windows Server®

Back up 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.

 

Author Comment

by:smsdesigns
ID: 33649334
tbl_Assignments
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33649398
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
ID: 33649442
THANK YOU VERY MUCH. I think I love you! :) Have a fantastic weekend!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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