I have a question and really hoping you guys can help or point me to the right direction.
MY SCENARIO:
I need to design a screen which show up all the questionnaires and the users can select 1 of the checkboxes (Frequency) for each question. The users can either select Never, Few Times, 1-3 Times a month, 1-5 Times a week and Everyday.
I store all the questions in the database table, tblItem, and display them in a continuous form. In my database design, I have 3 tables to hold the data.
tblUsage - tblItem - tblFrequency -
---------------- ------------------------ --------------------------
Usage_ID Item_ID Freq_ID
Item_ID Item_Description Freq_Description
Freq_ID
QUESTION: However with this database design how can you bound the Frequency to the checkboxes display in the continuous form? Is there a better way to do this.
I was thinking of changing the tblUsage to add in more frequency fields like Freq_Never, Freq_FewTimes, Freq_1To3PerMth, Freq_1To5PerWeek, Freq_Everyday so that I can directly bound the fields to the checkboxes. However this will create data redundancy.
Please take a bit of time and help me as I’m new to Access programming and I don’t know where to turn to for help.
Thanks in advance for any help.
kind regards,
John.
Assumptions:
__1 [Freq_ID] is an AutoNumber Data Type and is the Primary Key in tblFrequency, as in:
Expand|Select|Wrap|Line Numbers
- Freq_ID Freq_Description 'AutoNumber (Primary Key)
- 1 Never
- 2 Few Times
- 3 1-3 Times a Month
- 4 1-5 Times a Month
- 5 Everyday
B. Create a 1 to MANY Relationship between [Freq_ID] in tblFrequency and [Freq_ID] in tblItem as in: tblFrequency.[Freq_ID](1) ==> tblItem.[Freq_ID](MANY)
C. Create a Form whose Record Source is tblItem.
D. Create an Option Group on this Form with 5 Labels to match your [Freq_Description] Field and set the value of these these Items to match the [Freq_ID] Field in tblFrequency as in:
Expand|Select|Wrap|Line Numbers
- Value Option Group Labels
- 1 Never
- 2 Few Times
- 3 1-3 Times a Month
- 4 1-5 Times a Month
- 5 Everyday
F. Name the Option Group fraFrequency for easy identification.
-------------------------------------------------------------------------------------------------------------------
Now, you simply enter an Item Description and select from 1 of 5 Frequencies via a Check Box. A Number between 1 and 5 will now be stored in the [Freq_ID] Field in tblItem. Should you later need a visual description of the Frequency, you can create a Query with both tblItem and tblFrequency (Link will automatically be established). Drop the [Freq_Description] Field onto the Query Grid.
-------------------------------------------------------------------------------------------------------------------
NOTE: Should you later modify or add a Frequency, the Option Group will have to be re-configured.