G'day from Australia,
I'm hoping some bright spark may be able to help me with this one. I'm
sure that it can be done, I've just hit a wall with it. So I'm opening
it up.
I'm storing student competencies for a given number of performance
criteria (which are stored in a separate reference table).
Students are rated competent or not (yes/no field) on up to 5
assessments - Task, Observation, Project, Practical Test, Exam for
each performance criteria.
But for any given performance criteria, an assessment may not be used
to assess competency so I don't want it to display on the data entry
form (with two subforms - student and performance criteria)
Example:
Competencies table:
StudentID(PK/FK), PerformanceCrit eriaID(PK/FK), Task (Yes/No), Project
(Yes/No), Obs (Yes/No), PracTest (Yes/No), Exam (Yes/No)
Sample Data in the table:
StudentID, PerformanceCrit eriaID, Task, Project, Obs, PracTest, Exam
5432, 16, -1, -1, -1, 0, -1
5432, 23, -1, -1, -1, -1, 0
In this example, the field PracTest for student 5432 contains a zero
(not because the student isn't competent on Performance Criteria 16,
but because a PracTest is not used to assess this competency). I
don't want a user to accidentally tick a checkbox for this performance
criteria for a PracTest if a PracTest is not used to assess the
Performance Criteria.
But student 5432 could have a zero recorded for the Exam assessment
on Performance Criteria 23 because they do not have competency and the
staff member has not ticked the checkbox (which is displayed in this
instance)
What I considered was a separate table that determined (using yes/no
fields) whether an assessment was to be displayed or not (for that
PerformnceCrite riaID), then somehow query this table when the record
was being displayed on the data entry form (which is displaying
continuous forms) and use the visible property for the field control.
It would be cool if there was a datatype that handled three values -
yes/no/not used.
Does anyone have any bright ideas that I could progress with?
I would be most grateful for some inkling on how to proceed.
I have a database if anyone needs a copy to see where I'm at so far.
Probably not wise for it to be posted with this message. So I assume
if anyone needs it they will tell me how to send it to them.
Cheers
David :-)
Brisbane
Australia 2 4784
Hi David.
Your conclusion suggests that you need a field that can contain 3 states:
Yes/No/Not used. You can do that in Access is you use a Number field (size
Integer), and interface it with a check box. Set the Triple State property
of the check box to Yes, and it now has 3 states: Yes (-1), No (0), and Not
Applicable (Null.) If you are using Windows XP, there may not be any visual
distinction between No and Null unless you uncheck the option under:
Tools | Options | Forms/Reports | Use Themed
But there's a more basic issue here: the data structure. One performance
criterion can have multiple assessments. This should be handled as 2 tables
with a one-to-many relation instead of as multiple check boxes in the one
table. (In typical competency-based training, an assessment may assess
multiple elements/outcomes, each with multiple criteria, so the structure
may actually be a bit more involve than just that.) Once you get the
structure right, you can then teach the database which actual
critiera/assessments are matched, and therefore which ones apply when an
actual assessment is submitted.
Once the data structure is correct, you can then worry about how to
interface it. That might end up being a form with a subform to handle the
related records.
If you want to continue with the approach you already have instead, the
triple state check box will allow you to use Null for N/A. Unlike zeros, the
nulls don't affect averages. And you could run some checks in
Form_BeforeUpda te to ensure that there's no 0 or -1 in a field that should
be null. While tha'ts possible, it is not structurally correct, and so it is
harder to write, maintain, and query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"David - Australia" wrote in message
news:rg******** *************** *********@4ax.c om...
G'day from Australia,
I'm hoping some bright spark may be able to help me with this one. I'm
sure that it can be done, I've just hit a wall with it. So I'm opening
it up.
I'm storing student competencies for a given number of performance
criteria (which are stored in a separate reference table).
Students are rated competent or not (yes/no field) on up to 5
assessments - Task, Observation, Project, Practical Test, Exam for
each performance criteria.
But for any given performance criteria, an assessment may not be used
to assess competency so I don't want it to display on the data entry
form (with two subforms - student and performance criteria)
Example:
Competencies table:
StudentID(PK/FK), PerformanceCrit eriaID(PK/FK), Task (Yes/No), Project
(Yes/No), Obs (Yes/No), PracTest (Yes/No), Exam (Yes/No)
Sample Data in the table:
StudentID, PerformanceCrit eriaID, Task, Project, Obs, PracTest, Exam
5432, 16, -1, -1, -1, 0, -1
5432, 23, -1, -1, -1, -1, 0
In this example, the field PracTest for student 5432 contains a zero
(not because the student isn't competent on Performance Criteria 16,
but because a PracTest is not used to assess this competency). I
don't want a user to accidentally tick a checkbox for this performance
criteria for a PracTest if a PracTest is not used to assess the
Performance Criteria.
But student 5432 could have a zero recorded for the Exam assessment
on Performance Criteria 23 because they do not have competency and the
staff member has not ticked the checkbox (which is displayed in this
instance)
What I considered was a separate table that determined (using yes/no
fields) whether an assessment was to be displayed or not (for that
PerformnceCrite riaID), then somehow query this table when the record
was being displayed on the data entry form (which is displaying
continuous forms) and use the visible property for the field control.
It would be cool if there was a datatype that handled three values -
yes/no/not used.
Does anyone have any bright ideas that I could progress with?
I would be most grateful for some inkling on how to proceed.
I have a database if anyone needs a copy to see where I'm at so far.
Probably not wise for it to be posted with this message. So I assume
if anyone needs it they will tell me how to send it to them.
Cheers
David :-)
Brisbane
Australia
Wow that was fast Allen,
I see what you mean about my structure. I might look more closely at
your suggestion since it appears to be more easily updateable, and if
this works for my subject, others may want to use it. We're currrently
using spreadsheets which are a severe nuisance.
For ease of data entry I wanted checkboxes to be used by staff, but I
think that's still achievable using your suggestion.
Thank you for you help.
David
Brisbane
On Sun, 11 Feb 2007 19:49:02 +0900, "Allen Browne"
<Al*********@Se eSig.Invalidwro te:
>Hi David.
Your conclusion suggests that you need a field that can contain 3 states: Yes/No/Not used. You can do that in Access is you use a Number field (size Integer), and interface it with a check box. Set the Triple State property of the check box to Yes, and it now has 3 states: Yes (-1), No (0), and Not Applicable (Null.) If you are using Windows XP, there may not be any visual distinction between No and Null unless you uncheck the option under:
Tools | Options | Forms/Reports | Use Themed
But there's a more basic issue here: the data structure. One performance criterion can have multiple assessments. This should be handled as 2 tables with a one-to-many relation instead of as multiple check boxes in the one table. (In typical competency-based training, an assessment may assess multiple elements/outcomes, each with multiple criteria, so the structure may actually be a bit more involve than just that.) Once you get the structure right, you can then teach the database which actual critiera/assessments are matched, and therefore which ones apply when an actual assessment is submitted.
Once the data structure is correct, you can then worry about how to interface it. That might end up being a form with a subform to handle the related records.
If you want to continue with the approach you already have instead, the triple state check box will allow you to use Null for N/A. Unlike zeros, the nulls don't affect averages. And you could run some checks in Form_BeforeUpd ate to ensure that there's no 0 or -1 in a field that should be null. While tha'ts possible, it is not structurally correct, and so it is harder to write, maintain, and query.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: A.J.M. van Rijthoven |
last post by:
I have a table instrumenten (INSID Instrumentname, CATID), a table
Categorie (CATID, Categorydescription), Netten (NETID, description of
net) and a table (kpltblinstrument) that links the instruments to a
specific net (NETID, INSID, amount).
I have a form which displays the name of the net and it holds a the
subform from table lnknetins. I...
|
by: N. Graves |
last post by:
Hello... thank you for your time.
I have a form that has a List box of equipotent records and a sub form
that will show the data of the equipment select from the list box.
Is it possible to make a change in the name field in the details, then
refresh the list box with new name?
Please help and thanks!
|
by: Lyn |
last post by:
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the data in each record, which
includes the ID of the father and the mother (who also have records in the
table). One record per form.
I have a Tab...
|
by: sparks |
last post by:
After trying a combo box to do this.
its tied to a lookup table
name value
yes 1
no 0
they wanted to be able to type in a 1 but display a yes in the field.
and store a 1 in the table.
so I set the combo box to
|
by: jean.ulrich |
last post by:
Hi
I have a form that is not related with a table neither a query (unbound
form)
On this form I have a text field where the user can put a text or a
number
As I dont want to create a table just for that field, is it possible
that when someone write someting in the field, the text don't disapear
when he close the form and open it again
| |
by: alexsg |
last post by:
A little while ago Ron P kindly responded to my inquiry on how to copy
the first line of memo field into a text field, using a query:
left(,instr(1,,vbCrLf)-1)
This is great, but I would like to do this on a form, so that the
person entering the memo can use a button to paste the 1st line to a
title (text) field. I'm having trouble...
|
by: sellcraig |
last post by:
Microsoft access
2 tables
table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data main"
Goal-
the data in "code" field in needs to be inserted into a standard web
address in the table (the filed name is link) in ddw1
Example address ---
|
by: AA Arens |
last post by:
Hi I do have a products table and products-parts table in my Access
2003 database and log all services into a form.
I do have at least the following two combo boxes on my form:
- Choose Product where as the Row Source (See properties):
SELECT tblProducts.ProductName, tblProducts.ProductName FROM
tblProducts ORDER BY ProductName;
|
by: MLH |
last post by:
I have a form used as a subform on frmCreateInvoice.
It is displayed in datasheet view in the subform control.
It displays a few text fields, a boolean field (shown as a
checkbox control) and a numeric field. The subform is
fed by a query. When I open the main form, I often click
the checkbox control in the records listed in the subform...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |