如何使用另一个表字段作为 MS Access 的条件 [英] How to use another table fields as a criteria for MS Access

问题描述

这是 MS Access 相关查询.

我在 MS Access 中有一个包含三列的表格:FName、FValue 和 VDate.(实际表格相当大，但以下示例仅供参考.)!http://postimg.org/image/bx0grwoa3/

I have a table with three columns: FName, FValue and VDate in MS Access.(The actual table is quite big but the following example is for reference.) !http://postimg.org/image/bx0grwoa3/

现在我想获得以下类型的查询输出:获取每个唯一名称的最小季度值.例如:!http://postimg.org/image/je1w7gdi1/

Now I want to get a following kind of query output: Get the minimum quarterly values for each unique name. For example: !http://postimg.org/image/je1w7gdi1/

到目前为止，通过使用以下(在 MS Access 中)，我能够通过硬编码标准获得四分之一的输出

So far I am able to get the output for one quarter by hardcoded criteria, by using the following (In MS Access)

SQL 字符串为:

SELECT Table.FName, Min(Table.FValue) AS MinOfFValue, First(Table.VDate) AS FirstOfVDate
FROM [Table] LEFT JOIN [Table] AS Table_1 ON Table.FValue = Table_1.FValue
WHERE (((Table.VDate)>#3/31/2014# And (Table.VDate)<#7/1/2014#))
GROUP BY Table.FName;

现在，而不是将日期硬编码，我希望日期成为表的一部分，其中季度名称，从日期和到日期都在那里，Access 将它们一个一个地取出并提供所需的输出.

Now instead of the putting date hard coded, I want the dates to be part of a table where Quarter name, from date and to dates are there and Access takes them one by one and give the desired output.

提前致谢.

推荐答案

第二个问题比第一个问题难一些.我的方法是使用 3 个单独的查询来获得答案:

The 2nd problem is a bit more difficult than the 1st. My approach would be to use 3 separate queries to get the answer:

Query1 为原始表中的每条记录返回一条记录，从季度表中添加年份和季度.请注意，您可以轻松地从日期计算年份和季度，而不是使用季度表.

Query1 returns a record for each record in the original table, adding the year and quarter from the quarters table. Note that instead of using the quarters table, you could just as easily calculate the year and quarter from the date.

SELECT Table.FName, Table.FValue, Table.VDate, Quarters.Yr, Quarters.Qtr
FROM [Table], Quarters
WHERE (((Table.VDate)>=[start] And (Table.VDate)<=[end]));

Query2 使用 Query1 的结果并找到您需要的最小值:

Query2 uses the results of Query1 and finds the minimum values you need:

SELECT Query1.FName, Query1.Yr, Query1.Qtr, Min(Query1.FValue) AS MinValue
FROM Query1
GROUP BY Query1.FName, Query1.Yr, Query1.Qtr;

Query3 匹配 Query1 和 Query2 的结果以显示达到最小值的数据.请注意，我做了一个 Sum 查询并使用了 First(VDate)，假设最小值可能出现不止一次，而您只需要它发生的第一次.

Query3 matches the results of Query1 and Query2 to show the data on which the minimum value was reached. Note that I made this a Sum query and used First(VDate), assumining that the minimum value may have occurred more than once and you need only the 1st time it happened.

SELECT Query1.FName, Query1.Yr, Query1.Qtr, Query2.MinValue, First(Query1.VDate) AS MidDate, Query1.FValue
FROM Query1 INNER JOIN Query2 ON (Query1.Qtr = Query2.Qtr) AND (Query1.FValue = Query2.MinValue) AND (Query1.FName = Query2.FName)
GROUP BY Query1.FName, Query1.Yr, Query1.Qtr, Query2.MinValue, Query1.FValue;

可能有一种聪明的方法可以在一个查询中完成所有操作，但这是通常解决类似问题的方法.

There's probably a clever way to do this all in one query, but this is the way usually solve similar problems.

这篇关于如何使用另一个表字段作为 MS Access 的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！