MultiSelect for Dummies!!

[mjchristinaj]

Hi,

I have been searching for info about Access 2003 MultiSelect option. I have found a lot of info, however, I do not know how to use it.

I want in my forms a field where I can select more than 1 value. It seems like you can do this with a list box. It is fine for me till here.

I have created the list box and I have introduced the values that I want to be shown (I have introduced them manually; they are not read from a table or query). The list box in called “applications” and the possible values are: Broadband and Broadcast (they could be more). I have selected MultiSelect=simple or extended in the properties. Doing that, it is possible to select 2 values.

My problem: I do knot know how to read that selection!! How to store it??!!

If the selection of that field is for a form which introduces data in a table, then I need those selected values to be stored in the field “applications’ in the table. So, there will be an entrance in the table whose field application is = Broadband Broadcast, if both are selected.

If the selection of the field is for a QBF, then I need the selected values to be used in the criteria in the query. For example, I can make a query to search those who have in applications de values Broadband Broadcast. Or search for those who only have Broadband…

All the info I found about this involves macros, and I found some code examples. My problem is that I have no idea about macros. I do not know where I need to save the macro code, or what to do to execute it… When I try to copy the code examples, I do not know where to copy them… Could someone explain me step by step, for dummies, what I need to do?

Thank you!

 

[MarkK]

If you have a object that has mutiple related objects, and those related objects are of the same type, then you have a one-to-many relationship and the simplest solution requires two tables. Then, in your form design, you have a main form handling the 'one' side of the relationship and subform handling the 'many' side. In your case the subform would replace your efforts to use a listbox.
Though a second table may initially appear to be extra work, the simplicity it brings to your data storage and retrieval, and the simplicity it brings to your user-interface design make it well worth it.
Cheers,
Mark

 

[mjchristinaj]

Hi lagbolt,

thank you for your answer. I tried also the option of doing 2 tables. However, I do not see it as a one to many, but as many to many. In my case, the main table is 'satellites', so I store every satellite (name, date, year in orbit..., and its applications). So, a satellite can have one or more applications. At the same time, one application can be in different satellites.

If my assumption is right, and it is many to many, I built that relationship, with 3 tables: satellites, applications, and a linktable (to get many to many). The problem that I face with this, is that when I do a query to see all the names of the satellite , I get the names of the satellite repeted as many times as applications has. Is there a way to correct this?

Thank you!

 

[MarkK]

So we are not dealing with a listbox then? Instead the query returns repeated values?
I don't understand this:

I get the names of the satellite repeted as many times as applications has.

The table satellites should have one record for each satellite. I don't understand how it is then difficult to return a list of unique satellite names, if that is the problem.
Cheers,

 

[mjchristinaj]

I will try to explain my problem better. Let's forget about the list box for now.
I want to store one record per satellite, but each satellite could have one or more applications (Broadband, Broadcast, Military...). So, when I display all the satellites, I would get a table like this:

Satellite 1 2001 Broadband
Satellite 2 2001 Broadband Broadcast
Satellite 3 2000 Militar
Satellite 4 1999 Broadband Broadcast

So, how can I store the applications?? I thought I could do it with a list box. If not, I thought of creating a many-to-many relationship, between satellite table and Application table. The problem I face when I do that is that the kind of search that I get is something like this:

Satellite 1 2001 Broadband
Satellite 2 2001 Broadband
Satellite 2 2001 Broadcast
Satellite 3 2000 Militar
Satellite 4 1999 Broadband
Satellite 4 1999 Broadcast

Do you understand my problem better now? My first question should have been on how to desing the structure, instead of asking for listbox help... My apologies!

Thanks!

 

[phillipmai]

I'm trying to do this also... have found this...

http://access.mvps.org/access/forms/frm0007.htm

I have not figured out yet where to put the code so that I can select it in my criteria...

 

[spikepl]

#5 Your second attempt, using a many-to-many relation is suitable and just fine. Don't confuse
design of data structure with display of data. These two are different in a relational DB, as opposed to Excel.

To display the data the way you want, you can use a crosstab query (like a Pivot table in Excel)

 

[MarkK]

To present data like this use a report. In a report you have sections that are grouped on particular values in your data, so the parent data would be in a group header and then the detail section of the report would repeat for as many child elements as exist for each parent.
You can also use a crosstab query as mentioned here, but in a crosstab you don't know in advance how many fields will be returned, so it is difficult to bind a crosstab to a form or a report. Instead a crosstab is more useful if you create pivottables or pivotcharts.
A form is designed as a tool to manipulate your data, and manipulating data in this format...

Satellite 1 2001 Broadband
Satellite 2 2001 Broadband Broadcast
Satellite 3 2000 Militar
Satellite 4 1999 Broadband Broadcast

...is not really workable since it is not normalized.
So those are to some extent the constraints. 1) Use forms, normalized, to manipulate your data. 2) Use reports, which denormalize your data, for display. And 3) Pivottables and pivotcharts with crosstab queries for even more detailed reporting.
Hope this helps.