Queries in Display Fields

[Guest]

Hello all,

Got a (I believe) fairly simple problem that I can't figure out. I'm using
Access 97. Let me describe.

I have a table that has multiple fields for employee numbers. It's like a
call-center ticket system. One employee opens it, another might follow up,
another might close. So there are a total of 5 distinct employee numbers
stored in the table. I also have an employee table with just numbers and
names.

And here's where I run into problems:
I'm tying to create a form to display the some ticket info and the employee
number and name associated with the actions taken on the ticket. And I can't
get the name of the employee to display properly. I constantly have "#Name?"
displayed.

The RecordSource for the form is Ticket. And I have a relationship between
each of the emp numbers in Ticket and emp number in Emp table.

Inside the field I'm trying to use to display each employee name, I tried
writing the following:
SELECT [Emp]![EmpName] FROM [Emp] WHERE [Emp]![EmpNum] = [Ticket]![ENum1]

I've tried adding ( ) and = but it hasn't had any affect.

I've also tried creating a query, but it said there was a conflict between
and table used in the form and the table used in the query.

I'm convinced this is doable, and I'd appreciate any help I can get. I
think I'm missing something really obvious...

Thanks,
Jay

 

[tina]

Inside the field I'm trying to use to display each employee name, I tried

writing the following:
SELECT [Emp]![EmpName] FROM [Emp] WHERE [Emp]![EmpNum] =
[Ticket]![ENum1]

you can't set a SQL statement as the ControlSource of a form's control.

I'm convinced this is doable

yes, there are several ways to do it.

if the employee numbers are entered/displayed in the form using combo boxes,
you can add another column to the combobox RowSource with the employee's
name. increase the combobox's ColumnCount by 1, and set the additional
column's width to zero (0) in the ColumnWidths property (this hides the
column in the droplist, but leaves the values available for reference). use
an unbound textbox control to show the employee's name, setting the
ControlSource property to
=[MyComboboxName].Column(2)
note that combobox columns are zero-based, so the first column is (0), the
second column is (1), etc.

if you're using textbox controls to enter/display employee numbers, you use
the DLookup() to display the employee names, again in unbound textbox
controls with ControlSource set to
=DLookup("[EmployeeName]", "EmployeeTable", "[EmployeeNumber] = " &
[NameOfEmployeeFieldInTableTicket])

you can also probably base the form on an updateable query using the Ticket
table and the Employee table, using the following sytax, as

SELECT tblData.ID, tblData.OpenedBy, E1.EmpName, tblData.UpdatedBy,
E2.EmpName, tblData.ClosedBy, E3.EmpName, tblData.ReviewedBy, E4.EmpName,
tblData.ApprovedBy, E5.EmpName
FROM ((((tblData LEFT JOIN tblEmployees As E1 ON tblData.OpenedBy =
E1.EmpID) LEFT JOIN tblEmployees AS E2 ON tblData.UpdatedBy = E2.EmpID) LEFT
JOIN tblEmployees AS E3 ON tblData.ClosedBy = E3.EmpID) LEFT JOIN
tblEmployees AS E4 ON tblData.ReviewedBy = E4.EmpID) LEFT JOIN tblEmployees
AS E5 ON tblData.ApprovedBy = E5.EmpID;

in any of the above solutions, substitute the correct table, field, and
control names, of course.

hth

Hello all,

Got a (I believe) fairly simple problem that I can't figure out. I'm using
Access 97. Let me describe.

I have a table that has multiple fields for employee numbers. It's like a
call-center ticket system. One employee opens it, another might follow up,
another might close. So there are a total of 5 distinct employee numbers
stored in the table. I also have an employee table with just numbers and
names.

And here's where I run into problems:
I'm tying to create a form to display the some ticket info and the employee
number and name associated with the actions taken on the ticket. And I can't
get the name of the employee to display properly. I constantly have "#Name?"
displayed.

The RecordSource for the form is Ticket. And I have a relationship between
each of the emp numbers in Ticket and emp number in Emp table.

Inside the field I'm trying to use to display each employee name, I tried
writing the following:
SELECT [Emp]![EmpName] FROM [Emp] WHERE [Emp]![EmpNum] = [Ticket]![ENum1]

I've tried adding ( ) and = but it hasn't had any affect.

I've also tried creating a query, but it said there was a conflict between
and table used in the form and the table used in the query.

I'm convinced this is doable, and I'd appreciate any help I can get. I
think I'm missing something really obvious...

Thanks,
Jay

 

[John Vinson]

Hello all,

Got a (I believe) fairly simple problem that I can't figure out. I'm using
Access 97. Let me describe.

I have a table that has multiple fields for employee numbers. It's like a
call-center ticket system. One employee opens it, another might follow up,
another might close. So there are a total of 5 distinct employee numbers
stored in the table. I also have an employee table with just numbers and
names.

Well... there's a design flaw right there. You're "committing
spreadsheet upon a database". If you have a many (call ticket) to many
(employee) relationship, you need to model it as a many to many
relationship, *with another table*. This table should have the Ticket,
the EmployeeID, the action (open, close, followup), maybe a date/time
field defaulting to Now(), and a comments field.

You can then enter any desired number of actions, each with its own
employeeID and some perhaps useful information. Your current
"wide-flat" design is the cause of your query problems and will remain
very difficult to use.

John W. Vinson[MVP]

 

[Guest]

Jay,

In addition to Tina and John's accurate suggestions, the combo box would be
much more useful by looking up by name wouldn't it, and *storing* the EmpID?
To this in a combo box, include both fields in the RowSource, set the
BoundColumn to 1, and the ColumnWidths property to 0";x", where x is large
enough to display the longest name. Better still use the wizard, and choose
the Hide Key Field (Recommended) option, and Access will set them all for you.

Sprinks