options for initial design of table

Hi

In a weak moment I have offered to write a database for a local charity who also works with artists.

They need to keep detailed info on artists, and so one of the fields they want is to record the artists 'artform'. They have drawn up a list of 17 different artforms, which at the moment I have put as tick boxes on each record.

They have said that this list of artsforms is likely to be added to as the list if artforms grow.!

When they come to print off reports/labels etc, they will use the artform field to filter out records.

So the question for me is:

I know there is a way of having a table of artforms that can easily be added to, so that when one is entering artist info into a record a combo box comes up with the most current list of artform. But I don't think that this can be done so that each artform selected has a tick in its box.

Would you have to have a text box in which you put each artform selected from the combo (some artists will have more than one artform).

If so, will this cause problems when i want to filter out records by the artform?


Is my only option to keep the tickboxes, and just add more as they add more artforms?

Anyone with experience of this would be a great help!

Thanks
ian

Have a table called tblArtforms.

A table called tblArtist.

A table called tblArtistsToArtforms.

Then do a search on many-to-many relationships on this site. I can't answer much more as I'm heading off for a bit.

The checkboxes is a BAD idea. Stamp it out early.

Thanks for the reply and the pointers. I have downloaded the two 'many to many' samples on the forum, and will have a look.

Never having used junction boxes before, and my own database is littered with check boxes, it is going to be an interesting day!...

many thanks

Ian

artsalive said:

Never having used junction boxes before

Click to expand...

Junction tables. It's just another table.

As Access doesn't directly support many-to-many relationships you need to create two one-to-many relationships as one artist can have many artforms and one artform can be performed by many artists.

Maybe a ListBox which takes it's values from tblArtforms would seem to fit the bill?

What I can't seem to get my head round (though after a day programming my mind is often too mushy to see what is obvious), is how to store the values for each record.

An artist can pick say 10 different artforms from the listbox (or combo box), do I just store these in one field - or will this cause problems when I come to wanting to filter out records for mailing labels?


I do love programming but my lack of knowledge does create a sesne of frustration sometimes!

Many thanks for your help.
Ian

Use a subform:

Wow, you are a star! Thanks. I think it would have taken me some time to make the jump to this, but now i see it...

Cheers

Ian

No probs.

And the database means what it says about Damien Hirst and Tracey Emmin.

Hi Mile-O-Phile

I have been working on the dbase today and thanks to your example I have managed to get a long way.

However at one point in your ArtistToArtform table you have two Primary Key fields. I can't seem to get that on my table - what might I be doing wrong?

when I make another field the primary, the one that was primary stops being primary!

Cheers
Ian

I just did an internet search and came up with the answer - you have to hightlight both fields and do them both att he same time!!

Cheers

Ian