Query not displaying correct column from a field

[thorpk]

I have a table called Location with 2 columns,
Abbr | Location
VIL | Village

I have a field on a form that is bound to the 2nd column of the table.

I created a query based upon the form and have the location field in
the query, when i run the query it displays the 1st column of the
table. I need it to display the 2nd column.

please adivise.
Thanks
Karl

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

 

[thorpk]

Here is the SQL view.
SELECT [Incident Header_tbl].[CSS Incident #], [Incident Header_tbl].
[Code & Type of Incident], [Incident Header_tbl].[Location of
Incident], [Incident Header_tbl].[Date of Incident], [Incident
Header_tbl].Narrative, [Incident Header_tbl].[Assigned to], [Incident
Header_tbl].[Status of Report], [Incident Header_tbl].[Reporting
Officer]
FROM [Incident Header_tbl];

Karl

 

[Jerry Whittle]

Hi,

This SQL statement doesn't select from the Location table. It does have a
Location of Incident field. How does the Incident Header_tbl link to the
Location table and what do you wish to see?

 

[thorpk]

Hi,

This SQL statement doesn't select from the Location table. It does have a
Location of Incident field. How does the Incident Header_tbl link to the
Location table and what do you wish to see?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL view.
SELECT [Incident Header_tbl].[CSS Incident #], [Incident Header_tbl].
[Code & Type of Incident], [Incident Header_tbl].[Location of
Incident], [Incident Header_tbl].[Date of Incident], [Incident
Header_tbl].Narrative, [Incident Header_tbl].[Assigned to], [Incident
Header_tbl].[Status of Report], [Incident Header_tbl].[Reporting
Officer]
FROM [Incident Header_tbl];

Karl- Hide quoted text -

- Show quoted text -

The Incident Header_tbl, Location of Incident field is linked via a
combo box with the control source as the Location table, with
properties of column count 2, and bound column 1.

I wish to see the long form for the location, located in column 2, not
the abbreviation.

Karl

 

[Jerry Whittle]

A lookup field at table! I should have know. I hate those things.

You could go to the properties and try to see the column width to something
like 0,1 . That will hide the first column.

But the proper was is not to use lookup fields. Instead do honest joins
between the tables. Something like:

SELECT [Incident Header_tbl].[CSS Incident #],
[Incident Header_tbl].[Code & Type of Incident],
Location.Location,
[Incident Header_tbl].[Date of Incident],
[Incident Header_tbl].Narrative,
[Incident Header_tbl].[Assigned to],
[Incident Header_tbl].[Status of Report],
[Incident Header_tbl].[Reporting Officer]
FROM [Incident Header_tbl], Location
WHERE Location.Abbr = [Incident Header_tbl].[Location of Incident] ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

This SQL statement doesn't select from the Location table. It does have a
Location of Incident field. How does the Incident Header_tbl link to the
Location table and what do you wish to see?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL view.
SELECT [Incident Header_tbl].[CSS Incident #], [Incident Header_tbl].
[Code & Type of Incident], [Incident Header_tbl].[Location of
Incident], [Incident Header_tbl].[Date of Incident], [Incident
Header_tbl].Narrative, [Incident Header_tbl].[Assigned to], [Incident
Header_tbl].[Status of Report], [Incident Header_tbl].[Reporting
Officer]
FROM [Incident Header_tbl];

Karl- Hide quoted text -

- Show quoted text -

The Incident Header_tbl, Location of Incident field is linked via a
combo box with the control source as the Location table, with
properties of column count 2, and bound column 1.

I wish to see the long form for the location, located in column 2, not
the abbreviation.

Karl

 

[thorpk]

A lookupfieldat table! I should have know. I hate those things.

You could go to the properties and try to see thecolumnwidth to something
like 0,1 . That will hide the firstcolumn.

But the proper was isnotto use lookup fields. Instead do honest joins
between the tables. Something like:

SELECT [Incident Header_tbl].[CSS Incident #],
 [Incident Header_tbl].[Code & Type of Incident],
 Location.Location,
 [Incident Header_tbl].[Date of Incident],
 [Incident Header_tbl].Narrative,
 [Incident Header_tbl].[Assigned to],
 [Incident Header_tbl].[Status of Report],
 [Incident Header_tbl].[Reporting Officer]
FROM [Incident Header_tbl], Location
WHERE Location.Abbr = [Incident Header_tbl].[Location of Incident] ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,
This SQL statement doesn't select from the Location table. It does have a
Location of Incidentfield. How does the Incident Header_tbl link to the
Location table and what do you wish to see?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Here is the SQL view.
SELECT [Incident Header_tbl].[CSS Incident #], [Incident Header_tbl]..
[Code & Type of Incident], [Incident Header_tbl].[Location of
Incident], [Incident Header_tbl].[Date of Incident], [Incident
Header_tbl].Narrative, [Incident Header_tbl].[Assigned to], [Incident
Header_tbl].[Status of Report], [Incident Header_tbl].[Reporting
Officer]
FROM [Incident Header_tbl];
Karl- Hide quoted text -
- Show quoted text -

The Incident Header_tbl, Location of Incidentfieldis linked via a
combo box with the control source as the Location table, with
properties ofcolumncount 2, and boundcolumn1.

I wish to see the long form for the location, located incolumn2,not
the abbreviation.

Karl- Hide quoted text -

- Show quoted text -

thanks for the suggestions, i still have not been able to get it to
show the correct column. i can send you a copy of the database if you
wish. I do not want to impose on the holidays, there is no rush to fix
this.
Karl