本文是小编为大家收集整理的关于将付款应用于发票以报告未偿债务的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
让我以我曾经使用过 Access(10 年前)但真的又从头开始学习...
我有一个包含 4 个表的数据库，其中只有 2 个需要使用.
发票表
客户名称(从客户表中选择)
发票类型(从 LookUpTable 中选择)
InvoiceNumber(主键 - 编号)
发票日期(日期)
发票金额(货币)
发票评论(长文本)
付款表
客户名称(从客户表中选择)
发票编号(从发票表中选择)
TransactionID(主键-数字)
TransactionDate(日期)
交易金额(货币)
因此，我希望运行一个查询，该查询将对所有相同的 PaymentsTable.InvoiceNumber 求和(PaymentsTable.TansactionAmount)，然后从与其 Payments 总和相匹配的 InvoiceTable.InvoiceNumber 中减去该总数.
我希望这是有道理的.
推荐答案
我将创建两个 Group By 查询来 SUM() 使用"虚拟"的 ClientName 和 InvoiceNumber 的 Amount 字段.对于"缺失金额"；然后可以将这两个查询再次分组到一个 UNION 中，该 UNION 具有 InvoiceAmount 和 TransactionAmount 的 MAX().
这会导致每个客户/发票有一行，其中可以减去发票和交易金额.
明白了吗?
Nic;o)
@nico5038
谢谢，这实际上帮了很多忙……我只是在努力将所有发票和所有付款的总数结合起来，以表示客户的帐户余额.
客户账户余额是发票总和减去付款总和.由于这些将在单独的表中，因此您需要将它们与 UNION 组合，方法是使一个字段为负以让总和获得未结金额或使用"相反值"的虚拟列.要获得两个字段，您可以减去:
问题描述
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.推荐答案
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
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.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:
Expand|Select|Wrap|Line Numbers
更多相关问答
联系站长