Multiple Selections per Record on Form

[philipscodes]

Dear Experts,

I am in the process of developing a small Access utility for a company. It is an audit observation file.
Above shown tables have been made for the purpose.

tbl_AuditPersonnel contains the auditor details.
tbl_Lookup1_AuditCategory contains the main categories of questions - 4 categories.
tbl_Lookup2_AuditTest contains the different type of questions, which come under any of the above categories - types can range up to 15 for each category.
tbl_Lookup3_AuditQuestions contains the actual audit questions, which come under any of the above different types - questions can range up to 50 for each type.
tbl_AuditResults contains the data entry that the auditors make - all the above information are to be stored using the foreign keys.

A data entry form should let the auditors choose their ID and then first the Category followed by the Type of questions, relevant to the category chosen and finally they should be able to choose the relevant Questions from the chosen type - here an implementation of cascading combobox could be easily implemented.

However, the complication arises where the requirement is that the auditor should be able to choose more then one type and more than one question per record.
Also, there has to be an 'observation' field per question to be saved in the same record.

I tried my hands at check box - 'multivalue field combo box' technique too. It works in saving/retrieving the Types but wouldn't query the relevant Questions.
Also, I have no clue on how to save an observation for each question in the same record.

Any guidance on how I should go about this - anything new that I haven't tried, possibly without involving VBA would be much appreciated.
Not that I am not comfortable with VBA, I would rather make the maximum best use of the available techniques in Access 2010 before I succumb to VBA.



Any help is much appreciated.

Warm Regards,
Philip

[ArviLaanemets]

Usual design for such apps is Form - Subform.

1. You have a main form (bound or unbound). P.e. an Unbound form with combo box to select auditor. When bound form is used, then simplest is to use a single form. In case you want to use continuous main form, then the design will be more complicated. Subform(s) are usually designed as continuous forms;
2. You insert one or several subforms into main form (In design view, you drag a form from Objects Pane into main form. When you want to have several subforms on main form, you can insert a tab control and place subforms on tab sheets.);
3. (Optional, but recommended.) You rename subform control(s) differently from form(s), which is/are Source(s) for subform(s). P.e. you dragged a form fMyForm into main form - you rename the subform control as sfMyForm - it avoids some confusion later;
4. When you added subform into unbound form, or when primary and foreign key field names of source tables differ, you have to set the link between mother and child form(s) manually. Anyway, check subform's LinkMasterFields and LinkChildFields properties. Those must be like
a) LinkMasterFields - "PrimaryKey1[; PrimaryKey2, ...]" or "MainFormControl1[, MainFormControl2, ...]"
b) LinkChildFields - "ForeignKey1[, ForeignKey2, ...]"
My advice is - you hide the key field controls in child form from user (set them invisible).

Now, whenever you select a new record in main form (or change the value of unbound control, when such is used for linking), matching set of records linked with this key value is displayed in subform(s). And whenever you add a new record into subform, the value(s) of linked field(s)/control(s) is automatically added into linked field control(s) in subform(s).

I.e. Auditor adds p.e. multiple questions as different rows into proper subform.

[philipscodes]

The preference is using a single form bound to the tbl_AuditResults.

When using a subform, which I had tried as well, the auditor would not be able to choose multiple Types and multiple Questions - or am I missing something there, kindly advise.

Also, how should I save multiple observations per question?

Warm Regards,
Philip

[ArviLaanemets]

You are missing something for sure! But currently I have some problems with IE - it hangs all times. I had to save my previous post halfway, and I can't edit it anymore!

[philipscodes]

Oh my, that's a downer...hope you could help on this soon.


By the way, we now use Access 2016 with Windows 10 - thought I should update for advise on any latest features.

Plus, I have attached the Relationships as well, in case something needs to be changed here.

[aytee111]

"A data entry form should let the auditors choose their ID and then first the Category followed by the Type of questions, relevant to the category chosen and finally they should be able to choose the relevant Questions from the chosen type "

Main form - that will include all the selections mentioned above. BTW, the auditor does not need to choose their ID, in fact this is a dangerous method to use. Take their ID form their Windows logon - Environ("username") - when the database opens and do not let them change it.

For multi-selection, this can be achieved (either a combobox or a listbox). They select which test(s) and which question(s) and you append the relevant records to the results table. This table will be a subform linked to the main form and all the required information, including observation, will be available to the user.

[ArviLaanemets]

