Need help with combo box lookup reference in a report

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay

You can use a query to feed your report where 'Projects' table is joined to
'Development Phases' on ID and include Code in query output.

"Lindsay" wrote:

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay

Hi Karl,

I'm sorry, but I can't figure out how to get the query to work. I can only
get it to match a Development Phase to every single "Code" (letter) . Which
is not correct. There is also a sub-field in the Projects table called
DevelopmentPhase.Value, but when I try to pull that into the query, I get an
error message about ambiguous outer joins.

Thanks,
Lindsay

"KARL DEWEY" wrote:

You can use a query to feed your report where 'Projects' table is joined to
'Development Phases' on ID and include Code in query output.

"Lindsay" wrote:

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay

Create a query in design view with both 'Projects' and 'Development Phases'
tables.
Pull down all fields from both tables to the FIELD row of the grid.
Click on VIEW _ SQL View, highlight all, copy, and paste in a post.

"Lindsay" wrote:

Hi Karl,

I'm sorry, but I can't figure out how to get the query to work. I can only
get it to match a Development Phase to every single "Code" (letter) . Which
is not correct. There is also a sub-field in the Projects table called
DevelopmentPhase.Value, but when I try to pull that into the query, I get an
error message about ambiguous outer joins.

Thanks,
Lindsay

"KARL DEWEY" wrote:

You can use a query to feed your report where 'Projects' table is joined to
'Development Phases' on ID and include Code in query output.

"Lindsay" wrote:

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay

SELECT [Development Phases].ID, [Development Phases].Code, [Development
Phases].DevelopmentPhase, Projects.DevelopmentPhase,
Projects.DevelopmentPhase.Value, Projects.ID, Projects.[Sort order],
Projects.ProjectName, Projects.City, Projects.State
FROM [Development Phases], Projects;


"KARL DEWEY" wrote:

Create a query in design view with both 'Projects' and 'Development Phases'
tables.
Pull down all fields from both tables to the FIELD row of the grid.
Click on VIEW _ SQL View, highlight all, copy, and paste in a post.

"Lindsay" wrote:

Hi Karl,

I'm sorry, but I can't figure out how to get the query to work. I can only
get it to match a Development Phase to every single "Code" (letter) . Which
is not correct. There is also a sub-field in the Projects table called
DevelopmentPhase.Value, but when I try to pull that into the query, I get an
error message about ambiguous outer joins.

Thanks,
Lindsay

"KARL DEWEY" wrote:

You can use a query to feed your report where 'Projects' table is joined to
'Development Phases' on ID and include Code in query output.

"Lindsay" wrote:

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay

I missed something in your eariler post --
"In the Projects table, only the DevelopmentPhase field is shown in a
combo box. Multiple values are allowed/selected."
and
"There is also a sub-field in the Projects table called
DevelopmentPhase.Value"

It appears that your table has a LookUp field. All that I have read says
that is a terrible thing to use.

I can not help you with your problem. You need to start a new thread for
someone else to help.
"Lindsay" wrote:

SELECT [Development Phases].ID, [Development Phases].Code, [Development
Phases].DevelopmentPhase, Projects.DevelopmentPhase,
Projects.DevelopmentPhase.Value, Projects.ID, Projects.[Sort order],
Projects.ProjectName, Projects.City, Projects.State
FROM [Development Phases], Projects;


"KARL DEWEY" wrote:

Create a query in design view with both 'Projects' and 'Development Phases'
tables.
Pull down all fields from both tables to the FIELD row of the grid.
Click on VIEW _ SQL View, highlight all, copy, and paste in a post.

"Lindsay" wrote:

Hi Karl,

I'm sorry, but I can't figure out how to get the query to work. I can only
get it to match a Development Phase to every single "Code" (letter) . Which
is not correct. There is also a sub-field in the Projects table called
DevelopmentPhase.Value, but when I try to pull that into the query, I get an
error message about ambiguous outer joins.

Thanks,
Lindsay

"KARL DEWEY" wrote:

You can use a query to feed your report where 'Projects' table is joined to
'Development Phases' on ID and include Code in query output.

"Lindsay" wrote:

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay

Hi Karl,

Thanks for your help anyway.

Lindsay

"KARL DEWEY" wrote:

I missed something in your eariler post --
"In the Projects table, only the DevelopmentPhase field is shown in a
combo box. Multiple values are allowed/selected."
and
"There is also a sub-field in the Projects table called
DevelopmentPhase.Value"

It appears that your table has a LookUp field. All that I have read says
that is a terrible thing to use.

I can not help you with your problem. You need to start a new thread for
someone else to help.
"Lindsay" wrote:

SELECT [Development Phases].ID, [Development Phases].Code, [Development
Phases].DevelopmentPhase, Projects.DevelopmentPhase,
Projects.DevelopmentPhase.Value, Projects.ID, Projects.[Sort order],
Projects.ProjectName, Projects.City, Projects.State
FROM [Development Phases], Projects;


"KARL DEWEY" wrote:

Create a query in design view with both 'Projects' and 'Development Phases'
tables.
Pull down all fields from both tables to the FIELD row of the grid.
Click on VIEW _ SQL View, highlight all, copy, and paste in a post.

"Lindsay" wrote:

Hi Karl,

I'm sorry, but I can't figure out how to get the query to work. I can only
get it to match a Development Phase to every single "Code" (letter) . Which
is not correct. There is also a sub-field in the Projects table called
DevelopmentPhase.Value, but when I try to pull that into the query, I get an
error message about ambiguous outer joins.

Thanks,
Lindsay

"KARL DEWEY" wrote:

You can use a query to feed your report where 'Projects' table is joined to
'Development Phases' on ID and include Code in query output.

"Lindsay" wrote:

I have a form called "Project Snapshot" that is based on a table called
"Projects". The table has a field called [DevelopmentPhases] that is a
lookup reference to another table "Development Phases" with 3 fields (1 - ID
(primary key), 2 - Code (a letter), 3 - DevelopmentPhase (long text that
corresponds to that letter). In the Projects table, only the
DevelopmentPhase field is shown in a combo box. Multiple values are
allowed/selected.

I want to run a report that shows the corresponding letters (field "Code"
from the "Development Phases" table) but not the text (field
"DevelopmentPhase" from the "Development Phases" table). But I'm completely
stumped and can't figure out how. All I can get is a list of the text.

Two solutions I can think of, but can't actually do a
1. Adding the letter to the front of the text and using code to display only
the first character of that field for each text value in the series.
2. Lookup the corresponding letter from the "Code" field in the "Development
Phases" table and replace the each text value in the series with the
corresponding letter.

Any help would be greatly appreciated!

Thanks,
Lindsay