How to query the index rather than lookup value

[WDSnews]

When creating a query on a field with a lookup setting, the datasheet
returns the looked up value. Is there a way for the query to return the key
rather than the looked up value?

 

[Jeff Boyce]

Congratulations!

You've (re-)discovered one of the (unfortunately many) reasons the regulars
here in the newsgroups hold the lookup datat type in table in low regard.
The fact that the table displays one thing but actually stores the records
key continues to come back to bite you in queries and other places you try
to use that field.

The common solution is to not use the lookup datatype. Instead, convert it
back to a data type compatible with the underlying key and store that key.
When you need to see the "lookedup" value, use a query and join to the table
that holds those values.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LG]

Your are absolutely correct. But, I have to find a way to query through all
this information for reporting back to agencies. I find no way to do this in
excel.

 

[Jeff Boyce]

One way to query the data is to change the table definition so that the
field in question is no longer a lookup data type field.

Then use a query (you can start in design view) and join the table in
question with the table that holds the looked-up values, joining them on
that field. Use the query to provide the basic data set you use for
reporting purposes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Your are absolutely correct. But, I have to find a way to query through all
this information for reporting back to agencies. I find no way to do this in
excel.

Um?

You can use Access to query either by the numeric value or the looked-up
value, depending on how you structure the query.

You can create a query to do any reporting that you can imagine.

Access will indeed be better than Excel for the purpose, it would seem, and
Jeff (a major Access expert) is not suggesting that you do.

His point is that your problem is the use of the "Lookup Field" datatype in
your table. This fieldtype is NOT necessary to accomplish your goal, and (as
you have seen) is positively getting in the way!

 

[WDSnews]

ok. I found a solution. Since the problem shows up less frequently, only
on those occassions when we wish to view the index, but more often we wish
to see the looked up value, the solution is to do the reverse of what was
suggested. I can continue using the lookup feature, since most of the time
it's useful.

For those occassions when I wish to query the index, I can link the lookup
table with the foreign key, and display the lookup table's primary key.

Also in my comboboxes, I do this whenever the field needs to display the
description...

Query: ID, Description, ID
Format: 0";2";0.6"

 

[Andrzej Sagan]

ok. I found a solution. Since the problem shows up less frequently, only
on those occassions when we wish to view the index, but more often we wish
to see the looked up value, the solution is to do the reverse of what was
suggested. I can continue using the lookup feature, since most of the
time it's useful.

For those occassions when I wish to query the index, I can link the lookup
table with the foreign key, and display the lookup table's primary key.

Also in my comboboxes, I do this whenever the field needs to display the
description...

Query: ID, Description, ID
Format: 0";2";0.6"

 

[Jeff Boyce]

Thanks for the kind words, John. I don't know if I can stand the
pressure...!

Jeff B.