Insert comment if checkbox is ticked

[orange]

@Nokoff
I agree with June and knarfreppep -- normalized structures , query to populate form, no need for checkboxes --
will simplify data access and maintenance. The whole process will be less complicated.
Good luck.

[June7]

Of course, everything we suggest may make your mail merge more complicated. I have never used Access/Word mail merge, only assisted a few members. From what I recall, including multiple related dependent records is not simple and probably requires VBA to export 'tabular' data to a table in Word. If you must stay with current structure, it can probably be dealt with in a normal db with 'out of the box' creativity. Also, I have never built web database and don't know if it will be more difficult with non-normalized structure.

So why mail merge and not Access report?

A common philosophy is 'normalize until it hurts, denormalize until it works' or maybe vice versa.

[Nokoff]

Of course, everything we suggest may make your mail merge more complicated. I have never used Access/Word mail merge, only assisted a few members. From what I recall, including multiple related dependent records is not simple and probably requires VBA to export 'tabular' data to a table in Word. If you must stay with current structure, it can probably be dealt with in a normal db with 'out of the box' creativity. Also, I have never built web database and don't know if it will be more difficult with non-normalized structure.

So why mail merge and not Access report?

A common philosophy is 'normalize until it hurts, denormalize until it works' or maybe vice versa.

Yeah the mail merge is quite painful, it's very limited in it's functionality but unfortunately it's what management expects at this point.

I'm busy trying to redesign the db this morning with normalization in mind, however I know the mail merge might be a stumbling block again.

Tell me something, the stores that we go to might not have internet access, so I was wondering is it possible to capture the results offline, and then have access sync the changes to the sharepoint server?

[June7]

I think Access 2003 had some synchronization tools but apparently removed from later versions. Synchronizing databases is not a simple process, especially if autonumber is involved as PK/FK. Review https://www.accessforums.net/databas...ion-49776.html

[knarfreppep]

I'm busy trying to redesign the db this morning with normalization in mind, however I know the mail merge might be a stumbling block again.

Tell me something, the stores that we go to might not have internet access, so I was wondering is it possible to capture the results offline, and then have access sync the changes to the sharepoint server?

I'll be a monkey's uncle if your problem can't be solved without need of a mail merge ... not that that would be remotely difficult.

And I don't see how getting anything into Sharepoint helps a site with no internet access.

Am I missing something?

Management must be gently persuaded that how their objectives are met should be left to the app builder.

Why not have your app 1) write a PDF and 2) email it to an address someone at such a site can retrieve on their mobile/cell phone?

[knarfreppep]

Looking at your initial post, can we see screen captures of two or three of your other tabs please?

[June7]

That still doesn't explain why management insists on mail merge. If the same output can be produced with an Access report, why should they care about the means? Do they really want to allow users to edit the constructed Word document(s)? Is there so much 'boiler plate' text involved to make Access report impractical?

[knarfreppep]

I suspect management may be 'insisting' on the only option they've been given.

[orange]

I agree that it appears management has taken on the role of technical designer/implementer.
Probably, as knarfreppep said --it's the only thing they know or have seen.
This seems to be a case of where clear requirements in business terms would be very useful.
This at least could have resulted in some options with pros and cons of each.

I haven't used mail merge in a long time(years), but I think it can use a query so could get data from multiple tables.
Nokoff have you tried using a multi-table query to do a simple mail merge. You could determine the feasibility of this with some testing. Better to find out if the approach is possible before spending too much effort.

Good luck.

[knarfreppep]

When Mr/Ms off completes the dB design, all should become clearer.

I've thought a little more about tables -
.tblEntities with fields lngID, strName, strEmail, etc.
.tblAudits with fields lngID, lngEntity, dtmAudited, bytAuditor, memOverallAuditOpinion, dtmExitInterview, memActionsAgreedWithAuditee, etc.
.tblAuditExceptions with fields lngAudit, bytExceptionCategory, intException, memComments, ysnCritical
.tblLookup_AuditExceptionCategories with fields bytID, strDescription
.tblLookup_AuditExceptions with fields intID, bytCategory, strDescription
.tblAuditors with fields bytID, strName_Family, strName_Given, strEmail

[Nokoff]

When Mr/Ms off completes the dB design, all should become clearer.

I've thought a little more about tables -
.tblEntities with fields lngID, strName, strEmail, etc.
.tblAudits with fields lngID, lngEntity, dtmAudited, bytAuditor, memOverallAuditOpinion, dtmExitInterview, memActionsAgreedWithAuditee, etc.
.tblAuditExceptions with fields lngAudit, bytExceptionCategory, intException, memComments, ysnCritical
.tblLookup_AuditExceptionCategories with fields bytID, strDescription
.tblLookup_AuditExceptions with fields intID, bytCategory, strDescription
.tblAuditors with fields bytID, strName_Family, strName_Given, strEmail

Thanks again for all the input guys, I am considering all aspects and trying to make the best of the situation.

The reason I am sticking to mail merge is because the word document is VERY lengthy and to convert the whole thing to an access report would be a task on it's own. I'm not saying I won't explore the possibility but it will be a function of time vs benefit.

The thinking behind sticking with a checkbox approach per question is to provide guidance to the auditor on what to look for, as well as have a record that the person checked that item, and didn't just skip over it.

In this case, what does byt in bytAuditor stand for? I am trying to replicate your table structure now and I'm just wondering, the reference tblLookup_ is it a normal table containing lookup fields? I'm sorry I understand it must be frustrating to explain these things to a newbie but I really appreciate your help.

I can wrap my head around raising individual records for Audit Exceptions, but now what if I include audit STRENGTHS as well. The reason for doing so is in the audit report we identify the things that are not control weaknesses and include them as strengths in the report.

This is what I have in my Audit_Exceptions table. As I understand your structure, Audit Exceptions should contain all of the possible weaknesses, but should it contain strengths as well? Or should I make 2 separate tables to store the audit results of the interview questions depending on whether the checkbox was ticked or not, tblAuditStrengths and tblAuditWeaknesses.

[knarfreppep]

Many, some would say wise, database designers and programmers use mnemonics (memory joggers) in object and field names so when they and others see those names in code they know what they're looking at - e.g. tblTableName, frmFormName, basModuleName, sfrmSubformName, qryQueryName, bytNameOfField/VariableOfTypeByte/SmallInteger, intNameOfField/VariableOfTypeInteger, dtmNameOfField/VariableOfTypeDateTime, rsNameOfRecordset, etc. ... Google 'Leszynski naming convention'

If you want your auditor to consider all prescribed exceptions/strengths, you might, instead of tblAuditExceptions, have e.g. -
tblAudits_Findings with fields lngAudit, bytAuditAreaCategory, intAuditArea, bytFinding, memComments

Then you'd need -
tblLookup_AuditFindings with fields bytID, strDescription which might have records like -
1, Not Present
2, Above average
3, Not checked in this audit
4, Below average
5, Average
6, Present

Then you might also consider a tblAudits_Findings_Template with 'blank' records for all the items to be checked which can be copied into tblAudits_Findings when a new audit record is created.

[Nokoff]

Alright, thank you for that explanation, I've done a bit of programming before so I'm familiar with the naming conventions, I was just stumped as to a data type byte in access. Also why have you used byte for category but long integer for lngAudit? Shouldn't those 2 fields use the same data type?

I'm going to create a new topic if that is alright because this one has gotten a bit complicated. I'm starting from scratch and I would like to have my bases covered before moving forward.

[knarfreppep]

One would not expect 255 categories but many audits.