Append foregin key

[johannaellamay]

Hi. I have the following tables w/ the following fields:

1. Employee Category

EmployeeCategoryID | EmployeeCategory

2. Employee

EmployeeID | Name | EmployeeCategoryID

3. Employee Category History (generated from Append Query to record history of employee categories)

EmployeeID | Name | EmployeeCategoryID

Then I have a report which is generated from Employee Category History table.


Problem:

In my report, I want the Employee Category field to show the category name, and not the category ID. How can I do that?

 

[pbaldy]

Base the report on a query that joins the table, enabling you to return the name.

 

[johannaellamay]

Base the report on a query that joins the table, enabling you to return the name.

Hmm. I see. However, is it not possible to not create a new query for that? Is there another way? I don't want to create a lot of objects considering that I only have three fields to append.

 

[Galaxiom]

One option is to edit the report's original query.

Another is to use a combobox as the control to display the CategoryName.
(Bound combos in reports don't show the dropdown.)

In Design View right click the textbox and Change To > Combobox

Then set the properties something like this:

RowSourceType: Table/Query
RowSource: SELECT CategoryID, CategoryName FROM Categories
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0cm;5cm

This will bind the first column but display the second column.

 

[johannaellamay]

One option is to edit the report's original query.

Another is to use a combobox as the control to display the CategoryName.
(Bound combos in reports don't show the dropdown.)

In Design View right click the textbox and Change To > Combobox

Then set the properties something like this:

RowSourceType: Table/Query
RowSource: SELECT CategoryID, CategoryName FROM Categories
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0cm;5cm

This will bind the first column but display the second column.

Hi. I tried it. It didn't work.

 

[Galaxiom]

Hi. I tried it. It didn't work.

Tried what?

Moreover, "didn't work" isn't particularly helpful feedback.

What actually happened?

 

[johannaellamay]

Tried what?

Moreover, "didn't work" isn't particularly helpful feedback.

What actually happened?

Sorry. Should have explained better. Hmm. So I changed the "echEmployeeCategory" control into a combo box like you said. I also copied the rowsource but changed the names of course. I basically did everything from your instructions.

The result: No changes. No error message whatsoever.

 

[pbaldy]

You would probably need to set the column widths property as well, to hide the ID field.