I'd like to create a form with several checkboxes, but I'm not sure how to structure the tables to capture the checked items (1 Viewer)

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
This is essentially a form for a day of tracking tasks. Any number of these boxes can be checked, and I'd like the results saved as one record for that day -- so each day is a record, and each record can have up to 27 total check boxes checked. So Monday any of the 27 tasks would be checked, same for Tuesday, and so on.

Would each task have to have its own YES/NO field in a table for the total number of checked items to be captured in one record? Something seems wrong about this approach but I'm new to databases and cannot think of another way, and I need to store the results of the various check boxes as one record. Thank you!
SNAG-0000.jpg
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:33
Joined
Oct 29, 2018
Messages
21,357
Hi Bettany. Welcome to AWF!

Whatever you do, avoid using multiple checkbox fields in your table. It's better to have a child table to store the completed tasks, rather than use checkboxes. Actually, you may not even need a checkbox field since you already have the date of completion. You can still create the form you posted.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:33
Joined
Jul 9, 2003
Messages
16,244
I have a set of video lessons on YouTube and my website here which explain how to create multiple checklists:-

 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
Thanks for your reply! So I want to put each of the 27 tasks into its own table (maybe called tasks), and list them all in a single field? The problem I had with that approach is when I try to make a form, forms only seem to take fields, not row data -- I couldn't turn the rows of data into text boxes. I tried to make a continuous form, but that still essentially treats each check box as its own record, rather than the result of all the various completed tasks being captured as a daily record.

Hi Bettany. Welcome to AWF!

Whatever you do, avoid using multiple checkbox fields in your table. It's better to have a child table to store the completed tasks, rather than use checkboxes. Actually, you may not even need a checkbox field since you already have the date of completion. You can still create the form you posted.
 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
Thanks checking out your videos now!

I have a set of video lessons on YouTube and my website here which explain how to create multiple checklists:-

 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:33
Joined
Oct 29, 2018
Messages
21,357
Thanks for your reply! So I want to put each of the 27 tasks into its own table (maybe called tasks), and list them all in a single field? The problem I had with that approach is when I try to make a form, forms only seem to take fields, not row data -- I couldn't turn the rows of data into text boxes. I tried to make a continuous form, but that still essentially treats each check box as its own record, rather than the result of all the various completed tasks being captured as a daily record.
Hi. It's more important, for the database developer to get the table structure right. To get the desired UI, in this case, you may have to use an unbound form.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:33
Joined
May 21, 2018
Messages
8,463
The easiest way is to store the yes/no field in the table. You need a data table to store the results. In this case it is tblCustomer_Products. Then you clear and load the checks on the forms current event based on what is in your data table.
 

Attachments

  • SimulateWithChecks.zip
    102 KB · Views: 274

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:33
Joined
May 21, 2018
Messages
8,463
It can be done without having the "selected field" in your table, but this then requires more steps. Your checkboxes become unbound and have to use a function to populate them based on what is in the data table.

In your case since you have only 25 choices (and the assumption is that you are not going to add or delete from these in the future), you could also do this unbound pretty easily. However, if those assumptions are not correct, it is not worth it.
 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
Thanks for your reply! The problem I had with putting them all in, say, a tasks table in field "tasks" is that when I tried to build a form, it only let's me enter one field/text box for field "tasks." From this I gathered that rows of field data cannot be represented in a form, and certainly not in a way that accept form inputs. It's almost like if you want 27 checkboxes, you'll need 27 fields. I'm trying to get a very direct yes or no to that if someone knows the answer.

The easiest way is to store the yes/no field in the table. You need a data table to store the results. In this case it is tblCustomer_Products. Then you clear and load the checks on the forms current event based on what is in your data table.
 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
Thanks checking out your videos now!
I have a set of video lessons on YouTube and my website here which explain how to create multiple checklists:-



Goodness, I do not know how to do any programming. I was hoping this could be done without that. Could you answer this question please, do 27 checkboxes on a form require 27 fields if you're not going to do any coding to make it work?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:33
Joined
May 21, 2018
Messages
8,463
I do not think you can do this without code IMO. The only way to do it without code is to fall into the trap that DBGuy is talking about. However, we can walk you through this. If you can post a sample db with your table and form I can demo this in about 10 minutes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:33
Joined
Oct 29, 2018
Messages
21,357
Goodness, I do not know how to do any programming. I was hoping this could be done without that. Could you answer this question please, do 27 checkboxes on a form require 27 fields if you're not going to do any coding to make it work?
Hi. The short answer is a yes, but the long answer is you'll pay for it later.
 

plog

Banishment Pending
Local time
Today, 08:33
Joined
May 11, 2011
Messages
11,611
From this I gathered that rows of field data cannot be represented in a form, and certainly not in a way that accept form inputs. It's almost like if you want 27 checkboxes, you'll need 27 fields. I'm trying to get a very direct yes or no to that if someone knows the answer.

I don't know what "that" is which are you are requesting a yes/no to. Is it the 27 checkboxes=27 fields? If so, the answer is no. You can have 27 checkboxes that go into just 1 table with 1 Yes/No field as 27 records.

From you first post:

but I'm new to databases and cannot think of another way, and I need to store the results of the various check boxes as one record

