From one messy spreadsheet to a trainee database plus a 400-letter mail merge
A skill-training partner in Panchkula runs an IT-ITeS course for 1,800 trainees across 24 batches, and every record currently lives in a single spreadsheet: name, phone, email, batch, and module scores in one wide row. Phone numbers appear as 98xxxxxxxx, +91-98xxxxxxxx and 098xxxxxxxx; 210 rows are near-duplicates created during re-admission; 60 trainees have no email at all. The manager wants three things: reliable answers to questions such as which trainees of batch B-07 cleared Module 3 with more than 70 marks, 400 offer letters sent through mail merge from one Word letter, and a record of who was sent what and when. The office has one printer and a strict monthly paper quota. You have been asked to propose the database design and the merge procedure.
1. Propose the tables, primary keys and foreign keys, and justify why the obvious candidates - phone number, email, or name - should not be the primary key of the trainee table.
Read the worked answer
Proposed structure (third normal form, so no repeating module columns):
- Trainee(TraineeID PK, FirstName, LastName, Phone10, Email, BatchID FK, Status)
- Batch(BatchID PK, BatchName, StartDate, TrainerName)
- Assessment(AssessmentID PK, TraineeID FK, ModuleNo, Marks, AssessedOn)
Module scores move out of the wide row into Assessment, one row per trainee per module. That is what makes 'cleared Module 3 with more than 70' a simple query instead of hunting across columns.
Why not phone, email or name as PK:
- Name is not unique - two trainees can share a name - and is editable.
- Email is blank for 60 trainees; a primary key cannot be null, and email addresses change.
- Phone looks unique but is stored in three formats, so the same person appears as three different values; a key must be one canonical value. After normalising to a 10-digit Phone10 it could serve as a candidate key, but it is still personal data that may be corrected.
A surrogate TraineeID never changes and never nulls; uniqueness is still enforced by a unique index on Phone10 after normalisation. 210 duplicates are removed by matching on normalised phone plus name before the key is enforced.
2. Write the criteria for the B-07 Module 3 question, and describe how the mail merge must be set up so the right 400 letters go out and the 60 trainees without email are handled deliberately.
Read the worked answer
Query criteria. Three conditions combined with AND: Batch.BatchName = 'B-07', Assessment.ModuleNo = 3, Assessment.Marks > 70. In Access query design, all three go on the same Criteria row; putting one on the OR row would return extra rows. Joining Trainee to Batch and Assessment on their keys avoids the duplicate-name problem entirely.
Merge setup. Use that saved query (or a CSV exported from it) as the data source, not the raw spreadsheet - filtering inside Word's recipient list works but is invisible to the next person and cannot be audited. Fields placed in the letter: Name, BatchName, ModuleNo, Marks. Because field names in the letter must match the source exactly, check NameExpr spelling before merging.
Blank emails. Do not let Word silently skip them. Two clean options: (a) filter the source into 'has email' and 'no email' sets, merge to email/PDF for the first and print a short list for the second; or (b) in the letter, use a conditional field such as { IF { MERGEFIELD Email } = "" "(hand delivery)" "{ MERGEFIELD Email }" }.
Always merge to a new document for a test of 3 records first, inspect, then complete the merge - and never overwrite the master letter.
3. The paper quota will not cover 400 letters plus a register. Justify a printing and record-keeping plan that keeps proof of despatch without wasting paper.
Read the worked answer
Split the 400 by delivery channel. Roughly 340 have a valid email, so merge those to PDF and send by email; nothing is printed. The 60 without email get printed letters, ideally two pages per sheet double-sided, which is about 30 sheets. Total paper is around 30-35 sheets instead of 400+ - a defensible justification to put in front of the manager, not just a preference.
Quality controls that save more paper than they cost:
- Merge 3 test records to a new document and proofread before the full run - a misspelt merge field reproduced 400 times is the costly failure mode.
- Print preview, and reprint a single faulty page rather than the whole batch.
- Keep the register electronic.
Record of despatch. Add columns SentOn, SentVia (Email/Print), and LetterRef to Trainee, updated by an update query after the merge, or mark a Sent flag per letter. This prevents the classic failure of sending duplicates to people who already received a letter after a re-run, and it is auditable evidence of who received what and when - which is what the manager actually asked for.
Old draft printouts go to the recyclable bin, and the printer is switched off at the wall overnight rather than left in standby.