I have two Yes/No fields in a table (ContractorInstall, andInHouseInstall)

[Tara Metzger]

I have two Yes/No fields in a table (ContractorInstall and InHouseInstall) What I would like to happen in the form is as follows: Since one of the boxes must be checked (yes), the checked box will display either the contractor or the in-house survey depending upon which one is "yes". If it's a contractor install, the contractor survey needs to be displayed. The inhouse survey is to be hidden either from view or seen but blocked from data entry, and vice versa. My question is this. Am I better trying to set this up in the form itself or should I use queries to help?
Thank you,
Tara


Submitted via EggHeadCafe - Software Developer Portal of Choice
Silverlight, WCF, Security And Things You Might Not Know
http://www.eggheadcafe.com/tutorial...882-f6ac833492d5/silverlight-wcf-securit.aspx

 

[Jeanette Cunningham]

Setting up Yes/No fields for this is better avoided.
Create a table for InstallType.
tblInstallType
InstallTypeID - autonumber- Primary Key
InstallTypeName - description of the type

In the main table, put InstallTypeID as one of the fields.
In the relationships window with both the main table and the install type
table,
select InstallTypeID from tblInstallType and drag it onto the matching field
in the main table.

In the data entry screen you will have a field for the InstallTypeID. Make
this a combo.
The combo's row source will be a query based on tblInstallType.

So on the data entry screen, in the after update of the combo you can go
If Me.NameOfCombo = 1 Then
'contractor, code to display the contractor survey
Else
'code to display the in house survey
End If

The above assumes that 1 is the InstallTypeID for a contractor install.
You will use the value from your InstallType table.

You can also change the above slightly and make the InstallTypeID in the
table a text field and make it the primary key.
Doing it this way means you only need one field in the table and the values
for InstallTypeID would be Contractor, and the next row would be Inhouse.
It's up to you which way you do it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

I have two Yes/No fields in a table (ContractorInstall and InHouseInstall)

That's not an ideal design: I presume that if one is checked the other should
not be? You wouldn't have a record where neither was checked, or both were
checked, would you?

If that is the case, I'd suggest using *one* field, Installer; it would be
convenient to have this a Number/Integer field, and use an Option Group
control on the form. This would let you store a 1 for Contractor, 2 for
InHouse (and perhaps in the future 3 for Preinstalled and 4 for Customer
Installed, if those options should come to pass); the Option Group would have
two (or more) radio buttons with labels.

 

[KenSheridan via AccessMonster.com]

Tara:

I'll come back to the question of having a single 'InstallType' column later,
but firstly I'd like to examine just how the survey data is stored. I'm
assuming the form is based on a 'Jobs' table or similar, so lets assume it
has a primary key JobID. What I'd envisage for the surveys is a Surveys
table which includes a foreign key JobID column to relate it to the Jobs
table. This table would have a primary key SurveyID and columns for those
attributes which are common to both types of survey, such as SurveyDate. As
the two survey types presumably each have attributes which are distinct for
each I'd then envisage two further tables, ContractorInstallSurvey and
InHouseInstallSurvey say, each of which will again have a primary key
SurveyID. The important point here is that the SurveyID columns in
ContractorInstallSurvey and InHouseInstallSurvey are also foreign keys
referencing the primary key of Surveys, i.e. the relationship type is one-to-
one. Note that the SurveyID columns in ContractorInstallSurvey and
InHouseInstallSurvey cannot be autonumbers, but that in Surveys can be.

For data entry I'd envisage a form based on the Jobs table and within it two
subforms, one for each survey type. The ContractorInstall survey subform
would be based on a query which joins Surveys and ContractorInstallSurvey on
SurveyID, the InHouseInstall survey subform on a query which joins Surveys
and InHouseInstallSurvey on SurveyID. Both would be linked to the parent
form on JobID.

You could either superimpose the two subforms and show the relevant one.
With your current two fields you'd put the following code in the AfterUpdate
event procedures of both check boxes bound to these fields, and in the form's
Current event procedure:

Me.sfcContractorSurvey.Visible = Nz(Me.ContractorInstall,False)
Me.sfcInHouseInstall.Visible = Nz(Me.InHouseInstall,False)

where sfcContractorSurvey and sfcInHouseInstall are the names of the subform
controls in the main form, i.e the controls which house the subforms.

The reason for the Nz functions is that when you navigate to a new record a
bound check box is Null until you start to enter data.

The other option would be to have both subform's visible side by side or one
above the other, or on separate pages of a tab control and enable/disable
them as appropriate. In this case the code in the event procedures would be:

Me.sfcContractorSurvey.Enabled = Nz(Me.ContractorInstall,False)
Me.sfcInHouseInstall.Enabled = Nz(Me.InHouseInstall,False)

Coming back to the issue of a single 'InstallType' field rather than your two
Boolean (Yes/No) fields I'd agree completely with Jeanette and John on this.
What you are doing at present is what's known as 'encoding data as column
headings. A fundamental principle of the database relational model (the
Information Principle) is that data is stored as values at column positions
in rows in tables and in no other way.'

What I've said above about the survey tables and subforms would still apply,
however. You'd just amend the code along these lines:

Me.sfcContractorsurvey.Enabled = (Nz(Me.InstallType,"") = "Contractor")
Me.sfcInHouseInstall.Enabled = (Nz(Me.InstallType,"") = "InHouse")

