Create a Composite-Key Join Between Two Tables in Microsoft Access
Question details
Create an Access query that joins two tables using a composite key and calculates costs without a circular reference.

- 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.
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.
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.
In Access, select Create > Query Design, add DrugFormStrengths and PatientPrescriptions, and switch to SQL View.
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]);
Select Run and check that the results contain the expected matching records. An INNER JOIN returns only rows that match on all four fields.
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.

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.

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.




