Need advice with table design - multiple selections

[Larry Kahm]

Client has expectation of a form that has multiple checkboxes for a series
of categories, for each project, which allow for multiple selections.

Category - Checkbox fields
Existing building - Renovation, Structural investigation, Adaptive reuse,
Master planning, etc.
Historic designation - National Register, International, SHPO, etc.
Next category - Field1, Field2, and so on

Any one of the categories can be used and one - or more - of the fields will
be checked.

My current thinking is to put each of these field keywords into a table as
Yes/No types and link this table to the main table by ProjectID. But I have
a feeling I am simply building a spreadsheet in Access - something I know to
avoid.

Stumped at the present, I'm open to suggestions...

Thanks!

Larry

 

[Tom van Stiphout]

On Tue, 25 Nov 2008 08:47:44 -0500, "Larry Kahm"

The proper data structure would be:
tblCategories
CategoryID PK
CategoryName UIDX

tblItems
ItemID PK
CategoryID
ItemName

tblItemsOnProjects
ItemID PK
ProjectID PK

That's one school of thought. In this case if a record is in the
junction table it is selected.
Another school of thought is:
tblItemsOnProjects
ItemID PK
ProjectID PK
IsSelected YesNo

Problem with that is what to when the Items collection changes.

-Tom.
Microsoft Access MVP

 

[Larry Kahm]

Thanks I'll give this a try-out tomorrow.

Larry

On Tue, 25 Nov 2008 08:47:44 -0500, "Larry Kahm"

The proper data structure would be:
tblCategories
CategoryID PK
CategoryName UIDX

tblItems
ItemID PK
CategoryID
ItemName

tblItemsOnProjects
ItemID PK
ProjectID PK

That's one school of thought. In this case if a record is in the
junction table it is selected.
Another school of thought is:
tblItemsOnProjects
ItemID PK
ProjectID PK
IsSelected YesNo

Problem with that is what to when the Items collection changes.

-Tom.
Microsoft Access MVP

 

[Larry Kahm]

Tom,

I've built a sample of your suggestion and I'm still stuck.

tblCategories has 3 entries
tblItems has 9 entries

I just can't "see" the query to build that will allow me to create a subform
that has those 9 tblItem fields as checkboxes.

Thanks!

Larry

 

[Tom van Stiphout]

On Tue, 25 Nov 2008 18:25:48 -0500, "Larry Kahm"

Hi Larry,
Did you choose option A or B?

Once we have the ideal data structure, we can worry about how to best
implement the UI. But we will rarely compromise on that ideal data
structure, because it will continue to pay dividents.

-Tom.
Microsoft Access MVP

 

[Larry Kahm]

I went with option B.

On Tue, 25 Nov 2008 18:25:48 -0500, "Larry Kahm"

Hi Larry,
Did you choose option A or B?

Once we have the ideal data structure, we can worry about how to best
implement the UI. But we will rarely compromise on that ideal data
structure, because it will continue to pay dividents.

-Tom.
Microsoft Access MVP