Multiple fields to the same Lookup Table

[igooba]

Hope you can help me with what is probably a fundamental design question. (I am a novice)

I am creating a report/letter that needs to include up to 9 text phrases selected from a lookup table by the user from a form.

Table 1 - Healthplan


Healthplan-ID (autonumber)
PhraseField1 (lookup L-Phrases-ID)
PhraseField3 (lookup L-Phrases-ID)
PhraseField4 (lookup L-Phrases-ID)
PhraseField5 (lookup L-Phrases-ID)
PhraseField6 (lookup L-Phrases-ID)
PhraseField7 (lookup L-Phrases-ID)
PhraseField8 (lookup L-Phrases-ID)
PhraseField9 (lookup L-Phrases-ID)

Table 2 - Phrases
L-Phrases-ID (autonumber)
L-Phrases (text)

In the report, I am using free text fields similar to the below format -

="The client presented with the following symptoms- "&[PhraseField1]&", "&[PhraseField2]&", "&[PhraseField3]

Unfortunatly - the report displays the field value [PhraseField1] as a number (L-Phrases-ID). And if I refer to the lookup table [L-Phrases] this is associated with multiple fields so doesn't work.

To further confuse my situation, I noticed that if you run the "lookup wizard" in the table design for each of the 9 fields, in database tools/relationships it shows 9 lookup table copies.

However if you create one field and run the lookup wizard and then copy and paste the other 8, then change the name to be unique PhraseField1,PhraseField2 etc, Then they all are pointing to the same lookup in database tools/relationships .

Thankyou in advance.
peter

[June7]

I don't use the 'lookup wizard'. I don't set these properties in tables. I set lookups in combo and list boxes on forms. Users should not interact with tables and queries, use forms instead.

Either setup should work. 9 copies in Relationships okay. This occurs because your data is not normalized structure. It might suit your output requirements but as you can see the relationships will look awkward.

[igooba]

Thanks June7, I note your point about the lookup wizard, will use the form in future.

Besides an ugly design, the show stopper for me, is that I don't know how to get a report field [PhraseField1] to display the lookup table result rather than the ID number.

If I drag the field from the selection window onto the report it works, but if I use the field in an expression with the [] brackets it displays the number.

[June7]

Right, that is an evil of lookups set up in tables. They make you think you will get one thing when you get the other. Yes, expressions that refer to the field will ignore the lookup value and use the actual field value. The drag and drop is taking the properties you set up in the table and applying them. Does the control show on report as a combobox instead of simple textbox? If you want the related info available on report for expression, need to make the report's RecordSource a query that joins the tables on the key fields. That is basic functionality of relational database.

[igooba]

Yes the control when drag and dropped onto the report does show as a combo box.

The report data source is a query that contains both the main table and the look up table with joins. When you run the raw query it works displaying the lookup values.

[June7]

Good. Then the related info is available to the report.

[igooba]

Unfortunatly it is only avaliable to me in the query and when a drop and drag the combo box to the report. I can't access the values in an expression such as =[]

[June7]

Well, that doesn't make sense to me. Do you want to provide project for analysis?

[igooba]

Thanks for the offer, it has medical records in it, so will copy the relevant objects and will post later tonight.

[June7]

Some dummy data in the tables will be helpful.