Querys and Lookup fields

[Birdman895]

Hi,
I have a table with multiple Lookup fields . I am trying the create an append query to copy data from this source table to another table.
From the fields that are lookups in the source, I end up with a number in that field in the target table, instead of text (I need text)
I've tried making that field in the target table a lookup field also, pointing to the same lookup table, but the query responds with an error about an INSERT INTO query cannot contain a multi-valued field. But I would really rather have text anyway.
How do I query text from a lookup field instead of the numerical value?
Thanks

 

[boblarson]

Hi,
I have a table with multiple Lookup fields .

This is your first, and biggest, error. Get rid of lookups at table level. See here for why:
www.mvps.org/access/lookupfields.htm

Get rid of them and then all will work right.

 

[Galaxiom]

One of the things that is worse than Lookup fileds on a table is the use of Multi-Value fields. They look simple to use in forms but in fact they just make everything harder.

Moroever the goal of copying the text to another table also suggests you are are heading in entirely the wrong direction with your design. You need to closely study normalization before you continue with your project.

 

[Birdman895]

One of the things that is worse than Lookup fileds on a table is the use of Multi-Value fields. They look simple to use in forms but in fact they just make everything harder.

Moroever the goal of copying the text to another table also suggests you are are heading in entirely the wrong direction with your design. You need to closely study normalization before you continue with your project.

Thanks for the help guys. I have already replaced one of the lookup fields with a value list in the form, and problem solved. I can now combine the values in two fields into a field to create a unique ID for each client.
As far as copying text; this part of the database collects all of the information that a new patient provides at admission. There were over 340 questions, almost all of which had to be searchable. A person that I'm working with suggested turning those questions into records in a table. Each time I start a new patient I will append the records containing the questions into the table where the answers will be entered.
Don't know if that makes sense or not, but thanks again for the help!

 

[Galaxiom]

Each time I start a new patient I will append the records containing the questions into the table where the answers will be entered.

That definitely sounds like a normalization error.

You don't repeat the text of the question over and over in each patient/visit record.

You should have a junction table. This holds the QuestionID from the Question table and the ID of the Patient or visit record where the question was asked. There will be one record in the junction table for each question and patient/vist combination.

When you display the main record the database retrieves the text of the question from the Question table according to the junction records.

 

[Birdman895]

That definitely sounds like a normalization error.
..There will be one record in the junction table for each question and patient/vist combination.
When you display the main record the database retrieves the text of the question from the Question table according to the junction records.

I think that the ClientResponse table is a junction table.
I had thought about that issue with copying the text of the question into the response table, but I thought about changes being made down the road in the Question table and it changing the existing questions and answer.This is a medical record, collected once, and will be archived in the future.
Thanks for your help. I am re-examining the modules that I still have to create according to your input.
Alan

 

[Galaxiom]

Don't allow changes to the questions. Any "changes" should be implimented as new questions. Better to have a single copy of the text.

Moreover if your records just store the text to each question in the patient record itself it will be far more complex if you want to gather grouped data on a particular question.

Include a field in the Questions table to indicate whether it is in current use or archived. You might also consider recording the connection if a question is replaced by another.