Lookup table troubles in query

[Guest]

I am using a database, that I admit I created before I was the wiser, and
used combo boxes (lookup fields via the Wizard) in the database table. The
database is now filled with data.
The table has multiple fields, but there are four fields from which I would
like to extract data and combine into one field. Field one is a yes/no type
and the other three consist of combo boxes. In building a SQL statement in
the query builder, I am unable to create a statement that uses data in the
combo box fields as a filter--as you would expect. It is unable to find the
criteria that I specify in the query. Is there anyway around this mess?

 

[John W. Vinson]

I am using a database, that I admit I created before I was the wiser, and
used combo boxes (lookup fields via the Wizard) in the database table. The
database is now filled with data.
The table has multiple fields, but there are four fields from which I would
like to extract data and combine into one field. Field one is a yes/no type
and the other three consist of combo boxes. In building a SQL statement in
the query builder, I am unable to create a statement that uses data in the
combo box fields as a filter--as you would expect. It is unable to find the
criteria that I specify in the query. Is there anyway around this mess?

Create a Query joining the three lookup fields to their corresponding lookup
tables. There will be four tables in the query design window; you'll have all
of the fields available for searching.

John W. Vinson [MVP]

 

[Jeff Boyce]

See comments in-line below...

I am using a database, that I admit I created before I was the wiser, and
used combo boxes (lookup fields via the Wizard) in the database table.
The
database is now filled with data.

Even if you created one/more tables with a lookup datatype, the value
ACTUALLY stored in those fields is the underlying foreign key. You could
modify the table definition to change those fields to their underlying
foreign key's data type without losing data. (Backup first, of course!).

The table has multiple fields, but there are four fields from which I
would
like to extract data and combine into one field.

Combining data from multiple fields into a single field is not considered
good database design. "One fact, one field" is the approach to follow.

Field one is a yes/no type
and the other three consist of combo boxes. In building a SQL statement
in
the query builder, I am unable to create a statement that uses data in the
combo box fields as a filter--as you would expect. It is unable to find
the
criteria that I specify in the query. Is there anyway around this mess?

Back to the top -- are you trying to use the values from the combo boxes IN
THE TABLES? If so, remember that the values stored are NOT the values seen.
First change the field data types to match their underlying foreign key data
types. Then try doing the querying again.

Regards

Jeff Boyce
Microsoft Office/Access MVP