将付款应用于发票以报告未偿还债务 [英] Applying payments to invoices to report outstanding debts
问题描述
让我先说一下我使用Access（10年前），但我真的是从头开始学习......
我有一个包含4个表的数据库，只有2个我需要使用它。
InvoiceTable
ClientName（从ClientTable中选择）
InvoiceType（从LookUpTable中选择）
InvoiceNumber（主键 - 数字）
InvoiceDate（日期）
InvoiceAmount（货币）
InvoiceComments（长文本）
PaymentsTable
ClientName（从ClientTable中选择）
InvoiceNumber（从InvoiceTable中选择）
TransactionID （主键 - 数字）
TransactionDate（日期）
TransactionAmount（货币）
所以我期待运行查询这将汇总（PaymentsTable.TansactionAmount）所有相同的PaymentsTable.InvoiceNumber'，然后从InvoiceTable.InvoiceNumber中扣除与其付款总和相匹配的总额。
我ho pe有意义。
Let me preface this with I have used Access (10 years ago) but really am learning again from scratch...
I have a database with 4 tables, only 2 of which I need to work with.
InvoiceTable
ClientName (Select from ClientTable)
InvoiceType (Select from LookUpTable)
InvoiceNumber (Primary Key - Number)
InvoiceDate (Date)
InvoiceAmount (Currency)
InvoiceComments (Long Text)
PaymentsTable
ClientName (Select from ClientTable)
InvoiceNumber (Select from InvoiceTable)
TransactionID (Primary Key - Number)
TransactionDate (Date)
TransactionAmount (Currency)
So I am looking to run a query that will sum(PaymentsTable.TansactionAmount) for all identical PaymentsTable.InvoiceNumber''s and then subtract that total from InvoiceTable.InvoiceNumber that matches the sum of its Payments.
I hope that makes sense.
推荐答案
我将创建两个Group By查询到SUM（）ClientName和InvoiceNumber的Amount字段，其中包含dummy和dummy。对于缺失的金额然后可以将这两个查询再次分组到一个UNION中，该UNION具有一个用于InvoiceAmount和TransactionAmount的MAX（）。
这导致每个客户一行/发票与发票和交易金额可以减去。
得到这个想法？
Nic; o）
I would create two Group By queries to SUM() the Amount fields for ClientName and InvoiceNumber with a "dummy" for the "missing amount" these two queries can then be grouped again in a UNION having a MAX() for the InvoiceAmount and the TransactionAmount.
This results in one row per client/Invoice with Invoice and Transaction Amount that can be subtracted.
Getting the idea ?
Nic;o)
@ nico5038
谢谢，这实际上帮了很多...我正在努力将所有发票和所有付款的总和合并到一起客户账户余额的表示。
@nico5038
Thank you, that actually helped quite a bit... I am just struggling on combining the totals of all the invoices and all of the payments to give a representation of the clients account balance.
客户账户余额是发票总和减去付款总额。由于这些将在单独的表中，您需要将它们与UNION组合，通过使一个字段为负以使总和获得未付金额或使用虚拟列来表示相反的值。得到两个字段你可以减去如下：
The clients account balance is sum of invoices minus sum of payments. As these will be in separate tables, you''ll need to combine them with a UNION, by making one field negative to let the sum get the outstanding amount or using a dummy column for "the opposite value" to get two fields you can subtract like:
这篇关于将付款应用于发票以报告未偿还债务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
