Help with Access 2007--selecting multiple items to be the value of a field.
May 10, 2010 8:28 PM   Subscribe

How do I construct a field in Access 2007 that selects one or more of a group of items from a list to be the value of that field. TRICK: I can't use a look-up field. More inside.

I am working on an Access database for a class. The scenario is a small internet business selling handmade crocheted children's toys and clothes. One of the things that database has to track is what dyelots of yarn are used in each item, due to new laws about lead dye. Each item will have multiple dyelots and each dyelot will be used in multiple items.

I have a dyelot table that just lists the dyelot id and the color of the yarn, and an items table will have a field for dyelots related to the dyelot table. When the user enters a new item into a form, I'd like there to be a drop down-box that lists all available dyelots, and the user can check off each dyelot that was used in that item.

I was thinking a look-up field could accomplish this, but our teacher has said that a lookup field is not a real datatype, and there are other ways to do what a look-up field does. We haven't learned those things.

Any ideas? I can elaborate and give more info if needed.
posted by odragul to Computers & Internet (7 answers total) 1 user marked this as a favorite
 
Response by poster: I should add that Plan B is to scratch the dyelots table entirely, make the dyelots field in the items table a memo field where the user can enter in as many dyelot numbers as they wish, and then create a form to search the dyelot field for a specific dyelot in case of a recall. If any more elegant options are too big of a pain in the ass, I'm OK sticking with plan B.
posted by odragul at 8:43 PM on May 10, 2010


Best answer: You might be better off with three tables than just two: an items table, a dyelot table, and a items-dyelot table, that has one of each thing.

So, if you had "blue" "black" and "white" available as colors, and you have an item "rock", the third table would have entries like "rock","blue" and "rock","black". Really you should be using a unique key in this combo table instead of the words.

In database design, anything that requires a field to store multiple links to another table is probably the wrong way to go about it.
posted by that girl at 9:20 PM on May 10, 2010


Response by poster: Thanks. I considered that (that's a bridge table, right?) -- the problem that raises is how to create a form that lets the user input that info?
posted by odragul at 9:43 PM on May 10, 2010


Best answer: You may well have to make them add one at a time, or else finagle something with a multiple-selection list box and some VBA. I am not as familiar with Access in particular as I am with databases in general. It looks like this can be done with some relatively simple programming, but I'm not sure how up for that you are.
posted by that girl at 10:11 PM on May 10, 2010


Response by poster: OK, this is making sense in my mind. I have a few days and if I can make the bridge table and have a concept in mind for the for, I'll feel comfortable asking the prof for help with the VBA.

Thank you so much.
posted by odragul at 11:07 PM on May 10, 2010


First, that girl is absolutely right. You use a many-to-many ("cardinality reduction", "bridge") table.

As to what you show, you show a listof dyelots, with those associated with the current item checked. Then the user can check or uncheck associations.

The SQL for this is:

select a.name,
(select coalsece(
b.id from item_dyelot b where b.dylot_id = a.dyelot_id and b.item_id = ?,
0) as item_dyelot_id
from dyelot a
order by 2, 1;

(Note there's a potentially more efficient way to do this, depending on your RDBMS; but let's not worry about that now.)

In other words, list all dyelots, and if this item is associated with that dyelot, list the id of the association; if there is no association, list zero. The paramweter "?" is replaced with the item id for the item we're currently associating dyelots with,

On an was checked -> unchecked, delete the association (by its id); on an unchecked -> checked, insert the association (presumably the id is autoincrememted).
posted by orthogonality at 11:39 PM on May 10, 2010


If I recall correctly, one "other way to do those things" is to use a combo box sourced from one of your tables to choose the ID of a table row, but you make it a two-column box and set the width of the ID column itself to zero; that way, the combo box still returns you the ID you need, but all the user sees is the values from the other (presumably text) column.
posted by flabdablet at 5:43 AM on May 11, 2010


« Older And there ain't no nothin'...   |   "Plugging Holes", with your host Bob Vila Newer »
This thread is closed to new comments.