Nightly import keeps loading bad enrollment rows

Keep EnrollmentID as the primary key, because the two existing relationships depend on it and a composite primary key would require deleting and recreating them.

Add a composite unique index instead:

1. Open the table in Design view and click Indexes on the Design tab.
2. Add a row with Index Name uniqStudentCourse, Field Name StudentEmail.
3. Add a second row with the same index name and Field Name CourseCode, and set Unique = Yes on the first row only. Leave Primary = No.

This permits many students on one course and many courses for one student, but only one row per pair.

Two traps. First, Access cannot create the index while the 340 duplicate pairs exist - deduplicate first (a Find Duplicates query, or a totals query keeping Max(EnrollmentID)). Second, Access does not enforce a unique index on records where an indexed field is Null, so a blank StudentEmail slips straight past the index. Set Required = Yes on both StudentEmail and CourseCode before saving the index, otherwise the constraint is decorative.

StartDate is text, so comparisons are string comparisons: '10/01/2024' sorts before '2/01/2024', which is why October precedes February, and a date-range criterion in a query fails silently because Access compares a string to a date. Change the field to Date/Time. Convert existing values first, because Access cannot always parse mixed formats on a type change: add a temporary Date/Time field, populate it with CDate or DateSerial built from the pieces, verify, then swap the fields. Set Format to Short Date and add an index - date fields are routinely used in criteria and sorting.

Fee as Double explains 1299.9999999: binary floating point cannot represent decimal currency. Change it to Currency (four decimal places internally, two displayed) and set Decimal Places = 2. Add a table-level Validation Rule of >=0 with Validation Text naming the field, so the -450 row is rejected at insert time. If fractional cents ever matter, use Decimal with a defined scale instead.

CourseCode needs Field Size raised to 12 or more. Field Size is a hard ceiling enforced on every append, which is why the import truncated rather than errored. For the email, add a Validation Rule such as Like '*?@?*.?*' plus Required = Yes; a Validation Rule is enforced by the engine on any insert path, including imports and append queries, which an input mask on a form is not.

Point the import at a staging table with the same columns typed correctly - Date/Time for StartDate, Currency for Fee, no validation rules yet - so raw text never lands in the production table. Then append production rows from the staging table with an append query. Every constraint you defined fires during that append: Required fields, Validation Rules, Field Size, and the composite unique index.

Access does not stop the whole batch when a row fails. It appends the good rows and writes the rejected ones, with the error text, to the import errors table named after the destination (for example tblEnrollments_Paste Errors). The workflow is therefore: import to staging, run the append, then read the errors table, correct the source file, and re-run. Keep the staging table and the append query as saved objects so the nightly job is one macro or one saved import specification plus one query.

Limits to state honestly: table-level rules catch format, range, required and duplicate problems, but they cannot catch a valid-looking value with the wrong meaning (a CourseCode that exists but is retired). That needs a lookup to tblCourses or an unmatched query against the reference table, run on the staging table before the append.