Access 2007 on WinXP

I have a report designed that is printing the department number, I would 
like to print teh department name instead. The depatment number is included 
in teh query the report is based upon. The report requires data from 4 
differnt tables and when I try to add a fift table into the report 
relationship the output data duplicates unreliably.

I work primarily with the click and drag method of report build in design 
view as opposed to coding everything. What I want is to print the DeptName 
filed from the TblDept table where the tblDeptID in TblDept is equal to the 
EmpDept field in the query. Below is my best atteplt at coding this in source 
line of the report field

[Select DeptName from tblDept where DeptID=tblParticipantTrx].EmpDept

This resulted in a dialog box requesting data for Select DeptName from 
tblDept where DeptID=tblParticipantTrx. I did not include teh square brackets 
when en tering the statement above, Access placed those.

#1 is what I need to do possible, if so waht are my options?

Thanks for the effort

Unless the tblDept has duplicate departments adding it to the existing query 
with a join to EmpDept should not cause additional records.

You can use a subquery or the DLookup function.

(SELECT First(DeptName) FROM tblDept WHERE tblDept.DeptID = 
[tblParticpantTrx].EmpDept) as DeptName


OR using DLookup function:
(DeptID is a number field)
      DLookup("DeptName","tblDept","DeptID=" & [EmpDept])
(DeptID is a text field)
      DLookup("DeptName","tblDept","DeptID=""" & [EmpDept] & """") as DeptName

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

JR Hester wrote:
> Access 2007 on WinXP
> 
> I have a report designed that is printing the department number, I would 
> like to print teh department name instead. The depatment number is included 
> in teh query the report is based upon. The report requires data from 4 
> differnt tables and when I try to add a fift table into the report 
> relationship the output data duplicates unreliably.
> 
> I work primarily with the click and drag method of report build in design 
> view as opposed to coding everything. What I want is to print the DeptName 
> filed from the TblDept table where the tblDeptID in TblDept is equal to the 
> EmpDept field in the query. Below is my best atteplt at coding this in source 
> line of the report field
> 
> [Select DeptName from tblDept where DeptID=tblParticipantTrx].EmpDept
> 
> This resulted in a dialog box requesting data for Select DeptName from 
> tblDept where DeptID=tblParticipantTrx. I did not include teh square brackets 
> when en tering the statement above, Access placed those.
> 
> #1 is what I need to do possible, if so waht are my options?
> 
> Thanks for the effort

The fifth table should have a primary key of the department number so you can 
join it to the existing Department number in your query. This fifth table 
should have the department name in it so it can be included in the report.

-- 
Duane Hookom
Microsoft Access MVP


"JR Hester" wrote:

> Access 2007 on WinXP
> 
> I have a report designed that is printing the department number, I would 
> like to print teh department name instead. The depatment number is included 
> in teh query the report is based upon. The report requires data from 4 
> differnt tables and when I try to add a fift table into the report 
> relationship the output data duplicates unreliably.
> 
> I work primarily with the click and drag method of report build in design 
> view as opposed to coding everything. What I want is to print the DeptName 
> filed from the TblDept table where the tblDeptID in TblDept is equal to the 
> EmpDept field in the query. Below is my best atteplt at coding this in source 
> line of the report field
> 
> [Select DeptName from tblDept where DeptID=tblParticipantTrx].EmpDept
> 
> This resulted in a dialog box requesting data for Select DeptName from 
> tblDept where DeptID=tblParticipantTrx. I did not include teh square brackets 
> when en tering the statement above, Access placed those.
> 
> #1 is what I need to do possible, if so waht are my options?
> 
> Thanks for the effort