SQL逻辑帮助 [英] SQL Logic Help
问题描述
我一直在敲打这个2天，我可能只需要一双新的眼睛。此查询列出了POSCat中列出的所有销售类别，更具体地说是POSCat.Description。然后
总结这些类别，因为它们出现在销售历史表中
InvLine。我喜欢这个布局的是，即使一个类别的null
总和值仍然会被列出，我可以使用
这样的东西来制作它$ 0.00。
选择POSCat。*，（从InvLine中选择Sum（InvLine.Price）
POSCat.Description = InvLine.Cat）作为Totals
来自POSCat
记录集看起来像这样
cat1 sumcat1
cat2 sumcat2
现在虽然它在某些级别上有效，但非常有限，只能给表格总计
。我真正需要的是通过比较发票总额与支付的发票来验证已经支付了
项目，
也可以按日期搜索。我想出的是：
SELECT InvLine.Cat，SUM（InvLine.Price）AS总计
来自付款
INNER JOIN（（InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId）
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId）ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
＃1/2/03＃AND＃1/2/03＃
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
如果只有那些<中的值，则记录集看起来像这样br />
类别1,4,5
cat1 sumcat1
cat4 sumcat4
cat5 sumcat5
除了它只列出不是
null的类别之外哪个很好用，但我想列出所有这些类似第一个查询的内容。
我正在寻找有关如何将2个查询合并在一起的帮助，以便我可以列出所有类别，但也可以按日期搜索a并确保
Invoice.Total = Invoice.Paid
提前谢谢
0.00。
选择POSCat。*，（从InvLine中选择Sum（InvLine.Price）
POSCat.Description = InvLine.Cat）作为总计
来自POSCat
记录集看起来像这样
cat1 sumcat1
cat2 sumcat2
现在虽然它在某些级别上有效，但非常有限，只能给表格总计
。我真正需要的是通过比较发票总额与支付的发票来验证已经支付了
项目，
也可以按日期搜索。我想出的是：
SELECT InvLine.Cat，SUM（InvLine.Price）AS总计
来自付款
INNER JOIN（（InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId）
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId）ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
＃1/2/03＃AND＃1/2/03＃
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
如果只有那些<中的值，则记录集看起来像这样br />
类别1,4,5
cat1 sumcat1
cat4 sumcat4
cat5 sumcat5
除了它只列出不是
null的类别之外哪个很好用，但我想列出所有这些类似第一个查询的内容。
我正在寻找有关如何将2个查询合并在一起的帮助，以便我可以列出所有类别，但也可以按日期搜索a nd确保
Invoice.Total = Invoice.Paid
提前谢谢
< blockquote>你可以将类别外部加入到有问题的查询中，这样所有类别都会显示出来，无论他们是否在你的其他查询中都有相关记录
。然后你可以使用
NZ（[SomeField]）将null格式化为零
正确的确定我得到了一点点使用Inner Join的隧道视觉，我是一个新手，所以你将如何添加外部联接到这个
查询。我很抱歉成为一个痛苦，但你能告诉我这个问题吗？我想我可以使用这样一条线，假设我的例子是你的b $ b意味着什么。看到我的问题是我需要从我的内部加入付款然后
POSCat在我的外部我怎么能这两个？
来自POSCat LEFT OUTER加入POSCat.Description = InvLine .Cat
SELECT InvLine.Cat，SUM（InvLine.Price）AS Total
来自付款
INNER JOIN（（InvLine INNER JOIN Invoice ON） Invoice.IId = InvLine.IId）
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId）ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
＃1/2/03＃AND＃1/2/03＃
GROUP BY InvLine。 Cat
订购InvLine.Cat Asc
I have been banging my head on this one for 2 days I might just need a
fresh set of eyes. This query lists all of the sales categories which
are listed in POSCat, more specifically POSCat.Description. It then
sums these categories as they appear in the sales history table
InvLine. What I like about this layout is that even if there is a null
sum value for a category it still will be listed and I can use
something like iif to make it $0.00.
Select POSCat.*,(Select Sum(InvLine.Price) From InvLine Where
POSCat.Description = InvLine.Cat) as Totals
FROM POSCat
The recordset looks like this
cat1 sumcat1
cat2 sumcat2
Now although that works on some levels its very limited and only gives
me a total for the table. What I really need is to verify that the
item has been paid for by comparing the invoice total to invoice paid,
also be able to search by date. What I came up with is this:
SELECT InvLine.Cat, SUM(InvLine.Price) AS Total
FROM Payments
INNER JOIN ((InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId)
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
The recordset looks like this if there was values only in those
categories 1, 4, 5
cat1 sumcat1
cat4 sumcat4
cat5 sumcat5
Which works great except it only lists the categories which are not
null but I would like to list all of them like the first query does.
I am looking for help on how to merge the 2 queries together so that I
can list all categories but also search by date and make sure
Invoice.Total = Invoice.Paid
THanks in advance
0.00.
Select POSCat.*,(Select Sum(InvLine.Price) From InvLine Where
POSCat.Description = InvLine.Cat) as Totals
FROM POSCat
The recordset looks like this
cat1 sumcat1
cat2 sumcat2
Now although that works on some levels its very limited and only gives
me a total for the table. What I really need is to verify that the
item has been paid for by comparing the invoice total to invoice paid,
also be able to search by date. What I came up with is this:
SELECT InvLine.Cat, SUM(InvLine.Price) AS Total
FROM Payments
INNER JOIN ((InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId)
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
The recordset looks like this if there was values only in those
categories 1, 4, 5
cat1 sumcat1
cat4 sumcat4
cat5 sumcat5
Which works great except it only lists the categories which are not
null but I would like to list all of them like the first query does.
I am looking for help on how to merge the 2 queries together so that I
can list all categories but also search by date and make sure
Invoice.Total = Invoice.Paid
THanks in advance
you can outer join the categories to the query in question so that all
the categories show up, regardless of whether they have related records
in your other query. Then you can format the null as zero using
NZ([SomeField])
Right ok I got a little bit of tunnel vision using the Inner Join, I am
kind of a newbie to this so how would you add the outer join to this
query. I''m sorry to be a pain but could you show me givin this query
how I could use a line like this, assuming that my example is what you
meant. See my problem is I need FROM Payments on my Inner Join then
POSCat on my outer how can I do both?
FROM POSCat LEFT OUTER JOIN ON POSCat.Description = InvLine.Cat
SELECT InvLine.Cat, SUM(InvLine.Price) AS Total
FROM Payments
INNER JOIN ((InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId)
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
这篇关于SQL逻辑帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
