Table of contents
At a glance
Which data relationships do you need to test when validating Access?
Test the relationships required for a complete and accurate result in Access. Include unique keys, related detail records, joins and behaviour under deletion or concurrent editing. A form opening correctly does not demonstrate that a report includes all relevant records.
Scope and Applicability
This article addresses the logic of a specific Access application. An Access file and an Access interface with an external database backend possess different technical limitations. Referential integrity must be verified at the actually primary data repository.
Assess cascading deletion and concurrent editing early
Deletion and update cascades require deliberate evaluation. A convenient technical deletion may result in the removal of quality-related records. When data is corrected or invalidated for technical or scientific reasons, traceability must be preserved. An appropriate solution may involve a status logic instead of physical deletion.
In a multi-user environment, you should test concurrent modifications using realistic work processes. Two users should be able to open the same process and save different content. The expected locking or conflict behavior should be specified in the requirements, so that a lost update is not treated merely as a user error.
Understand the technical data model prior to form completion
Start with the objects upon which your decisions are based. A small laboratory database can store samples, test requests, methods, and results in separate tables. For each result value, it must be clearly attributable to the corresponding sample and the specific method version. A correctly displayed numerical value is professionally meaningless if the assignment to the sample and method version is incorrect.
Prepare an overview including primary key, foreign key, and intended relationship. Differentiate between one-to-one, one-to-many, and many-to-many relationships mapped through an intermediate table. Review imported tables and linked data sources. The relationship line displayed in Access alone does not prove that all desired integrity rules are effectively enforced. The actual enforcement depends, among other factors, on the location of the tables in storage.
Microsoft defines referential integrity as protection against orphaned references. Cascading deletion, on the other hand, allows intentional removal of associated data records. Therefore, from a technical and regulatory standpoint, it must be assessed whether any deletion operations are permissible for records subject to retention requirements. The availability of a technical option does not constitute an appropriate default for every GxP process. Test your selected deletion rule in a controlled environment.
Check queries for completeness and duplicate counting
An inner join displays only dataset combinations that have matching pairs. An outer join can also show objects lacking a corresponding match. This distinction impacts, for example, a report on all submitted samples. If an uninvestigated sample is excluded by the query, the report may erroneously appear complete. Therefore, define which missing relationships must remain visible in the report.
Multiple relationships can, in reverse, amplify values. When a query links a sample to multiple results and multiple attachments, the combination may generate additional rows. Aggregations and counts must then be verified against an independently known dataset. The statement "The query runs without error messages" neither addresses completeness nor correct aggregation.
| Data scenario | Test case | Expected process outcome |
|---|---|---|
| Probe without result | Generate a report with an open inspection assignment | Open sample remains visible in the intended status. |
| Two results per sample | Compare count and result assignment | Probes and results are counted according to the reporting definition. |
| Result with invalid sample | Verify foreign key invalidity control attempt | Selected integrity control prevents or detects inappropriate references |
| Several annexes | Compare report before and after Annex amendment | Technical amounts do not change by annexes alone |
| Missing method version | Reconstruct historical dataset | Missing context is detected and not silently replaced |
Refined test example: Sample, method, and result
The sample P-104 contains two result datasets: E-201 for method M-3 Version 2 and E-202 for a justified repeat. The report should display both results with their respective usage context. Prepare a known starting inventory and document the database version, table structure, query version, and user role.
In the positive case, a report is generated and each identifier is compared against the initial inventory. Subsequently, an attempt is made to link a result to a probe key that does not exist. The established referential integrity is designed to reject such invalid references. In the second case, the result is assigned to an existing, previously recorded probe. A simple key-based relationship cannot prevent this; here, a predefined technical verification is performed, for example, by cross-checking against the inspection mandate. The expected reaction is predefined for both cases prior to execution.
Add a deletion test for P-104. If the technical regulation requires retention of results, cascading deletion must not remove them without notice. Verify not only the notification in the form but also the remaining table inventory. A form may hide a delete button, while another editable pathway may still modify data.
| Test step | Prerequisite and Action | Expected evidence |
|---|---|---|
| Normal report | Open P-104 with E-201 and E-202 | Correct identifiers, method references, and result usage |
| Invalid reference | Create a result using a sample key that does not exist | Selected control responds predictably |
| Delete parent object | Attempt to delete existing results for P-104 | Fixed storage requirements remain valid. |
| Repeat to distinguish | E-202 amend and re-evaluate | E-201 is not arbitrarily overridden or obscured. |
| Report edit | Modify or filter in test copy selectively alter | Control comparison detects loss or double counting |
Include concurrent users and alternative write paths
Check whether multiple users are editing the same datasets. A meaningful test involves two users accessing the same inspection task and attempting to make conflicting changes. Observe the actually configured conflict resolution mechanism. Describe which version is stored and how the second user detects the conflict. This article does not guarantee any specific locking behavior for any access configuration.
Capture imports, macros, VBA, direct table editing, and external connections. A carefully reviewed input form does not automatically cover these functionalities. When users are able to copy or exchange data files, questions arise regarding primary ownership and access. File format, application locks, and organizational policies must be jointly evaluated within the actual operating model.
Use controlled test copies with known data for validation. Document differences from the production environment, user management, and links to external tables. A single-user test on the developer's workstation is insufficient to adequately simulate multi-user conflicts in a networked environment. Unverified production manipulations are not a suitable method for detecting such risks after the fact.
Which evidence and operating procedures should remain?
A viable package integrates a data model, critical queries, roles, and test documentation. Establish and document which tables and files are interrelated and which version is approved for release. Define a pathway for changes to queries, forms, imports, and code. A new query may alter the subject-matter scope of the analysis, even if the underlying tables remain unchanged.
Assess backup and recovery for the complete dataset. When surface and data storage are separated, multiple components may be required. Opening a file after recovery does not confirm correct assignment of historical results. Therefore, reconstruct a known process including method reference and report. The scope and frequency should be aligned with your usage and risk profile.
The scope of an access solution should be clearly defined. If required access, change, or retention controls cannot be adequately achieved with the selected model, an alternative architecture should be evaluated. This does not mean that every small access tool is inappropriate. What matters is demonstrable suitability for the specific, limited task.
Draft: Data relationship and query validation against a reference inventory demonstrates compliance
Use a small reference inventory whose relationships are independently known. In the example, sample P-104 has two results and two attachments; sample P-105 has no results yet. Define prior to testing whether the report counts samples, results, or documents. These three sets are distinct and must not be implicitly equated.
For each critical query, capture input objects, join, filter and aggregation. Check a missing counterpart and several matching counterparties. This makes it visible whether open samples disappear or sums are multiplied. The comparison should be based on known identifiers; a merely plausible value is too little.
If the reference dataset does not produce the expected result, first verify the model and query separately. A correct relationship may be compromised by an incorrect filter. Accurate display may be based on previously incorrectly imported data. Document the root cause and the affected scope before merely cosmetically adjusting the report.
| Template field | Example entry | Decision on testing |
|---|---|---|
| Purpose of the report | All sampled batches with processing status indicated. | P-105 must remain visible despite the absence of a result. |
| Key | ProbeID links the sample to its result. | The result must not be assigned to another existing sample. |
| Multiple relationship | P-104 has two results and two appendices. | Four join lines must not be counted as four samples. |
| Eradication Procedure | Historical mission should remain reconstructable. | Do not uncontrolledly delete parent objects and dependent data. |
| Write paths | Forms, import, and direct table are considered. | Review selected integrity rules for relevant pathways. |
| Supporting record | Starting balance, query version and actual report. | The expected and actual records have the same unique identifiers. |
Next steps
- Document critical tables, key points, and relationships.
- Check queries with missing and multiple assignments.
- Targetedly test cases for changes, deletions, and conflicts.
- Match results with the intended recording and authorization concept.
Project experience and collaboration
Test the forms, data model and result together
For an Access database, we look beyond the forms. Which tables, relationships and queries support the result? With your team, we assemble a known dataset that exposes missing results, double counting and the effects of deletion. If a single query causes the problem, a targeted correction and retest may suffice. If keys, write paths or concurrent access are unresolved, a sound data model comes first. We involve the people who maintain the database and identify where the authoritative data are held.
Frequent Questions on Access Relationships and Queries
Is referential integrity equivalent to data integrity?
Are linked SQL tables automatically backed up?
Can a report still be incorrect despite correct individual values?
Is a correctly established line of relationship sufficient to prove it?
No. Verify that integrity rules are actually enabled and effectively applied at the respective storage location. Additionally, queries and reports must fully and correctly utilize the intended data. A technical relationship may be correct, while a filter may exclude open samples or a join may inadvertently inflate summated values. Therefore, evidence must include both the data model and subject-matter analysis.
Must we fully retest every query?
The scope is determined by intended use, impact, and changes. A quality-relevant result calculation requires different validation evidence compared to a purely technical support view. Capture dependencies to enable identification of affected outputs when changes occur in tables or common queries. Reuse appropriate existing evidence when its current status and relevance can be reasonably transferred to the current usage context.
How do we identify duplicate counting in Access?
Use known objects with multiple associated results or attachments. Compare the expected number of objects to the actual output. Evaluate JOIN and aggregation separately. Equal sums from a simple inventory are not sufficient evidence when multiple relationships exist in the actual process.
Should reference integrity be verified in the frontend or backend?
Where the tables are actually stored. For linked tables, Access cannot enforce referential integrity in the frontend. If the tables are located in an Access backend file, integrity is activated there; in other database systems, their own integrity rules are primarily applicable. Verify the relevant write paths against the actual data location, rather than considering only a local frontend relationship.
Sources: [2]
Sources and review date
The sources provide the technical foundation. Tables, checklists, and examples are derived methodological tools, not verbatim regulatory requirements. Manufacturer information must be referenced to the specific version and configuration in use.
- European Commission: EU GMP Annex 11, Computerised Systems (2011)§§ 1, 4.4, 4.7, 5, 7 and 12: risk-related requirements, test methods, data and access. Applicable version according to EudraLex on 14.10.2026; no universal project or deadline.
- Microsoft: Create, edit or delete a relationship in AccessSupplier source for product functionalities. Availability and behavior depend on version, edition, license, and configuration; no general compliance certification.
Sources checked: .
Verify traceability of data relationships in Access
We support you in assessing the current situation, clarifying responsibilities, and implementing appropriate evidence within operations. The type of support that is most effective depends on your system, your team, and the specific objective.
- Computer system validation →
Bring your GxP systems into validated operation, from laboratory software, MES and eQMS to ERP. We define the scope, plan risk-based tests using GAMP 5 and hand the evidence to QA for release.

