结合两个 MS Access 查询 [英] Combining two MS Access queries

问题描述

我有这个查询:

SELECT "I1" & "," & "I2" AS Item_set, Round(Sum([T1].Fuzzy_Value)/Count(*),15) AS Support
    FROM (SELECT *
    FROM Prune AS t
    WHERE t.Trans_ID IN 

        (SELECT t1.Trans_ID FROM (

         SELECT *FROM Prune WHERE [Nama]="I1")  AS t1 
         INNER JOIN (SELECT * FROM Prune WHERE [Nama]="I2")  AS t2 ON t1.Trans_ID = t2.Trans_ID)

    AND t.Nama IN ("I1","I2"))  AS T1;

和 ttrans 查询

And ttrans query

SELECT Count([Trans_ID].[Trans_ID]) AS Expr1
FROM Trans_ID;

我需要将 Count (*) 从 :

 SELECT "I1" & "," & "I2" AS Item_set, Round(Sum([T1].Fuzzy_Value)/Count(*),15)

进入 ttrans 查询.

into ttrans query.

我试过使用

SELECT "I1" & "," & "I2" AS Item_set, Round(Sum([T1].Fuzzy_Value)/ttrans.Expr1,15) AS Support
FROM (SELECT *
FROM Prune AS t
WHERE t.Trans_ID IN 

    (SELECT t1.Trans_ID FROM (

     SELECT *FROM Prune WHERE [Nama]="I1")  AS t1 
     INNER JOIN (SELECT * FROM Prune WHERE [Nama]="I2")  AS t2 ON t1.Trans_ID = t2.Trans_ID)

AND t.Nama IN ("I1","I2"))  AS T1, ttrans;

但是我得到了这样的错误:

But I got error like this :

You tried to execute a query that does not include the specified expression 
'Round(sum([T1].Fuzzy_Value/ttrans.Expr1,15)' as part of an aggregate function

知道如何修复它吗?

注意:我试图在交易数据库中找到所有项目的 2 个组合并得到这样的结果

Note : I'm trying to find 2 combination of all item in transaction database and get a result like this

ITEM       Support
I1, I2     0.xxxxxxxxx

支持在哪里(包含项目 I1 和 I2 的总交易/总交易)-> 请注意，我正在使用 ttrans 查询来获取总交易价值

where support is (total transaction containing item I1 and I2 / total transaction) -> note that I'm using ttrans query to get total transaction value

注意2:我使用的是 MS Access注意 3:

note2: I'm using MS Access note3:

Ttrans 表看起来像这样

Ttrans table will look like this

Expr1
270200

推荐答案

我以某种方式找到了答案:

somehow i find the answer :

我尝试使用

SELECT "I1" & "," & "I2" AS Item_set, Round(Sum([T1].Fuzzy_Value)/sum(ttrans.Expr1),15) 

效果很好

这篇关于结合两个 MS Access 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！