What the Hell is going on here?

[Harbin]

Howdy Y'all:

I've been beating on this problem for 3 days, and I can't see where the problem is. I am trying to copy the sql from the row
source from the
"Ordres" table, and the "Employee" column, and use it on my "TblInvoice" table, and the "Technician" column, but as you can see
below,
instead of getting back the last name, then the first name, I just get back the "pNk2EmployeeID", which is "1" on my "TblInvoice"
table. What
is the problem here?


Orders Order ID Customer Employee Order Date Required Date Shipped Date Ship Via Freight Ship Name Ship Address Ship City Ship
Region Ship Postal Code Ship Country
10248 Vins et alcools Chevalier Buchanan, Steven 04-Aug-94 01-Sep-94 16-Aug-94 Federal Shipping $32.38 Vins et alcools
Chevalier 59 rue de l'Abbaye Reims
51100 France
10249 Toms Spezialitäten Suyama, Michael 05-Aug-94 16-Sep-94 10-Aug-94 Speedy Express $11.61 Toms Spezialitäten Luisenstr. 48
Münster
44087 Germany
10250 Hanari Carnes Peacock, Margaret 08-Aug-94 05-Sep-94 12-Aug-94 United Package $65.83 Hanari Carnes Rua do Paço, 67 Rio de
Janeiro RJ 05454-876 Brazil
10251 Victuailles en stock Leverling, Janet 08-Aug-94 05-Sep-94 15-Aug-94 Speedy Express $41.34 Victuailles en stock 2, rue du
Commerce Lyon
69004 France



RowSource:

SELECT DISTINCTROW Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Name FROM Employees ORDER BY Employees.LastName,
Employees.FirstName;

TblInvoice Invoice ID Vehicle ID Service Writer Technician Date Description Date Promised Estimated Cost Actual Cost Parts
Replaced Prev Service Date Next Service Date Miles Since Last Srvc Date Completed Received By Labor Hours Save Parts Invoice Status
2

1 1/17/2006 mem 12/6/1945 $100.00 $100.00 spark plugs 10/20/2005 10/10/2007 200000 1/2/2003 Joe Smith 8 Yes Comeback



Row Source:

SELECT DISTINCTROW tbl2Employees.pNk2EmployeeID, [strLastName] & ", " & [strFirstName] AS Name
FROM tbl2Employees
ORDER BY tbl2Employees.strLastname, tbl2Employees.strFirstname;

In the above sql, "tbl2Employees" is the table, "pNk2EmployeeID" is the employee autonumber ID, and the [strLastName] and
[strFirstName]
are the field names in the design view.

Any idea's why this is not working for me, send me a email at (e-mail address removed), (remove no spam), or just post in the
groupe.
Thanks Harbin

 

[Harbin]

In the message that I sent earlier, the tables did not come out, so I hope it is not too confusing.

Howdy Y'all:

I've been beating on this problem for 3 days, and I can't see where the problem is. I am trying to copy the sql from the row
source from the
"Ordres" table, and the "Employee" column, and use it on my "TblInvoice" table, and the "Technician" column, but as you can see
below,
instead of getting back the last name, then the first name, I just get back the "pNk2EmployeeID", which is "1" on my "TblInvoice"
table. What
is the problem here?

"deleted table"

RowSource:

SELECT DISTINCTROW Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Name FROM Employees ORDER BY Employees.LastName,
Employees.FirstName;

The sql above will return the employees last name, then first name in one column.

Row Source:

SELECT DISTINCTROW tbl2Employees.pNk2EmployeeID, [strLastName] & ", " & [strFirstName] AS Name
FROM tbl2Employees
ORDER BY tbl2Employees.strLastname, tbl2Employees.strFirstname;

In the above sql, "tbl2Employees" is the table, "pNk2EmployeeID" is the employee autonumber ID, and the [strLastName] and
[strFirstName]
are the field names in the design view.

The sql above will return the employee autonumber ID of "1" only, will not bring back the lastname, firstname to the datasheet.

 

[John Vinson]

SELECT DISTINCTROW tbl2Employees.pNk2EmployeeID, [strLastName] & ", " & [strFirstName] AS Name
FROM tbl2Employees
ORDER BY tbl2Employees.strLastname, tbl2Employees.strFirstname;

In the above sql, "tbl2Employees" is the table, "pNk2EmployeeID" is the employee autonumber ID, and the [strLastName] and
[strFirstName]
are the field names in the design view.

The sql above will return the employee autonumber ID of "1" only, will not bring back the lastname, firstname to the datasheet.

I would very strongly guess that strLastName is a Lookup field in
tbl2Employees... right?

Lookup fields are very deceptive. The Lookup misfeature CONCEALS the
actual contents of the table - a numeric ID number - behind the
looked-up text. This does *not* carry over to queries or much of
anything else you do with the table.

You will probably need to join tbl2Employees to the real Employees
table to retrieve the names.

John W. Vinson[MVP]

 

[Harbin]

SELECT DISTINCTROW tbl2Employees.pNk2EmployeeID, [strLastName] & ", " & [strFirstName] AS Name
FROM tbl2Employees
ORDER BY tbl2Employees.strLastname, tbl2Employees.strFirstname;

In the above sql, "tbl2Employees" is the table, "pNk2EmployeeID" is the employee autonumber ID, and the [strLastName] and
[strFirstName]
are the field names in the design view.

The sql above will return the employee autonumber ID of "1" only, will not bring back the lastname, firstname to the datasheet.

I would very strongly guess that strLastName is a Lookup field in
tbl2Employees... right?

This is correct.

Lookup fields are very deceptive. The Lookup misfeature CONCEALS the
actual contents of the table - a numeric ID number - behind the
looked-up text. This does *not* carry over to queries or much of
anything else you do with the table.

You will probably need to join tbl2Employees to the real Employees
table to retrieve the names.

John W. Vinson[MVP]

The "tbl2Employees", which is the table I'm trying to get the employees name from is linked to the table "tblInvoce", which
I'm trying to get the last name, and first name from "tbl2employees" to show up in the field named "Technician" on the data view,
on the "tblInvoice" table. "pNk2EmployeeID" field from the "tbl2Employees" is linked to the "tblInvoice" table to the field
"fNk2Employees"
in the design view, who's caption is "Technician" in the "data sheet" field. It will show the autonumber ID of the employee, but
will not
show the last, and first name, as it should with the sql code above. The code above was taken from the northwind database, which
it does work, and copyed to my table "tblInvoice", field "fNk2Employees", but does not work there. What's up with that!

Thanks Harbin