How do I use an Alias in my Acess report

Question

I've created an alias in my Access SQL query, it shows fine as a valid column when I run my query, but when I try and use that field in my report, it reverts back to the original name and value. How do I make the report pull in the value of the alias returned in my record stack? Will I have to do this through VBA?

This is the query which is my report's Record Source:

SELECT
    ISO_ID.ISOID,
    ISO_Stats.TransactionCount,
    ISO_Stats.TerminalCount,
    ISO_Stats.MonthEnd,
    m2.TerminalCount,
    m2.TransactionCount,
    m2.MonthEnd,
    m3.TerminalCount,
    m3.TransactionCount,
    m3.MonthEnd
FROM
    (((ISO_ID
    INNER JOIN ISO_Stats AS m2
    ON ISO_ID.[ISOID] = m2.[ISOID])
    INNER JOIN ISO_Stats
    ON (ISO_ID.ISOID = ISO_Stats.ISOID) AND (ISO_ID.InstNbr = ISO_Stats.InstNbr))
    INNER JOIN ISO_Stats AS m3
    ON ISO_ID.[ISOID] = m3.[ISOID])
WHERE
        (((ISO_Stats.MonthEnd)=DateSerial(Year(Date()),Month(Date()),0))
    AND ((m2.MonthEnd)=DateSerial(Year(Date()),Month(Date())-1,0))
    AND ((m3.MonthEnd)=DateSerial(Year(Date()),Month(Date())-2,0))
    AND ((ISO_ID.Cancelled)<>"Y") )
ORDER BY ISO_ID.ISOName;

Yes. I go into Report Properties and get to the query from the Data source. It shows as an available field and shows correctly on the design view. However, when I view the report, it shows the value of the original field. When I go back into design view, the data source for that label then reflects the original field. — Kevin Watts
Actually have three that I've created, using inner joins, which I probably should have mentioned, and all three are acting the same way. — Kevin Watts
SELECT ISO_ID.ISOID, ISO_Stats.TransactionCount, ISO_Stats.TerminalCount, ISO_Stats.MonthEnd, m2.TerminalCount, m2.TransactionCount, m2.MonthEnd, m3.TerminalCount, m3.TransactionCount, m3.MonthEnd — Kevin Watts
FROM (((ISO_ID INNER JOIN ISO_Stats AS m2 ON ISO_ID.[ISOID] = m2.[ISOID]) INNER JOIN ISO_Stats ON (ISO_ID.ISOID = ISO_Stats.ISOID) AND (ISO_ID.InstNbr = ISO_Stats.InstNbr)) INNER JOIN ISO_Stats AS m3 ON ISO_ID.[ISOID] = m3.[ISOID]) — Kevin Watts
WHERE ( ((ISO_Stats.MonthEnd)=DateSerial(Year(Date()),Month(Date()),0)) AND ((m2.MonthEnd)=DateSerial(Year(Date()),Month(Date())-1,0)) AND ((m3.MonthEnd)=DateSerial(Year(Date()),Month(Date())-2,0)) AND ((ISO_ID.Cancelled)<>"Y") ) ORDER BY ISO_ID.ISOName; — Kevin Watts

HansUp HansUp · Accepted Answer · 2016-04-20T16:29:44

Your query's SELECT column list includes duplicate column names. Use aliases so that the column names in the query result set are distinct:

SELECT
    ISO_ID.ISOID,
    ISO_Stats.TransactionCount AS [TransactionCount_i],
    ISO_Stats.TerminalCount AS [TerminalCount_i],
    ISO_Stats.MonthEnd AS [MonthEnd_i],
    m2.TerminalCount AS [TerminalCount_2],
    m2.TransactionCount AS [TransactionCount_2],
    m2.MonthEnd AS [MonthEnd_2],
    m3.TerminalCount AS [TerminalCount_3],
    m3.TransactionCount AS [TransactionCount_3],
    m3.MonthEnd AS [MonthEnd_3]

You will need to revise the report to use those aliased column names, but then you should be able to avoid the report's confusion when dealing with duplicate column names.

How do I use an Alias in my Acess report

1 Answers