Using Multi-column look-up tables

[Reh]

Hi!
I am designing a simple database as an intern, having no experience with Access beyond my exploration as part of work experience. Well, I have been tasked to create an additional, more complicated database, and if I don't turn something out (even though it wasn't part of my original job), I won't look very good!

I have one problem that I think if I can get it fixed, everything will be peachy. I have looked in several help books and searched on Google, but I think maybe I just don't know what to search for...

The Problem: I am using a multi-column lookup table and have it set to be able to select multiple rows. The drop-down looks like this in my database:

But when I check multiple boxes, it looks like this in the datasheet view:



Is there any way to make it display the information from both columns? I need to be able to search on this information and filter by it.
Or if you have any alternative methods for displaying the information, I'll hear any suggestions!

Thanks!

[June7]

This is saving data into multi-value field in table. It is saving only the value from the bound column. You need to save a record ID from the source table. And then only the record ID will show. No, don't think can save values from all the columns of the dropdown.

I refuse to use multi-value fields because they violate relational database principles and are difficult to work with. Multi-value field is a convenient, quick, easy way to input multiple data but makes querying, filtering, grouping on this data harder.

Normalized data structure would require a dependent table to store records of these selected items. A multi-value field sort of does this, only Access hides the dependent table.

[orange]

I agree totally with June7. Multivalue fields are not the way to go.
I strongly suggest you read the first few topics at this site.
http://www.rogersaccesslibrary.com/forum/topic238.html
Normal forms, normalization and entity relationship diagramming

I can assure you that getting your tables and relationships well designed will save you much time and frustration in the long term.
Good luck with your project.

[rpeare]

I have nothing to add, I'm just agreeing!

Multi value fields are a terrible, terrible, terrible temptation for newer users that make programming much, much harder than it needs to be.

[Reh]

Thanks mucho, guys! Luckily I am at the very beginning of this database, so changing it is no headache at all =)