Need help with combo box lookup reference in a report

  • Thread starter Thread starter Lindsay
  • Start date Start date
L

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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

Thanks for your help anyway.

Lindsay

KARL DEWEY said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top