Add a Memo Field to an Access Report

[ProgEvalJAA]

I've designed a quality assurance report in which Access creates a customized list of corrections for a particular staff member in a specific geographic area. Each staff member receives a one to two-page-report with only his/her information.

I want to add a memo field in order to enable the person conducting the quality assurance review to add comments to the database. The comments, in turn, will appear on each staff member's report. Currently, the memo field is not in the database.

What is the most efficient method in which to proceed with this task?

 

[dhookom]

You would add the field to a table. Without you telling us anything specific about your tables and fields, we can't be of much assistance.

Do you anticipate multiple comments per staff review or per list correction?

Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

I will describe the database structure for more specific help.

I have a table called "Primary".

It has the following fields:

Customer
Staff Member
Geographic Area/Region
Quality Control Staff
Question 1
Question 2
Question 3
And so forth

When entering information for the specific records, the questions receive one of three values: 1, 2 or 3

The value of 2 means the question is deficient for that particular customer or needs correction in some manner from a quality control perspective.

Based on this table, I have created several select queries to pull only the questions for each customer with values of 2. Those select queries are then turned into reports.

Those reports serve as sub-reports in a main report that through another query pulls all staff members and geographic areas/regions.

The report has been sorted and grouped such that each staff member receives a report listing only their specific deficiencies/issues needing correction as well as the customer names affected by the deficiencies. The report does not print any values if there are not any corrections or deficiencies.

I want to add a memo field to the report. I want it to be an “Additional Comments” type of field where the quality control staff will enter any additional information regarding the issues needing correction for that particular staff member.

The Comments field does not currently exist in the database.

If I add it to the primary table at the customer level, there will be no way to group all of the customer comments because the text will be different for each customer.

The next best thing I can think of would be to somehow create a new table with the staff members and geographic regions, then add the comments field grouped like this:

Geographic Area Staff Member Comments

Area 1 Jane Doe Review policy manual.

I would then query this table and use the results as a sub-report for my main report.

What I’m wondering is how to keep the tables in sync with each other. That is, how do I keep the Primary Table and the New Table in sync so that if the assigned staff member or geographic region changes for a customer, the new table updates automatically? Or, is there an easier way to complete this task?

 

[dhookom]

Your Primary table isn't normalized. To get the report of deficiencies, I would first create a normalizing union query like:

SELECT PrimaryKeyFieldName, 1 as Question, [Question 1] As Result
FROM Primary
UNION ALL
SELECT PrimaryKeyFieldName, 2 , [Question 2] 
FROM Primary
UNION ALL
SELECT PrimaryKeyFieldName, 3 , [Question 3] 
FROM Primary
UNION ALL
-- etc ---

It would then be quite simple to query all the 2s from the Result field.

I don't know if you want comments at the Customer or other level. If you don't have a table for the level you need the comments at, you will need to create one.





Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

Thank you for the SQL union query information. I had already created my deficiency report several days ago, and it appears to work well, so I'll probably save the SQL information for another project.

However, I know I need to add the Comments field to a table, but I'm not sure how to proceed.

Can you review the second part of my last post that mentions creating the second table of staff members and comments and see if you have any advice for me?

 

[dhookom]

Don't you just want one comment per record in the primary table?

Duane
Hook'D on Access
MS Access MVP

 

[ProgEvalJAA]

I want one overall comments line per staff member, not the customer.

The primary table is governed at the customer level with unique id numbers.

Right now, I have a query set up that groups all my staff members, so their names appear once in my master report, no matter how many customers he/she has. I want to add an overall comments field at that level on the master report.

In other words, I want my users to be able to go into some data entry form, key in a staff member's name and add overall comments regarding that staff member only not his/her customers. The comments will then print on my master report.

 

[PHV]

So, you should have a staff table, don't you ?

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgEvalJAA]

Yes, how I would keep it linked to my primary table so the two would stay syncrhonized?

 

[PHV]

Your primary table should have a ForeignKey referencing your staff table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgEvalJAA]

Thanks, dhookom and PHV. I have read all this information and learned a lot.