vba - 如何使 Access 2016 交叉表输出列通用？

Question

我想比较两年的预算。年份从 2020 年到 2028 年（比如说）。我需要交叉表报告驱动查询能够输出“Year1”和“Year2”，而不是说 2021 和 2022

我已经创建了将驱动报告的交叉表查询，但测试是比较 2027 年和 2028 年。这意味着输出列已被硬编码为 2027 年和 2028 年。我想询问用户在年份范围，我不能有引用“2027”或“2028”的报告，它必须只引用“Year1”和“Year2”。

我对此一头雾水，并尝试输入列标题“Year1”和“Year2”，但这当然会出错。

我想做的事可能吗？

谢谢

标签： vbams-access

Answer 1

You should create a Period2 column that calculates something like:

Period2: iif(period = dmax("period", "mytable"), "Current", "Previous")  

Probably not superfast but you get the idea.
You could achieve a similar result with perhaps a better performance by creating (and joining) a subquery like

select Min(period) as Previous, max(period) as Current from myMainTable

You can then use this Period2 as the Column header in your xtab.