Access: Look upfield/make or append query

Topic

New Reply

I am using maketables and append queries on a table with a lookup field. when viewing the table the correct display value is shown. However, when i run a maketable or append query the bound value is shown. I therefore need to link the source table in so as to get the display value.

This is long winded and so I wondered if I am missing something? I cant change the source table as its accessed via Sharepoint and i dont have such rights.

Rupert

Reply | Quote

Replies

One possibility is to join the “look-up” table and use the linked value to paste instead of the index of the linked value. In that way, you’ll have the displayed value and not have to link to a sharepoint table.

Reply | Quote

when viewing the table the correct display value is shown. However, when i run a maketable or append query the bound value is shown.

I would use the exact opposite words to describe this situation. When you use lookup fields you ask Access to display the wrong value in the table. When you move the records to a new table (via append or make table) it shows the correct values. The true value stored in a table when you use a lookup is the value (usually a number) that links to the related table.

Lookup fields are a ruse to show you something other than what is really there. It is common practice to do this with forms and reports, but when you do it with tables you are inviting problems, as you find. Another common problem is that query criteria don’t seem to work, as the true underlying data in the table is not available to you.

Reply | Quote

> Lookup fields are a ruse to show you something other than what is really there.

I agree (and so do many of my MVP friends). Have a look at the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom

Reply | Quote