Missing Fields from report

[TimJames]

I have created a Request table with references to another table (Requesting
Employees), then created a form based upon the Request table. The fields in
the requesting Employee table are Employee, Extension, Department. The form
will self populate the Extension and Department fields after selecting the
Employee name. This information is saved and reviewable in the form, but not
in my Request table nor in any reports I generate listing the requests. Any
idea how I get the Extension and Deparments to list in my reports? (And to
save in my table would be nice also, but I am mostly concerned with the
information appearing in my reports.) I am using Office 2007.

 

[Jeff Boyce]

Tim

It sounds like you may have employed the "lookup" data type ("... with
references to another table..."). This causes considerable confusion, as it
stores one thing (a primary key value), but displays something else (the
"looked-up" value).

Then, when you try to run queries (and reports) against the main table, you
don't see the looked-up value, but the stored value.

Does this match your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I have created a Request table with references to another table (Requesting
Employees), then created a form based upon the Request table. The fields in
the requesting Employee table are Employee, Extension, Department. The form
will self populate the Extension and Department fields after selecting the
Employee name. This information is saved and reviewable in the form, but not
in my Request table nor in any reports I generate listing the requests. Any
idea how I get the Extension and Deparments to list in my reports? (And to
save in my table would be nice also, but I am mostly concerned with the
information appearing in my reports.) I am using Office 2007.

The report's record source query need to join the two
tables. Create a new query, select both tables, make sure
there's a connecting line between the linking fields and
then drag the needed fields from both tables to the query's
field list.

Except for the linking field(s), you DO NOT want to include
fields from one table in another table.

 

[TimJames]

Jeff,
This matches my situation to a "T". I presume I have recreated a familiar
situation?

Marsh,
I tried to run a Query as you suggested and I alwyas get a Type Mismatch
error. I have tried changing my relationships many ways and have included /
omitted various fields from the tables. I still get the error even if there
is only one field, but both tables.

Thanks,
~Tim


--
Thank you,
~Tim

Tim

It sounds like you may have employed the "lookup" data type ("... with
references to another table..."). This causes considerable confusion, as it
stores one thing (a primary key value), but displays something else (the
"looked-up" value).

Then, when you try to run queries (and reports) against the main table, you
don't see the looked-up value, but the stored value.

Does this match your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

Jeff,
This matches my situation to a "T". I presume I have recreated a familiar
situation?

Marsh,
I tried to run a Query as you suggested and I alwyas get a Type Mismatch
error. I have tried changing my relationships many ways and have included /
omitted various fields from the tables. I still get the error even if there
is only one field, but both tables.

You are probably confused by what you see for the lookup
field. If you change that field back to a text box, you
should be able to see what's really going on and make more
sense of things.

FYI, see the second commandment (and its link) for a
discussion of the hole you've dug for yourself.

 

[TimJames]

Ok, I have looked up the Second Commandment and also did a search in this
group for references to the second commandment with very informative results.
My question now is - when I set up a form to look up this information from
two seperate tables, it requires a related field in the two tables. Wouldn't
this be a look up field that violates the Prime Directive? Oops - the Second
Commandment? Or should I set up a form with a subform? I want users to fill
out a form with a drop down of employees making requests. Once the employee
is selected their phone extension and department are automatically filled in
also.

 

[Marshall Barton]

Ok, I have looked up the Second Commandment and also did a search in this
group for references to the second commandment with very informative results.
My question now is - when I set up a form to look up this information from
two seperate tables, it requires a related field in the two tables. Wouldn't
this be a look up field that violates the Prime Directive? Oops - the Second
Commandment? Or should I set up a form with a subform? I want users to fill
out a form with a drop down of employees making requests. Once the employee
is selected their phone extension and department are automatically filled in
also.

Prime directive? By reading up on this issue, you have
already had your culture uplifted by advanced technology.
There is no going back to your primitive condition ;-)

Don't confuse the evil lookup ***field*** in a table with
the useful lookup table. When you gain a deep understanding
of the relationship between tables, then you will see how a
lookup field is hiding all the details from you but doing
the same thing in an information obscurring way.

Forms commonly use a combo box with its row source set to a
table/query, which then acts as a lookup. Some tables are
only used as a lookup table (e.g. a table of states).

The relationship between two tables is defined by the one
side table's primary key field(s) and the many side table's
foreign key field(s). These should be specified in the
Relationships window, which will make sure that the foreign
key field(s) are indexed so query joins can be optimized (a
potentially huge performance gain) and will also prevent
relationshp violations with no further effort from you.

Now to your specific question about displaying phone, etc
when a employee is selected in the form's combo box. Since
the combo box's bound column is set to the corresponding
employee ID field, there is no good reason to store any
other employee data in the form's base table. However, it
is perfectly normal to display that information in unbound
controls. This is easily done by setting the combo box's
RowSource to a query that includes the fields you want to
display and then using expressions like
=thecombo.Column(x), where x is the zero base number of the
field to display. Don't forget to set the combo box's
ColumnCount to the number of fields in the query and the
ColumnWidths property to something like 0; ;0;0 so it only
displays the name in the drop list.