Many-to-many with checkboxes

[pteare]

Hello,

Just a quick question on the best way to attack many to many joins.

I've a table of Tour Operators, and a table of Countries. I'd like a many-to-many join between them.

I'm using access only as a front end, the tables themselves will be on a SQL server.

I'm struggling to get a decent UI for this. Ideally I'd like some checkboxes. So you could have a form for the TourOperator, and on this for a check box for each country. As you tick / untick the boxes it would need to add / delete a line in the joiner table.

This seems a pretty standard requirement, yet I cannot find any simple way to do this? I've done it before in other systems, just not with access.

I've had a play with using the a option in access for "Allow Multiple Values" when you create a lookup and setting that to "yes". That gives a good UI nice and quickly, BUT as soon as I put the tables on a SQL backend then I loose the option to select Multiple values - it changes it to selecting one item only.

Any ideas for a decent way to attack this to get tick boxes? Any good tutorials to read? Everyone I find shows me how to create the tables & relationships which I can do, but I'm looking at the UI side.

Thanks!

Phil.

 

[jdraw]

Watch this free video from TrainSignal

 

[pteare]

Thanks for the reply.

That videos just shows me how to setup the junction table. I'm happy with all that side.

My problem is what to do next - how to use that data structure to make a checkbox type UI?

Thanks.

 

[jdraw]

It could be as simple as the attached

List of Tutors and a list of Coursenames and a button.

Reality is you have a list of TourOperators and a List of Countries,
when your values match you click the button and add the record to the "joiner" table

 

[pteare]

Hello, thanks for that.

I'm trying to avoid the subform system, thanks though. I'm looking for a checkbox UI.

For example the built-in system for allowing multiselect would be okay:

The problem with that would be that you can only do it with internal tables - I cannot get it to work when using linked tables to a sql database.

Also the data structure it uses isn't exactly what I'd like. I'd prefer to use a junction table, but still have some form of checkbox type UI.

thanks!

 

[spikepl]

If you want checkboxes then the only option apart from multivalued field is a subform.

A common tool for selecting multiple items consists of two listboxes, where you let items "move" from one to the other, depending on whether selected or not (center and rightmost litsbox). Alternatively, you can have a listbox with multiselect enabled, so the selected items are shown directly (see leftmost listbox).

 

[pteare]

thanks for that.

Okay if a list box in a subform is all that's realistic I'll go re-think the data structure. Alas it's not quite what I need in this case, but thanks still for the time, good to know what's possible.

Thanks,

Phil.

 

[spikepl]

if a list box in a subform is all that's realistic

That is your invention, not what I wrote.

Further, what has data structure to do with display? This is not Excel we are dealing with here.

 

[jdraw]

pteare,
Can you tell us more about your requirement. You mention TourOperators and a Checkbox for each Country, but your example doesn't show that specifically. My feeling is that it is Tours or Packages that 'include" Countries, but I'm not in that industry.
I recall a few years ago assisting someone with a PackagedTours application. This included TourPackages, guests, hotel/accommodation, payment, who is in what group, repeat customers etc.
Because his/her data structure did not allow for all the business needs, I recommended he get a trial version of a commercial package and "get an overview understanding" of the tables involved. Whether he chose to buy such a package or quit at trial end, was his decision. I believe he adjusted his structure based on what he learned during the trial.

There are a number of TourOperator software packages. You may take a look at their structures and interfaces via a trial approach.
Here is a quick list of TourOperator software based on Google search.

Taking a quick look at how someone has done the interface could give you additional ideas/options.
Good luck.