How can I translate the results of a dynamic query into a report?

Carol Davis 25 Reputation points
2026-10-30T20:51:31.98+00:00

I have a table in MS Access with Medical Lab results. There are 25 fields of results. I have a dynamic query that allows changing the field to display in a query and it is working properly. I want to build a report that will allow the different fields to be displayed. Currently when I build a report based on the dynamic query the field name remains fixed while the dynamic query results changes the field name correctly. How can I do this?

Microsoft 365 and Office | Access | Development
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Duane Hookom 27,030 Reputation points Volunteer Moderator
    2026-10-30T21:36:02.4466667+00:00

    There is an example of reporting a crosstab with unknown headings at http://access.hookom.net/Samples.htm

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments

  2. AI answer

    2026-10-30T20:52:14.5633333+00:00

    Use a fixed alias for the changing query column, bind the report text box to that alias, and update only the heading to show the selected laboratory field.

    Modify the SQL generated by the existing dynamic query so that whichever laboratory field is selected is always returned as the same alias—for example, ResultValue.

    SELECT
    PatientID,
    TestDate,
    [Glucose] AS ResultValue
FROM LabResults;

    When another result is selected, generate:

    SELECT
    PatientID,
    TestDate,
    [Cholesterol] AS ResultValue
FROM LabResults;

    The source field changes, but the output column remains ResultValue. Access SQL supports assigning a different result-column name with AS.^1^

    Configure the report as follows:

    1. Open the report in Design View.
    2. Press F4 to open the Property Sheet.
    3. Select Report, then open the Data tab.
    4. Set Record Source to the saved dynamic query. A report’s RecordSource can be a query name or SQL statement.^2^
    5. Select the result text box.
    6. Set Control Source to ResultValue. The text box will now remain bound even when the underlying laboratory field changes.^3^
    7. Give its heading label a clear name, such as lblResultHeading.

    If the field selection is made on a form—for example, combo box cboResultField on frmLabSelector—set the report heading in its On Open event:

    Private Sub Report_Open(Cancel As Integer)
    Me.lblResultHeading.Caption = _
        Forms!frmLabSelector!cboResultField.Column(1)
End Sub

    Adjust the column number to the combo-box column containing the display name. The label’s Caption property controls its displayed text.^4^

    Keep the selector form open while opening the report.

    Alternative: rebind the report control at runtime

    If changing the query to return a stable alias is not possible, change both the text box binding and its label when the report opens:

    Private Sub Report_Open(Cancel As Integer)
    Dim fieldName As String

    fieldName = Forms!frmLabSelector!cboResultField.Value

    Me.txtResult.ControlSource = "[" & fieldName & "]"
    Me.lblResultHeading.Caption = fieldName
End Sub

    This requires the selected field to exist in the report’s current RecordSource; otherwise Access reports that the field is not in the field list. Limit the combo box to the 25 known field names rather than accepting arbitrary user-entered names.

    The alias approach is preferable because the report always sees the same structure: PatientID, TestDate, ResultValue, and any other fixed columns. Only the query decides which underlying result populates ResultValue.

    References

    1. SELECT statement (Microsoft Access SQL)
    2. Report.RecordSource property (Access)
    3. TextBox.ControlSource property (Access)
    4. Label.Caption property (Access)
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

    1 person found this answer helpful.
  3. Carol Davis 25 Reputation points
    2026-11-05T13:34:28.1866667+00:00

    The response from DGB resolved my problem

    Was this answer helpful?

    0 comments
  4. DBG 11,801 Reputation points Volunteer Moderator
    2026-10-30T20:57:20.94+00:00

    When you say "dynamic" query, does that mean you're using a crosstab query? To create a dynamic report, you may have to resort to using some VBA code. Are you okay with that?

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.