Lookup answer from non-linked table

[Aubs010]

Hi,

I have a form that populates a table.

One of the fields in the form can have multiple answers

I will use colours as an example, because what I have is actually more complicated than what I am showing!

Say for example the ball is green and blue.

This needs to be saved in one field on the table.

The options for colour are saved in another table, in the form:
[tt]
| ID | Colour |
|----+--------|
| 1 | Green |
| 2 | Red |
| 3 | White |
| 4 | Blue |
| 5 | Yellow |
| 6 | Black |
[/tt]

When the user selects more than one option from the ListBox, the ID's of the corresponding colours are entered into the form field using VB, for the example above: [tt]1,4[/tt]

This is fine, because the user never sees the ID's, only the text for the colour.

If at a later date, it is decided that blue is no longer blue, and it should be yellow, the table can be changed and the records will now show [tt]green and orange[/tt] still in the original form "1,4".

The problem I have is with reporting.
I can obviously get it to return [tt]1,4[/tt] but I can't for the life of me find out how to get it to return [tt]green and blue[/tt] or [tt]green and orange[/tt], whichever it is decided to be.

Any suggestions greatly appreciated.



Aubs

 

[RoyVidar]

This would be more a table/relationship question (forum700)

You should not store more than 1 value in one field. It breaks the rules of Normalization, which really isn't thrown upon us to make our time hard, quite the contrary. As you've found out, it is not easy to do something meaningflull (find/sort/report on) on multiple values in one field.

What is most commonly used when there's a M:N relationship (many to many), is creating a junction table containing the primary keys of both tables, then you can select more than one Color per each ball, and easily report.

Roy-Vidar

 

[Aubs010]

Yeah, I know what you mean, but I can't see how it will link together...

I'll ask the question in the other forum.

Thanks for your help Roy, much appreciated.



Aubs