query, with lookup fields

C

cdoyle

Registered User.
Local time
Today, 15:43
Joined
Jun 9, 2004
Messages
383
Hi,

I have a table that has 3 columns.

Issue, Review, and Information.
These 3 fields get their data from the same lookup table that holds some code values.

So I'm trying to create a query to export to excel that shows the data for this table, but for the lookup fields, I just get the ID's from the lookup table.


So the table is like this now

ID...Issue...Information....Review
1.......2..........5...............1
2.......1..........12..............6


So I would like to be able to display what the ID numbers actually are, but having trouble getting a query to do this for all 3 fields.

I can run a query that shows 1 field's but not all 3.

what I would like to see

ID.....Issue...Information....Review
1.......2a.........4c.............e5
2.......1a.........3c.............2F

all 3 columns use code_ID from the lookup table.

Is there a way to do this? or should I have used 3 different lookup tables for these?
 
I did,

my query has table IRR which holds the records, and the then the lookup table.

maybe I'm not building the query right, how would I include the lookups descriptions for all 3 columns?
 
I think I might be missing something.

I have both tables added to the query,

I've added all 3 columns in question to the query grid.
If I run the query right now, for those columns I just get the lookup numbers from the look up table.
 
here is an example db that I made,
I have a form with 3 combo boxes, that use the same lookup table.

So my table has 3 columns that get their data from the ID column of the lookup table.

Now I'm trying to build a query, that lets me see the actually lookup values and not just the numbers.

I just can't seem to get it for all 3 columns.
 

Attachments

Last edited:
Does my example help show the problem I'm having?
 
I must be just overlooking something obvious?

I should be able to use the same lookup table for those 3 combo boxes right? How do I get the values for the 3 columns to show in a query?

Thanks!
 
My Access is broken so I can't check your file.

Are these table level lookups or are they set up using a combo box in a form?

Table level lookups cause no end of trouble and I would never use one. If you have used a combo on a form then you will need to add the lookup table three times to your query and link one instance othe table to each field in the main table. Then you drag the description field to the query grid, not the ID field.
 
neileg said:
My Access is broken so I can't check your file.

Are these table level lookups or are they set up using a combo box in a form?

Table level lookups cause no end of trouble and I would never use one. If you have used a combo on a form then you will need to add the lookup table three times to your query and link one instance othe table to each field in the main table. Then you drag the description field to the query grid, not the ID field.
Click to expand...

Just want to make sure I understand what you said correctly, what I have is a lookup table that holds the values I need for the combo box.

Then in my form, each combo box uses this table to display it's values. Each combo box is bound to a field in my main table. When an item is selected, the ID# from the lookup table is stored in the main table.

Is this not how I should be doing it?
If not, how should I be using combo boxes?

I did what you said about adding the lookup table 3 times to the query, and then adding then did my joins between the fields, and the lookup table for each one.

That appears to work, only thing I don't like is for the column headings for the 3 fields are all the same in the query. So not sure which one is which.
 
cdoyle said:
Just want to make sure I understand what you said correctly, what I have is a lookup table that holds the values I need for the combo box.

Then in my form, each combo box uses this table to display it's values. Each combo box is bound to a field in my main table. When an item is selected, the ID# from the lookup table is stored in the main table.

Is this not how I should be doing it?
If not, how should I be using combo boxes?
Click to expand...
No that's fine

I did what you said about adding the lookup table 3 times to the query, and then adding then did my joins between the fields, and the lookup table for each one.

That appears to work, only thing I don't like is for the column headings for the 3 fields are all the same in the query. So not sure which one is which.
Click to expand...
You can add an alias. Either edit the field name in the grid by inserting 'MyNewFieldname:' before the existing name or edit the SQL to say 'AS MyNewFieldName' after the existing name in the SELECT statement.
 
Great! that worked perfectly.

I was hoping something like this could be done, just didn't know what to search for on the forum. I thought I tried alias too, but didn't see anything.

Thanks Again
 
Hi there, I found this while searching because I have had this same problem for weeks now and really have to figure it out because it is giving problems in reports.

I have a main table which looks (up) into two related tables by using the lookup feature in Access. The problem is when I print from the main table, it shows only the ID in the main table and does not automatically resolve the IDs (look them up) in the two related tables. The ID won't have any meaning to the user, I need to display the Description that the ID is pointing to in the related table to the user.

I have tried using all sorts of joins, but it doesn't work. I came across this thread which describes some of what I was doing but you all got it to work.

What kind of join did you do when you said
I did what you said about adding the lookup table 3 times to the query, and then adding then did my joins between the fields, and the lookup table for each one.
Click to expand...
 
It sounds like you are using table level lookups. Don't. Delete any table level lookups and do them in the form. The problem from the table level lookups is that the ID is stored but the lookup value is displayed so you (and Access) never really know where you are.
 
You must log in or register to reply here.

Similar threads

southwestgooner
Help!!! Update Query to update fields in table???
Replies
2
Views
168
Pat Hartman
P
T
if all subrecords is=* then show me records from main one.
Replies
11
Views
176
TipsyWolf
T
E
Issues creating a lookup in a table
2
Replies
24
Views
409
Mike Krailo
Mike Krailo
S
how to show picture on form by lookup from other table field
Replies
7
Views
346
Sokkheng
S
N
How to view/amend Lookup Values in Access 2016
Replies
1
Views
271
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom