completed value of a check box

[stephvliet]

Good morning,

I have an access data base which I have attached, There are Sub systems and pre requisite sub systems. The pre requisite Sub Systems have a tick next to them. There is another tick box to say whether that sub system is complete or not. I want a formulae that lets me know if the Pre Requisite Sub Systems are complete and the non pre requisite sub systems are complete?

Hopefully someone can help.

 

[Ranman256]

A query OR Dcount() :
1. count the # of sub records (a subform textbox to count)
and
2. count both, [Pre Requisite Sub Systems] = complete and [NON PreRequisite]= complete

if both #s equal then all records are complete

 

[stephvliet]

Thank you for your response but I am afraid I don't quite follow?

 

[stephvliet]

Good morning,

I have an access data base which I have attached, There are Sub systems and pre requisite sub systems. The pre requisite Sub Systems have a tick next to them. There is another tick box to say whether that sub system is complete or not. I want a formulae that lets me know if the Pre Requisite Sub Systems are complete and the non pre requisite sub systems (the Sub Systems with the tick next to them) are complete?

Hopefully someone can help.

 

[Ranman256]

you have a sub form with data in it?
make a text box in the sub form FOOTER, that has COUNT([records])
in another box DCOUNT("*", "qsDataChecked") to count the checked items.
if the 2 match then everything is checked.

 

[plog]

I can't directly help you with your issue because I don't understand your data. I do understand how to layout tables and I think my advice my indirectly help your issue.

Your database isn't properly structured. You need at least 1 more table to hold SubSystem information. All data that is about a SubSystem should go into a table just of that data:

SubSystems
SubSystemID, autonumber primary key
SubSystemNo, text
SubSystemDesc, text
*SubSystemPR, Yes/No

*Since I don't understand your data, I'm not certain if your current PRSubSystem field should be in this table or not. Looking at your data, I believe it should be.

Now here's why you need that new table. Filter your table to SubSystemNo=3000-02-B and look at the values in the SubSystemDescription field. You have 3 unique SubSystemDescription values for it (Central Control Room, CCR Equipment & Central Control Room Equipment). Which is it? Shouldn't they all be the same description?

Also, You don't need a Complete checkbox and a CompleteDate field. You simply use logic--if CompleteDate is blank then its not complete, if it has a date it is complete. Then a best practice piece: only use alphanumeric characters and underscores in field/table names. When you add spaces or any special characters it makes writing queries and code down the road just that much more difficult.

This process of structuring your tables is called normalization (https://en.wikipedia.org/wiki/Data_normalization). Give that link a read and set your tables up properly and hopefully this issue you have now dissappears. If not, post back your new table structure and then a plain english (no database jargon) explanation of the real life process your database is to model.

 

[stephvliet]

I do not have a sub form with data in it!