Ken Sheridan
Stafford, England

 

[Tara Metzger]

Jeanette, thank you for your help. I thought of doing it the way you suggested initially, but talked myself out of it. Guess I should have trusted my instincts on this one. I will go with what you suggest. It looks like a much better way of doing it.
Thank you,
Tara



Jeanette Cunningham wrote:

Setting up Yes/No fields for this is better avoided.
27-Jan-10

Setting up Yes/No fields for this is better avoided
Create a table for InstallType
tblInstallTyp
InstallTypeID - autonumber- Primary Ke
InstallTypeName - description of the typ

In the main table, put InstallTypeID as one of the fields
In the relationships window with both the main table and the install typ
table
select InstallTypeID from tblInstallType and drag it onto the matching fiel
in the main table

In the data entry screen you will have a field for the InstallTypeID. Mak
this a combo
The combo's row source will be a query based on tblInstallType

So on the data entry screen, in the after update of the combo you can g
If Me.NameOfCombo = 1 The
'contractor, code to display the contractor surve
Els
'code to display the in house surve
End I

The above assumes that 1 is the InstallTypeID for a contractor install
You will use the value from your InstallType table

You can also change the above slightly and make the InstallTypeID in th
table a text field and make it the primary key
Doing it this way means you only need one field in the table and the value
for InstallTypeID would be Contractor, and the next row would be Inhouse
it is up to you which way you do it

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Content Director Test Article
http://www.eggheadcafe.com/tutorial...f-e92ac7a4593f/content-director-test-art.aspx

 

[Tara Metzger]

John & Ken,

I didn't see your posts until just now. Not sure what happened, however they did not come through for some reason. All 3 of you (including Jeanette) agree that it's a bad idea to do what I was going to with the multiple yes/no fields. Looking back on it, your right, not the smartest thing I've come up with. That having been said, I am very intrigued by both of your responses to my question. I'm going to have to look into what you are saying, but I think I understand what you gentlemen are getting at and will play around with the database to see what works best. For now I can't thank all of you enough for your wise council. It's been a huge help!
Tara



KenSheridan via AccessMonster.com wrote:

Tara:I will come back to the question of having a single 'InstallType' column
27-Jan-10

Tara

I will come back to the question of having a single 'InstallType' column later
but firstly I'd like to examine just how the survey data is stored. I a
assuming the form is based on a 'Jobs' table or similar, so lets assume i
has a primary key JobID. What I'd envisage for the surveys is a Survey
table which includes a foreign key JobID column to relate it to the Job
table. This table would have a primary key SurveyID and columns for thos
attributes which are common to both types of survey, such as SurveyDate. A
the two survey types presumably each have attributes which are distinct fo
each I'd then envisage two further tables, ContractorInstallSurvey an
InHouseInstallSurvey say, each of which will again have a primary ke
SurveyID. The important point here is that the SurveyID columns i
ContractorInstallSurvey and InHouseInstallSurvey are also foreign key
referencing the primary key of Surveys, i.e. the relationship type is one-to
one. Note that the SurveyID columns in ContractorInstallSurvey an
InHouseInstallSurvey cannot be autonumbers, but that in Surveys can be

For data entry I'd envisage a form based on the Jobs table and within it tw
subforms, one for each survey type. The ContractorInstall survey subfor
would be based on a query which joins Surveys and ContractorInstallSurvey o
SurveyID, the InHouseInstall survey subform on a query which joins Survey
and InHouseInstallSurvey on SurveyID. Both would be linked to the paren
form on JobID

You could either superimpose the two subforms and show the relevant one
With your current two fields you would put the following code in the AfterUpdat
event procedures of both check boxes bound to these fields, and in the form'
Current event procedure

Me.sfcContractorSurvey.Visible = Nz(Me.ContractorInstall,False
Me.sfcInHouseInstall.Visible = Nz(Me.InHouseInstall,False

where sfcContractorSurvey and sfcInHouseInstall are the names of the subfor
controls in the main form, i.e the controls which house the subforms

The reason for the Nz functions is that when you navigate to a new record
bound check box is Null until you start to enter data

The other option would be to have both subform's visible side by side or on
above the other, or on separate pages of a tab control and enable/disabl
them as appropriate. In this case the code in the event procedures would be

Me.sfcContractorSurvey.Enabled = Nz(Me.ContractorInstall,False
Me.sfcInHouseInstall.Enabled = Nz(Me.InHouseInstall,False

Coming back to the issue of a single 'InstallType' field rather than your tw
Boolean (Yes/No) fields I'd agree completely with Jeanette and John on this
What you are doing at present is what is known as 'encoding data as colum
headings. A fundamental principle of the database relational model (th
Information Principle) is that data is stored as values at column position
in rows in tables and in no other way.

What I have said above about the survey tables and subforms would still apply
however. You'd just amend the code along these lines

Me.sfcContractorsurvey.Enabled = (Nz(Me.InstallType,"") = "Contractor"
Me.sfcInHouseInstall.Enabled = (Nz(Me.InstallType,"") = "InHouse"

Ken Sherida
Stafford, Englan

Tara Metzger wrote

-



Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
C#.NET and COM Interop
http://www.eggheadcafe.com/tutorial...c-8610-bc2550430968/cnet-and-com-interop.aspx