I edited my 1st post meanwhile.

You database table structure needs an upgrade definitely!
1. tbl_AuditPersonell is OK;
2. tbl_AuditCategory is also OK, except I'd remove this "Lookup" part - it doesn't add anything, and it makes commands/formulas much longer. (Btw. tbl_AuditPersonell doesn't have it, and this you use also to look up for auditor making audit.);
3. tbl_AuditTest (I'd name it simply tbl_Tests). Same as previous for "Lookup" part. And I am unclear, is this a table of predefined tests, from where one or several tests for an audit are selected, or it defines a group of certain questions for certain audit. Depending on answer the need for foreign keys must be decided;
4. tbl_AuditQuestions (I'd rename it simply tbl_Questions). Same as previous for "Lookup" part. Like previous, I am unclear, is this table of predefined questions, from where some for an audit are selected, or from where some for a predefined test are selected, or they are questions from certain audit (grouped into test). Again, depending on answer the need for foreign keys must be decided.
5. As you have marked last 3 tables as lookup tables, it looks like they are predefined ones. When this is so, you have a table tbl_Questions, where all questions are grouped as Tests from table tbl_Tests, which are grouped as Categories from table tbl_AuditCategories;
6. There is a problem with a tbl_AuditResults - you duplicate AuditPeresonellID, and Audit_Date for every AuditQuestionID.
6.1. You must ave a table tbl_Audit: AuditID, AuditDate, AuditPersonellID;
6.2. And you must have a table tbl_AuditResults: AuditResultID, AuditID, [AuditCategoryID], [AuditTestID], AuditQuestionID, Result. (Having AuditCategoryID and AuditTestID in table is optional - these are determined with AuditQuestionID, but having them makes limiting selections for auditQuestions simpler.);
7. To allow auditor enter several observations per question you must have an additional table tbl_AuditObservations: ObservationID, AuditQuestionID, ObservationText. In case one observation per question is enough, you can have the field ObservationText in tbl_AuditResults.

About forms:
I'd prefer an unbound main form with tab control, where on leftmost page a single subform based on tbl_Audits is situated, and on next pages subforms based on tbl_AuditPersonell, tbl_AuditCategories, tbl_Tests and tbl_Questions are situated. Above Audits subform (sfAudits further) on main form are combos to select auditor and audit category, and sfAudits is linked with those.
In form fAudits (the Source of sfAudits) is continous subform sfAuditResults (linked through AuditID and AuditCategoryID). In form fAuditResults (the source of subform), the user can register audit questions and audit results for those questions. When a new question is registered, user selects from combo audit test (combo selections must depend on audit category, and after that from another combo the question. After question is selected, the result of audit for this question can be entered. Again - the selections in questions combo must depend on previously selected test. NB! Row sources of combos in continous subform must be updated through VBA and the calculations must be made based on source table fields, not on form controls (form control value equals always with value of source field in current table row).

In case you want to allow several observations per question, you must have an hidden textbox in fAudits. The OnCurrent event of fAuditResults writes current AuditResultID into this textbox. And you add another continous subform sfAuditObservations into fAudits, based on tbl_AuditObservations, and link subform's [auditQuerstionID] with hidden text box in fAudits

[Micron]

Not sure if you what you mean when you refer to a category. This could be just the pertinent clauses of one particular standard (e.g. clause 4.2 of ISO 14001:2004) or you could mean to choose between ISO 14001:2004 or TS 16949 or any other standard the company is registered to. So I would take a pyramidal view of this, and if you want to build something that should work for any standard following your audit program, then allow for that now, otherwise you'll make something that can only deal with one standard. Even then, what happens if you migrate to an updated standard (e.g. 14001:2015)?

You have a rather complex task in front of you. I hope it's not your first db. Regardless of which approach, I don't have an issue with lookup tables for clause numbers or questions pertaining to a clause. Who selects the questions and inputs the answers to is another matter. This should not be selectable or editable, and should be restricted to auditors IHMO. I have other issues with your tables beyond what has been mentioned, such as connecting auditors to results. You have nothing for the audit as an entity (such as the scheduled date, the applicable standard, who the auditors will be, etc.) which exists far sooner than the results. You can't really begin to assemble a list of questions before the audit begins, which as you probably know, are based on prior audit results, again, because you have no audit table - just results.

Respectfully, I think you need to stop and first make sure you understand db Normalization concepts, especially from the point of view as to what constitutes an Entity and its Attributes. Then research examples of audit db's (and maybe join a forum related to the standard, where you might get suggestions or even db templates or entity diagrams). If you will be starting from scratch get big paper (or a whiteboard whose content is safe until you are done with it) so you can map things out, but it's crucial that you marry your knowledge of the standard with db design concepts. If either is a weak link, you won't build a strong chain. Consider also that your db may even need to be considered part of your document management system, which means not only will it need protection from prying eyes, but also from unauthorized editing.

I was going to go on about table structure, but it will make a long answer much longer, plus you're not even ready to start worrying about what to do about forms. Besides, I don't even know what standard the db is for. If you need links for db design concepts, I have some that I often post, but they're just a start if you're really a novice.
Hope some of that helps.

[philipscodes]

Thanks Micorn, that opened up a lot of knowledge avenues to tread by.
Though its not my first db, yes, I am quite a novice at it.

I agree that this is complicated and that it might be beyond me.
However, I am developing this db for a friend of mine - so I really do not have any direct access to company business requirements.
I am working with what I am told and probably my friend could continue to work up from there with the actual standards.
For me this is more or less like a weaning project.

I have taken your advise into consideration and will go further with it in gaining more knowledge.
In the meantime, I would still need to gather everything I can from all the experts in this forum and cook up a fairly decent outcome.

Warm Regards,
Philip

[philipscodes]

Thanks ArviLaanemets.

I have taken up your hints and implemented the same as far as I could understand it.
And yes I have almost cracked it. Just a bit more to go...

I have attached here the form and the tbl_Lookup3_AuditQuestion (with additional fields from the previous)

I am asked to develop the from in this manner.
That the auditors be able to choose all the questions that they require in one go.
So, I have used the listbox multi-select method.
When the auditor chooses the questions, it automatically populates a multi-line text box - so that that can have a better view of what was selected.

Now I need to figure out how to add new records to the subform based on the number of questions selected.
The AuditPersonnelID, Category, Test and selected Questions should programmatically be populated in each record - from the unbound comboboxes (Since they have already selected those).
The auditor then should simply be able to type out the Observation field alone, for each new record.

Now, the multi-line textbox uses the Question description (boundcolumn = 2 of cboQuestion control) to display.
However, I would require the boundcolumn = 1 (primary key of tbl_Lookup3_AuditQuestion) to populate the subform new records.

I have no clue on how to go about it from here.
Any clues or maybe a better workaround is much appreciated.

Thanks,
Philip

[philipscodes]

Thank you aytee111.

"Take their ID form their Windows logon - Environ("username") - when the database opens and do not let them change it."
Very good advise here - will implement the same.

"They select which test(s) and which question(s) and you append the relevant records to the results table. This table will be a subform linked to the main form and all the required information, including observation, will be available to the user."

I like this route, could you kindly expand a bit on this please...
I have included my form screen shot in another reply...could you have a look please.

Warm Regards,
Philip

[ArviLaanemets]

I haven't used multiselect listboxes much - probably you need some VBA code to populate the table tblAuditResults from listbox.

An alternative will be a table tbl_Selections: Selection, AuditQuestionID, where field Selection is a text ( a selection name). Auditors can predefine selections of questions. Now when registering the new audit, the auditor selects one selection in combo box, and clicks Import button. The button's OnClick event runs an Insert query like

"INSERT INTO tblAuditResults sel.AuditQuestionID FROM tbl_Selections sel WHERE Selection = '" & Me.cbbSelection & "' AND sel.AuditQuestionID NOT IN (SELECT qst.AuditCuestionID FROM tbl_AuditQuestions qst WHERE qst.AuditID = " & Me.AuditID & ")"

, which adds new questions into result table. Auditor can insert as many different selections as he likes - every question is added only once.

[aytee111]

It would help if you post a copy of your database - just the tables mentioned above with a few lines of data and the forms shown above.

[Micron]

agree with aytee111; might be beneficial to you.
I haven't detected who is getting audited here, so I'm wondering if you don't need anything for audit type (e.g. registration/compliance, 1st, 2nd or 3rd party). If this is for internal use only, how will audit nc's be dealt with?

[orange]

Philip,

For clarity could you step back and tell us in plain, simple English (no database jargon and don't assume we know anything about you or your environment) WHAT is the "business" for which you are building this database. Give us the what, who, where, when, how and how often overview in terms you would use to describe things to a 10 year old.