Solved

Make Report field show as name instead of ID

Posted on 2010-09-10
6
1,193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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