Field changes in Make table query

[asherbear]

I have a table with one field that is a lookup from another table (a dropdown). When I run a maketable query it changes that particular field to the ID# of the table. This does not happen in a regular query only a make table query.

I've tried a number of different settings to no avai. Any suggestions are appreciated......

thanks,

Asher

[kimmer]

That is because the field is storing the ID, but is displaying a different field.
Lookup fields in tables are known as the "creation of the evil one". I'd get rid of it. They cause trouble. It takes less than a minute to make a form with a drop-down, so what do you need the lookup in the table for?

http://www.mvps.org/access/tencommandments.htm

[kimmer]

you should be able to add the related table to the original query, add the appropriate field and then make the table.

[asherbear]

I am a little confused so here is what it currently looks like

Table1 has the field with the lookup

t\Table2 has the actual data that is looked up

When I use table 1 in a query that field is changed. When I try to change the field in table 1 to something else it tells me I have to delete the relationship. However, I can't seem to find a way to delete just the field relationship. Am I on the right track??

Thanks for the help

[kimmer]

I want to be sure you understand that after you remove the lookup, the field will still store the ID field, but no longer will display the other value; it will display the ID field.

If you want a make table query that includes the field that isn't the ID field, you will still need to include both tables in the query in order to accomplish that.

1. Create a copy of your db first, or at least a copy of the table
2. Open the table in Design view (the table that has the lookup)
3. Click in the field.
4. In the Field properties pane, go to the Lookup tab.
5. Change the Control type to Text Box (instead of Combo box)
6. Save.
7. Have a look at Relationships and make sure there is a line between the tables. The Relationship button is on the Database tools tab of the Ribbon.

[kimmer]

By the way, you said "When I use table 1 in a query that field is changed'.

But that isn't accurate. You have a lookup set on the field, which means the field isn't storing the same thing it displays. It stores values from the primary key kield from its parent table (as is correct and good). But it displays the values from the prettier field from its parent table. Can you hear the twilight zone theme?

So when you use this field other places, it isn't "changing", it is simply not deceiving you anymore about what is really stored in the field.

Don't confuse the storage of data with the display of data. They are frequently diffferent.


To make a query that includes the pretty values that are displayed in the table, you must add the parent table (I think you called it Table2) to the query because that is where the pretty values are really stored.

[asherbear]

I changed the field with the lookup to a text box from combo box and saved.

When I add the table with the lookup values (tbl_surgeries) to the already existing relationship (tbl_patient and tbl_patient_surgeries) it adds two tables (the original one called tbl_surgeries and one called tbl_surgeries1). When I write the query using this lookup table I get no results. When I remove the table the query gives me results but not the field I need..........I'm sure I'm missing something simple....Thanks again

Asher

[kimmer]

I changed the field with the lookup to a text box from combo box and saved.

When I add the table with the lookup values (tbl_surgeries) to the already existing relationship (tbl_patient and tbl_patient_surgeries)

Asher

Sorry, I don't follow. Are you in the Relationship window or a query?

With table names like that, I can assume that the relationship between the Surgeries table and the Patients table is many-to-many, so you have a junction table... the Patient Surgeries table.

In the Relationship window, you should see a line
from Patients to PatientSurgeries
and also a line
from Surgeries to Patient Surgeries.

Is that what you have?

[asherbear]

Update. I got it to work. Thanks. It took a little while for it to sink in...........

[kimmer]

Yay! Thanks so much for posting the update.