Teacher Training - Database to demonstrate I have met standards - help requested

[SamK]

Hello All,



About me: I am an Access novice. I am not formally trained but I have built a couple of functioning Access databases in the past.

My current problem: To pass a postgraduate teacher training programme I have to demonstrate that I have met a set of Qualified Teacher standards (QTS). To demonstrate this I am compiling a list of evidence (tasks performed). Each piece of evidence may be eligible as proof of meeting multiple standards.

I have set up an Evidence table in my database where I will log my completed tasks. To associate each piece of evidence with one or more standards, I have set up a series of binary checkboxes for each standard. It's essentially ticklist of what standards the evidence proves (see the file attached)

I will eventually want the database to report on various things, but the crucial element is getting it to report a list of relevant evidence by standard. So, for example, I would like the report to be able to select the required standard (Q1-33) and then list all the evidence relevant to that particular standard.

I've attached my embryonic database should anyone care to look.

I know I should go away and RTFM - and I would dearly love to as I'm very keen on Access - but my time must now be spent learning to teach! Any advice will be very much appreciated.

I hope my problem is clear and promise to answer any queries promptly. Thanks

Sam

[June7]

Multiple similar columns, such as the standards checkboxes, is an indicator of non-normalized data structure. This can cause issues. Say you want a report that groups records by standards met, this does not allow that. What if you add standards, have to modify the table. Table has 255 fields limit. Doubtful, but what if total standards and other attributes exceeded 255 fields?

Don't really understand need for the checkboxes. Shouldn't the table Marry Targets to Standards provide the info on met standard? Add a date field to this table called DateMet or something like that.

Also, the jointypes in the Relationship builder are all one-to-one. Table relationships not really clear to me. Does a subject have only one target and each target only one subject? Do targets have only one standard? The Marry table makes me thnk Targets have many standards, means jointype should be 'Include ALL records from 'Marry Targets...' Why does Cluster table have linking to Targets and QTS Standards (you did not include Cluster table in Relationships).

Also, with only 5 subjects, I wouldn't bother with a lookup table, especially since there is no related information and subject names are short. Could use a ValueList instead of Table/Query as RowSource. Save the actual subject name in the Evidence record, no need for table join to retrieve the name associated with key. Even if you want to retain the lookup table, coud use the name as key field. I know with so few subjects in lookup not likely to duplicate but still should set the Subject field to Indexed Yes(no duplicates). The Cluster lookup table should also be Indexed Yes(no duplicate) on the Cluster field. Targets TargetText field should also probably be Indexed Yes(no duplicates).

You have a field called Sort# in the QTS table. First, using special characters and spaces in names is not advised. Second, if you designed the QNumber with place holder zeros the Sort# field would not be needed. Example, Q4 as Q04 will sort properly with Q33. Design the number structure to handle the largest possible for the series. If you expect to stay less then 100, then the two places for digits will suffice. Play it safe and use Q004 and Q033.

[SamK]

June7 - thanks very much for your detailed input.

I've put some of your advice into use and I'll continue working on it tomorrow. I've removed the checkboxes and just married the pieces of evidence to the relevant QTS standards with a table.

I'm shaky on jointypes so I think I'll have to read up on these.

I've run some tentative reports on the newly structured database which seem to be displaying the data I require so I think the database will be useful once it has been tidied up and tweaked. (Obviously most of the data is false at the moment)

Any further thoughts much appreciated.

Sam