Deal with more than 50 checkboxex on the same form

[jinalpatel]

I am fixing already existing database about coastal grants. I have several tabs for data entry on main form like Main, Contacts,Budget, Project Goals,Performance Reports..etc.Total 10 tabs on a main form.

On Project Goals tab I have a command button called "Open Project Deliverable". When you click on this command it will open you a form or rather say a subform which has more than 50 checkboxex and associated textboxes.

Right now I have created one table which has all those more than 100 fields one chekbox and associated textbox. All the data is stored there.

On another tab I want a button which on click shows the tabular format of what a user has selected and entered a value for.

Please help I am clueless. I have tried several ways around.

My main table is called MainDataEntry and Primary key is Grant Index.
One Grant Index can have multiple deliverables.
User can be able to edit all of them if he wants to.
These are several conditions on it.

Let me know if anybody have any question regarding description of problem.
Thanks for help in advance

[ChipR]

What is the structure of the table that holds the data about checkboxes and text fields? It seems to me that one record in this table should include a checkbox value and a textbox value, and there will be 50 of these entries all with the same ID or whatever it is they are a part of.

Say your table has:
CustomerID, chkField, txtField

Then you can base the tabular format you want on something like:

SELECT * FROM MyTable WHERE chkField = -1 AND _
 & CustomerID = 'Customer1'

Since -1 = True, and 0 = False, that will display only the values that are checked.

If that's not what you want, you'll have to explain a little more.

[jinalpatel]

I thought to do something like that and also tried to put it into effort. But than I confused how do I uniquely identify that set of 100 checkbox and textbox.

Here is what I did.
I created two tables

1)tblProjectDeliverables

GrantIndex Number
DeliverableId AutoNumber

2)tblDeliverables

PerformanceMesure Text
CheckBoxValue Yes/No
TextBoxValue Number
GoalValue Number

PerformaceMeasure has 50 different kinds of data to be entered like Public Access, Boating Access, Fees, Management etc( like that 50 diff. types)

I have to connect these two tables to the main table that is called "Main Grant Information". Here, GrantIndex is a primary key and it is autonumber.

Please guide be little bit about how to make them realted. How should I establish relationship between them?
One "GrantIndex" can have many Project Deliverables..User requirement.

Thanks for helping

[ChipR]

Each GrantIndex can have many Project Deliverables.

Ok, you just need to specify in your tblDeliverables which GrantIndex this PerformanceMeasure is for. Add the GrantIndex field to your tblDeliverables and delete the tblProjectDeliverables, that's how you make them related. You don't have to, but you can make a primary key including GrantIndex and PerformanceMeasure. I don't see any reason you would have the same PerformanceMeasure twice for one GrantIndex.

Now you can select records from tblDeliverables with criteria based on the GrantIndex, and there should be up to 50 records per GrantIndex, right? Make sure the GrantIndex field is indexed for perfomance reasons, if you don't use a primary key.

[jinalpatel]

Now you can select records from tblDeliverables with criteria based on the GrantIndex, and there should be up to 50 records per GrantIndex, right? Make sure the GrantIndex field is indexed for perfomance reasons, if you don't use a primary key.

Here little bit nore clarification is needed I think.

One GrantIndex can have many Project deliverables. That is right, but
One deliverable means 50 or less Performance measures.
Meaning, One granIndex has many Deliverables and One Deliverable has 50 or less performance measures values(Chkbox+Textbox)

Also, on other tab I have to provide the Progress report. So I want a tabular format something like this

GrantIndex DeliverableId
[+]
Performance Measure ChkBoxValue TextboxValue Goal


By deleting tblProjectDeliverables table I cannot establish link between deliverables and GrantIndex.


Thanks once again

[ChipR]

Ah, I missed the concept of the deliverables and the relationship, sorry.

So, instead of GrantIndex in your tblDeliverables, you need the DeliverableID, and you keep the tblProjectDeliverables. So those 2 tables will be related by the DeliverableID, and if you wanted the tblDeliverables data on a form with the associated GrantIndex, you'd use a query that inner joins the two tables on the DeliverableID field.

It sounds like you are close to what you want already. I'm not exactly clear what you mean by the report format, but if you make that query that joins the data, you can set the report to group GrantIndex and DeliverableID, then put them in a header for their section, or put them in the detail and set them to not repeat duplicates.

[FishVal]

Hello, jinalpatel.

When you work out your tables you probably could use method described here to render those 50 options on subform.

[jinalpatel]

Still I am not able to fix it.
Now I have two tables like

1)tblProjectDeliverables
GrantIndex Number
DeliverableId Autonumber

2)tblDeliverables
DeliverableId (Number)
PerformanceMeasure (text)
ChkboxValue (number)
TextboxValue (number)
GoalValue (number)

And my Main table which has GrantIndex as a primary key

Now what do you think about their relationship.How should I connect them, provided all the criterions in previous posts.

OR do you think I need third table called Performance measure, Which has following fields

PerformanceMeasureId (autonumber)
PerformanceMesureDescription(text)
ChkBoxValue(yes/no)
TextboxValue(number)
GoalValue(number)

And with that I will need to change the tblDeliverables like the following
2)tblDeliverables
DeliverableId (Number)
PerformanceMeasureId (Nnumber)

[ChipR]

I don't see any reason for a third table. What's the problem you're having with the two tables you have now? All the information is there, it's just a matter of how you want to present it.
If you want to show all the performance measures in a subform, make it's data source a query which is a join of the two tables on DeliverableID, and link its child fields GrantIndex and DeliverableID to master fields selected or filled in on the main form. Know what I mean?

[jinalpatel]

Ok, I have figured this thing. I am not 100% sure but I think it will work.

Now I have two tables

1)tblDeliverables
GrantIndex (number)
DeliverableId (number)
PerformanceMeasure (Text)
ChkboxValue (yes/no)
TextboxValue (number)
GoalValue (number)

2) tblProjDelUser (this is the table opened from my main data entry form to enter the performance measure data and their values)

PerformanceMeasure (Text) (PK)
ChkboxValue (yes/no)
TextboxValue (number)
GoalValue (Number)

I have made one Append query that appends all the data entered by user in the table "tblProjDelUser", to tblDeliverables.
That way I got all my data in the table called tblDeliverable.
So GrantIndex + DeliverableId togather uniquely identify the record on that table. I also can write a query to show it in a report format.

My questions:

1) My GrantIndex field is autonumber and it is automatically incremented when user starts to enter data on first tab.
How do I populate this field in "tblDeliverables"?
I need to show tblProjDelUser table for data entry on 6th tab of main form.

2) I would like same DeliverablId in "tblDeliverables" for bunch of Performance Mesure. How can I do that?
Meaning When user hit append records command button on 6th tab, all the records that are appened in tblDeliverables should have same DeliverableId.


Thanks for helping me and discussing with me Chip R.