Getting another employee's data from the Employee table, and text innumber fields

[Lostguy]

Hello!

1) In the Northwind database, they use this query ifrom the Employees
Table in the Employees Form as the RecordSource for a combobox which
has a dropdown to allow you to select who the employee "Reports To":

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

The result is put in back into the Employees Table in the "ReportsTo"
field as "Fuller, Andrew", etc.

I have an Evaluation Report which is supposed to look this:

<Employee> <Reports to>
Smith, Bill Fuller, Andrew
Mr., Technician Dr, Manager
Supply Division Headquarters Division

So, I can make the query based on the Employee Table and the Divisions
Table for everything about Bill Smith ([LName]&", "&[FNName]&Chr10&Chr
(13)&[Title]&", "&[Position]&Chr10&Chr(13)...etc

But for Andrew Fuller, I can use whats in [ReportsTo] in the Employee
Table, but how do I set up the rest so that I am getting the data
(Dr., Manager, etc.) for Fuller vice Smith? Do I put that in the
query, the report, or the original form combo box?

2) Just some schooling needed about this:

That combobox is putting "Fuller, Andrew" in the ReportTo field of the
EmployeeTable. Yet, if you go to Design View, the ReportTo field is a
Number/Long Integer datatype. So is a number gettng stored in there
somewhere that I can't see? How can "Fuller, Andrew" (text) appear in
a field formatted as a number???

Thanks for this and past help! I am learning slowly, but getting
there.

VR/

Lost

 

[Tom Wickerath]

Hi Lostguy,

I'm a bit lost on your first question, but I'll try to answer your second
question. The combo box has a Row Source of this (I removed the redundant
tablenames):

SELECT EmployeeID,
[LastName] & ", " & [FirstName] AS ReportsTo
FROM Employees
ORDER BY LastName, FirstName;

Thus, the EmployeeID field (a numeric field) is considered Column 1.
The concatenated name, aliased as "ReportsTo", is considered Column 2,
although you will not see this alias unless you have Column Heads set to Yes.
(Don't confuse the field name, which is captioned as "Reports To", with the
query alias).

The Bound column is column 1. This is the value that gets saved when one
makes a selection from the combo box, and this column is the numeric
EmployeeID field.

The Column Widths include a zero width for the first column. A combo box
that is not in the dropped down state will only display the first visible
column, which in this case is the field aliased as ReportsTo.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hello!

1) In the Northwind database, they use this query ifrom the Employees
Table in the Employees Form as the RecordSource for a combobox which
has a dropdown to allow you to select who the employee "Reports To":

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

The result is put in back into the Employees Table in the "ReportsTo"
field as "Fuller, Andrew", etc.

I have an Evaluation Report which is supposed to look this:

<Employee> <Reports to>
Smith, Bill Fuller, Andrew
Mr., Technician Dr, Manager
Supply Division Headquarters Division

So, I can make the query based on the Employee Table and the Divisions
Table for everything about Bill Smith ([LName]&", "&[FNName]&Chr10&Chr
(13)&[Title]&", "&[Position]&Chr10&Chr(13)...etc

But for Andrew Fuller, I can use whats in [ReportsTo] in the Employee
Table, but how do I set up the rest so that I am getting the data
(Dr., Manager, etc.) for Fuller vice Smith? Do I put that in the
query, the report, or the original form combo box?

2) Just some schooling needed about this:

That combobox is putting "Fuller, Andrew" in the ReportTo field of the
EmployeeTable. Yet, if you go to Design View, the ReportTo field is a
Number/Long Integer datatype. So is a number gettng stored in there
somewhere that I can't see? How can "Fuller, Andrew" (text) appear in
a field formatted as a number???

Thanks for this and past help! I am learning slowly, but getting
there.

VR/

Lost

 

[Lostguy]

Tom,

Thanks!

I think that it is comboxes that stump me. They show up in the tables,
in queries, in forms, in reports. Plus you can use them for "mini-
queries" separate from those queries you make using the query wizard.
Crazy stuff, but at least my database is coming along until I run into
the next hurdle.

Thanks again!

VR/

Lost

 

[Tom Wickerath]

Hi VR,

They show up in the tables, in queries, in forms, in reports.

If you are having combo boxes show up in tables or queries, these are evil
"Lookup fields", not to be confused with normal combo boxes on forms. See the
2nd Commandment of Access, here, for reasons why you want to avoid using
Lookup Fields:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________