Display First Name/Last Name

[scouser]

Guys, I have a report based on a query rprtServiceSchedule. I want to output the LastName and FirstName of the ServiceEmployee (the person assigned to satisfy the order) to the report.
I currently print to the report the FirstName and LastName of the EmployeeResponsible for inputting the Order on which the service schedule is based. Currenly the report only prints the AutoNumber of the ServiceEmployee Name? Query has many fields, the name one's as follows:

' This displays the name of the Employee Responsible for
' inputting the order.
Name:[FirstName]&" "&[LastName]

See attached Jpeg to view query.

Basically I want to display the FirstName & LastName of the ServiceEmployee?

The ServiceEmployee(tblOrders) is a look-up field based on tblEmployees.
Am I making any sense here?

 

[suepowell]

Hi

I may have missed something, but I think you need to have the employees table in your query, with the employeeid field linked to the service employee field on the orders table. The name field you have in the query at the moment seems to refer to the name of the customer contact.

If you don't include the table in the query, it won't be available in the report, so you can't get the names to print out.

If I have completely misunderstood this sorry, I will try again, or if you can post a sample database, I will try to help again

Sue

 

[scouser]

Query Error

Sue many thanks for taking the time to reply. You understand me well! I originally included the ‘tblEmployees’ in my query but get an error (see attached Jpeg). Without the addition of ‘ServiceEmployee’ the Customer Contact First and Last names display OK. I am useless when it comes to code (any logic needs applying I am NOT your man!!). The Jpeg explains my dilemma better than I could, hope it makes things a little clearer.
Phil.

 

[Rich]

Change the join between tblOrders and tblEmployees.

 

[scouser]

Change The Join

Hi Rich. Thanks for the advice. I tried the 2 other join options and got the same error for both so have attached another Jpeg. I will give a quick explanation of how my tables are set up.

I have a customer contact table from which I get LastName & FirstName for forms orders, and enquiries. I also get LastName & FirstName for the current Employee logged in for the same forms.

I added a field to the tblOrders so I could assign a member of staff to satisfy a order request 'Service Employee'. Service employee field is 'lookup' and takes its values from tblEmployees.

Not sure any of that helps!!!
Thanks for you time.

 

[Rich]

You're storing names in more than one table, why do you store Company name in tblOrders?

 

[scouser]

Company Name

Rich my DB started off a mess and got better!!!! Still not perfect..........It is part of a dissertation and its now too late to make lots of changes. If I change that now it will snowball throughout my DB.................

 

[scouser]

Query

Rich I have attached a Jpeg of the query after I run it. It displays the First Name of the ServiceEmployee. If you select the combo you can view the full name. In the report it only displays EmployeeID (Auto Number)!!

In my order form I have the following as row source for the field 'ServiceEmployee'

' Thanks to Dan Cat for this
SELECT [tblEmployees].[EmployeeID], [FirstName] & " " & [LastName] AS N FROM tblEmployees;

How hard can it be!!!!!

 

[suepowell]

Hi

Going back to the jpg with the error message saying first name could refer to more than one table, you need to change the name: field to

name:[tblemployees]![firstname]& " " & [tblemployees]![lastname]

this is because you have firstname and lastname fields in two tables, so you need to specify which table you are refering to. This should then give you what you want

Sue

 

[scouser]

If Only!!

Sue thanks for taking time to respond. This is turning into a nightmare!! I need to show the name else I will not know who is to satisfy the order!! I tried your suggestion. Look at the Jpeg. This will show how everything is set up.
Cheers,
Phil.

 

[scouser]

Nearly There

Nearly there now. Right the latest query displays the ServiceEmployee but now the CustomerContact field returns blanks!!!!!!
Can I have both please...........................

 

[a.sinatra]

Name: [tblCustomerContacts]![FirstName] & " " & [tblCustomerContacts]![LastName]

I belive thats what the sql code should be for the second column, as long as you have included, tblCustomerContacts in the from for the sql statement.
________
Free Sex

 

[scouser]

Success

Success at long last!!
Thanks to all that replied.