Return Desired Value in a Text Box

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Let me see if I can explain the structure before the question ...

The table named TblLeads uses two fields, LeadsBy & AssignedTo, which are
List Box lookups from the table named TblEmployees.
TblEmployees is simple in that it contains a primary key (set to autonumber)
and the name of the employee which we input.

EmployeeID Employee Name
1 Jack Daniels
2 Samual Adams
3 Bud Light

I used a select query named QryLeads to pull the data from TblLeads. My
report, RptLeads is pulling from QryLeads.

My report has two Text Box fields; one in the header, LeadsBy, and one in
the detail, AssignedTo. The value that is returned in the report is 1,2,3,
etc. that is the EmployeeID and not what I want to report on an that's the
EmployeeName. I've tried switching these fields in the report to list or
combo boxes, modifying the column counts and widths, but so far can't get the
EmployeeName data to pass thru.

Thanks in advance with any assistance.
 
I expect you might have used lookup fields in tables. Most of us "seasoned"
programmers don't use these.

Your report's record source query should use TblLeads and two copies of
TblEmployees. Join EmployeeID from one to the LeadsBy field and the other
table is joined to the AssignedTo field. Add each copy of the EmployeeName
field to the query grid and rename them "LeadEmployeeName" and
"AssignedEmployeeName".
 
1. Create a query using tblLeads and tblEmployees.

2. Depending on your relationships etc, Access may join tblLeads.LeadsBy to
tblEmployees.EmployeeID and also tblLeads.AssignedTo to
tblEmployees.EmployeeID. If you see both lines, delete the 2nd. If you see
neither, create the first. You now have only one line between the 2 tables
in the upper pane of query design.

3. Add the tblEmployees table to the query again.
Access will alias it as tblEmployee_1.
This time you want only the other join (i.e. tblLeads.AssignedTo to
tblEmployees.EmployeeID.)

4. You can now drag tblEmployee.[Employee Name] into the grid, and also
tblEmployee_1.[Employee Name], so you get both name output.

5. (Optional) Type an alias and a colon in front of one of the Employee Name
fields, e.g.:
AssignedToName: tblEmployee.[Employee Name]

Your report can now show both names.

Other suggestions:
a) If either field could be Null, use outer joins. More info in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

b) Generally it's better to have separate fields for Surname and FirstName,
rather than having both in an [Employee Name] field.
 
Dear Dale,

I am a MS Access developer. After many tries I had overcome this requirement for my client. If you are interested for solution you can contact me on the following E-mail address.

Farrukh Rehman
(e-mail address removed)



Allen Browne wrote:

1. Create a query using tblLeads and tblEmployees.2.
11-Nov-09

1. Create a query using tblLeads and tblEmployees

2. Depending on your relationships etc, Access may join tblLeads.LeadsBy t
tblEmployees.EmployeeID and also tblLeads.AssignedTo t
tblEmployees.EmployeeID. If you see both lines, delete the 2nd. If you se
neither, create the first. You now have only one line between the 2 table
in the upper pane of query design

3. Add the tblEmployees table to the query again
Access will alias it as tblEmployee_1
This time you want only the other join (i.e. tblLeads.AssignedTo t
tblEmployees.EmployeeID.

4. You can now drag tblEmployee.[Employee Name] into the grid, and als
tblEmployee_1.[Employee Name], so you get both name output

5. (Optional) Type an alias and a colon in front of one of the Employee Nam
fields, e.g.
AssignedToName: tblEmployee.[Employee Name

Your report can now show both names

Other suggestions
a) If either field could be Null, use outer joins. More info in
The Query Lost My Records! (Nulls
at
http://allenbrowne.com/casu-02.htm

b) Generally it is better to have separate fields for Surname and FirstName
rather than having both in an [Employee Name] field

-
Allen Browne - Microsoft MVP. Perth, Western Australi
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
License ASP.NET Applications - Desaware vs Microsoft SLP
http://www.eggheadcafe.com/tutorial...ef-45699c35efd9/license-aspnet-applicati.aspx
 
Farrukh Rehman,

GO AWAY! These news groups are for free support, not for you or anyone else
to advertise for work for hire. I hope this is just a simple mistake on your
part.

Take a hint from someone like Allen Browne who regularly posts great answers
and has a wonderfully maintained web site of FREE resources.
 
Back
Top