You should have a table that stores the Client ID and the Category ID. If
categories 2, 4 and 9 apply to client 123, you'd have 3 rows in that table:
ClientId CategoryId
123 2
123 4
123 9

Signature
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
> Rick
>
> Are you saying I would have to set up a table for each of the 200 check
> boxes? Any or all of the check boxes could apply to a client.
Vincent Johns - 06 Oct 2005 17:22 GMT
> You should have a table that stores the Client ID and the Category ID. If
> categories 2, 4 and 9 apply to client 123, you'd have 3 rows in that table:
[quoted text clipped - 3 lines]
> 123 4
> 123 9
This is an especially good solution if you typically have only a few
boxes checked (= [CategoryId] values) for each of your clients. If most
of the boxes are usually checked, however, you'll wind up with lots of
records in this Table.
You could define your [CategoryId] values, for boxes that are usually
checked, to be true if and only if the box is NOT checked. Then you'd
store records only for the less frequent cases and keep the size of your
Table down that way.
Or you might use a combination -- check boxes in the main record for
some properties, [CategoryId] values in linked records in a separate
Table for other properties.
In any case, I suggest that you CLEARLY identify in comments or
elsewhere, for anyone else who later has to maintain the database, or
for yourself six months from now, EXACTLY what each checkbox or category
value means. Otherwise, you'll have endless trouble and be sorry you
ever embarked on this project.
-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.
> Rick
>
> Are you saying I would have to set up a table for each of the 200 check
> boxes? Any or all of the check boxes could apply to a client.
No, I think he's saying that you can group them. Are they totally
random, or is a "Yes" in box 50 often associated with "Yes" in box 69?
If they're related, they could share a record in a Table, and other
unrelated boxes could be in another Table. The record would contain, in
addition to some of the check boxes, a key linking it to the client's
information in the main Table.
Doing all this will actually *increase* the size of the database unless
you can arrange it so that some of these Tables will frequently contain
records that are entirely blank and can thus be omitted. Otherwise, I
think you might just as well keep everything in one record; Access
doesn't care unless the number of fields reaches 255.
-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.