Others

Create a Composite-Key Join Between Two Tables in Microsoft Access

Adam DavisAdam Davis Nov 22, 2026 868 views

Question details

Create an Access query that joins two tables using a composite key and calculates costs without a circular reference.

Create a Composite-Key Join Between Two Tables in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
DrugFormStrengths and PatientPrescriptions share drug, form, strength, and unit fields that identify the related records.
Observed behavior
A calculated expression may not be available as a join field, and a calculated alias matching a source column can cause a circular-reference error.
Before you start

Open the database in Access and confirm the exact field names and compatible data types in both tables. Check that the fields used in cost calculations contain numeric values; unit labels such as mg and mcg are text.

Solution 1Recommended

Join the Four Key Columns and Use a Distinct Calculation Alias

Use the underlying columns to match records, then give any calculated cost a name different from the source field.

1
Open a query

In Access, select Create > Query Design, add DrugFormStrengths and PatientPrescriptions, and switch to SQL View.

2
Join the matching columns

Enter a query using all four key fields, adjusting names if necessary: SELECT PatientPrescriptions.*, DrugFormStrengths.* FROM PatientPrescriptions INNER JOIN DrugFormStrengths ON (PatientPrescriptions.[Drug] = DrugFormStrengths.[Drug]) AND (PatientPrescriptions.[Form] = DrugFormStrengths.[Form]) AND (PatientPrescriptions.[Strength] = DrugFormStrengths.[Strength]) AND (PatientPrescriptions.[Unit] = DrugFormStrengths.[Unit]);

3
Run the query

Select Run and check that the results contain the expected matching records. An INNER JOIN returns only rows that match on all four fields.

4
Add a cost calculation

In Design View, add a calculated field with a distinct alias, such as Total Component Cost: [SourceTable].[2019 Component Cost] * [SourceTable].[Volume]. Replace SourceTable with the actual table name or names and use numeric fields.

Join the Four Key Columns and Use a Distinct Calculation Alias
Qualify ambiguous fields: When both tables contain a field with the same name, include its table name, such as [DrugFormStrengths].[Unit]. Do not multiply a text unit label such as mg by a quantity.
Free Microsoft Office alternative

Work With Prescription Spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface and compatibility with Microsoft Office formats for an easy transition. WPS Spreadsheets can help you review prescription and cost data, but it does not fix or replace an Access database query.

Review prescription and numeric cost data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for everyday spreadsheet workGet a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why should I join all four fields instead of combining them?

The composite key consists of drug, form, strength, and unit together. In Query Design or SQL View, join each matching pair so Access checks every part of the key without relying on a calculated concatenation.

Why does Access report a circular reference in my calculated field?

An alias that matches a source column can cause Access to interpret the expression as referring to itself. In Query Design, qualify the source column with its table name and use a different alias, such as Total Component Cost.

Can I multiply a unit such as mg by quantity?

No. A unit label such as mg or mcg is text. In the query grid, multiply numeric fields such as UnitPrice and Quantity instead, and display the unit label separately.

What if I need prescriptions without a matching drug record?

Use a LEFT JOIN from PatientPrescriptions to DrugFormStrengths instead of an INNER JOIN. In Query Design, open the join properties and choose the option that includes all records from PatientPrescriptions.