如何在MS Access中使用多对多关系进行计算

Question

我有一个具有以下结构的数据库。每本书可以有多个作者，每个作者可以写多本书。

[book:book_id, book_name, book_price]
[author: author_id, author_name]
[link:book_id, author_id]

{book_id 和author_id 已链接。完整的结构如下所示：https://i.sstatic.net/vIFNU.png}

现在每本书都有一个价格（货币）。价格的30%应平均分配给对本书做出贡献的每位作者。 我的问题是如何找到特定年份每个作者的总付款。

[我自己想到了一个解决方案。我只能做第 1 步。如果您可以向我提供一些提示或材料，让我可以找到如何进行此类操作，这将非常有帮助]

我的解决方案的算法是：

1. 因此对于每个 book_id ，我需要找到中间表中具有相同book_id的author_id的数量。 （可以通过查询来完成）

2. 如果我将 book_price 除以 number_of_author_in_book 并乘以 30/100，我会得到该书的钱，该钱将转到该书的每个作者的帐户（例如 payment_of_one_author_in_book）

3. 对于中间表中的每个author_id，我查找相应的 book_id 并将该author_id 的 payment_of_one_author_in_book 添加到与作者 ID 对应的新变量（author_ payment_this_year），如果年份与查询年份匹配。

提前致谢

Answer 1

SELECT author.AuthorId, author.Author_name, book.Book_Name, book.Book_Price, [Book_Price]/DCount("[Author_id]","[Link]","[Book_id]=" & [Book_Id]) AS Share
FROM (author INNER JOIN link AS link_1 ON author.AuthorId = link_1.Author_id) INNER JOIN book ON link_1.Book_id = book.Book_Id;

基本上，它只是三个表的联接。唯一棘手的一点是使用 DCount 函数来计算 Link 中有多少作者共享这本书的 ID

Answer 2

此示例包括别名和子查询。

SELECT 
    a.author_id,
    a.author_name,
    SUM(share.auth_share) AS author_total
FROM (link l
INNER JOIN (
    SELECT 
        b.book_id,
        ( [b.book_price] * 0.3 ) / [no_auth] AS auth_share
    FROM book b
    INNER JOIN (
        SELECT 
            l.book_id,
            COUNT(l.author_id) AS no_auth
        FROM   link l
        GROUP  BY l.book_id) AS ac
    ON b.book_id = ac.book_id) AS share
ON l.book_id = share.book_id)
INNER JOIN author a
ON l.author_id = a.author_id
GROUP BY a.author_id,a.author_name