Need help with combo box lookup reference in a report

[Lindsay]

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 are:
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

 

[KARL DEWEY]

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]

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

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.

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 are:
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

 

[KARL DEWEY]

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.

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

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.

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 are:
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

 

[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;

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.

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

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.

:

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 are:
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

 

[KARL DEWEY]

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.

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;

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.

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

:

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.

:

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 are:
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

 

[Lindsay]

Hi Karl,

Thanks for your help anyway.

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.

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;

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.

:

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

:

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.

:

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 are:
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