Because you are new, you don't understand that you do not need the results to go into 1 record. Like most beginners you've jumped in at the sexy part--forms. The real place to begin with databases is normalization (https://en.wikipedia.org/wiki/Database_normalization). That is the process of setting up your tables and fields. Your data dictates how you structure your table and fields, not anything else.

I suggest you take a step back, read up on normalization, try and apply it to your data without regard of how you want your forms to look, then set up the Relationship Tool in access, expand it so all fields in all tables show, take a screnshot and post it back here. Then we can walk you through making the tables/fields correct. From there you can move on to making reports (that's step 2) the last step is making forms.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:33
Joined
Jul 9, 2003
Messages
16,244
Hi. The short answer is a yes, but the long answer is you'll pay for it later.

This is the problem you face...


You can go the flat file route, but at some stage it is likely you will have to come up with some fancy coding techniques to extract any useful information.

The choice is go for the what looks like the easy route now and pay for it later, or get it right now and have an easy life later!

Only you can make that decision!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:33
Joined
May 21, 2018
Messages
8,463
For your table design, my guess is a junction table that stores

TblDailyTasks
PersonID_FK ' relates back to the person table
TaskID_FK ' relates back to the task list
TaskDate ' date of the tasks.

With this structure you can get all kinds of things
' total tasks for each person by date
' total tasks for each person per date by mini, plus, and elite
. total tasks over a give period
' count of specific tasks over a time period
etc.

So you should have tables something like.
tblTasks
-TaskID_PK 'autonumber
-TaskType ' mini, plus, elite
-TaskSelected 'if going my route

tblPeople
-PersonID_PK ' person id autonumber
-LastName
-FirstName
other person information

tblDailyTasks
-TaskID_FK
-PersonID_FK
-TaskDate
 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
I don't know what "that" is which are you are requesting a yes/no to. Is it the 27 checkboxes=27 fields? If so, the answer is no. You can have 27 checkboxes that go into just 1 table with 1 Yes/No field as 27 records.

From you first post:



Because you are new, you don't understand that you do not need the results to go into 1 record. Like most beginners you've jumped in at the sexy part--forms. The real place to begin with databases is normalization (https://en.wikipedia.org/wiki/Database_normalization). That is the process of setting up your tables and fields. Your data dictates how you structure your table and fields, not anything else.

I suggest you take a step back, read up on normalization, try and apply it to your data without regard of how you want your forms to look, then set up the Relationship Tool in access, expand it so all fields in all tables show, take a screnshot and post it back here. Then we can walk you through making the tables/fields correct. From there you can move on to making reports (that's step 2) the last step is making forms.

I totally hear what you're saying and I did read up on 1NF, 2NF, 3NF, but when I tried to put that into practice it all made sense until I got to the form part. To explain, I knew that I'd need 3 tables: One that held all the tasks (in one field), a "completed" table (a way to associate all the completed tasks from the other table with a single record -- in this case a date), and a junction table to join them. The problem occurred at the form level -- there was no way to have the 27 checkboxes represented in the form if they were mere rows of records in a single field -- the access form only seems to accept fields, and since there was only one, there was literally no way to get anything but a single "task" textbox/checkbox onto the form. I could not figure out how to represent each of the 27 tasks listed in the field on the form in any way so they could accept inputs.
 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
For your table design, my guess is a junction table that stores

TblDailyTasks
PersonID_FK ' relates back to the person table
TaskID_FK ' relates back to the task list
TaskDate ' date of the tasks.

With this structure you can get all kinds of things
' total tasks for each person by date
' total tasks for each person per date by mini, plus, and elite
. total tasks over a give period
' count of specific tasks over a time period
etc.

So you should have tables something like.
tblTasks
-TaskID_PK 'autonumber
-TaskType ' mini, plus, elite
-TaskSelected 'if going my route

tblPeople
-PersonID_PK ' person id autonumber
-LastName
-FirstName
other person information

tblDailyTasks
-TaskID_FK
-PersonID_FK
-TaskDate

Thank you so much for taking the time and effort to write all of that up! Here's what I have:

tblCompletionDates
-CompletionDateID
-CompletionDate


TblTask_CompletionDates
-taskID (PK)
-CompletionID (PK)

TblTasks
-taskID (PK)
-Task


TblLevels (not relevant to the problem)
tblHabitCategories (not relevant to the problem)
 

Bettany

New member
Local time
Today, 09:33
Joined
Apr 13, 2020
Messages
26
For your table design, my guess is a junction table that stores

TblDailyTasks
PersonID_FK ' relates back to the person table
TaskID_FK ' relates back to the task list
TaskDate ' date of the tasks.

With this structure you can get all kinds of things
' total tasks for each person by date
' total tasks for each person per date by mini, plus, and elite
. total tasks over a give period
' count of specific tasks over a time period
etc.

So you should have tables something like.
tblTasks
-TaskID_PK 'autonumber
-TaskType ' mini, plus, elite
-TaskSelected 'if going my route

tblPeople
-PersonID_PK ' person id autonumber
-LastName
-FirstName
other person information

tblDailyTasks
-TaskID_FK
-PersonID_FK
-TaskDate

Looks like the problem is going to be the form. :( Regardless of how the table data is structured, representing it on the form is going to be the hiccup, since one checkbox apparently equals one field and there's no way around that.
 

Users who are viewing this thread

Top Bottom