我的发票数据存储在一个SQL数据库中,我需要一个摘要查询来使用MS Access查询在一行中调出发票编号、PO编号、日期和发票金额.遗憾的是,客户PO号只在发票数据的一行上,并在查询结果中弹出如下所示.
Invoice Date | Invoice # | PO Number | Amount |
---|---|---|---|
8/11/22 | 12345 | NULL | $23.00 |
8/11/22 | 12345 | 456 | $00.00 |
我需要如下所示的输出:
Invoice Date | Invoice # | PO Number | Amount |
---|---|---|---|
8/11/22 | 12345 | 456 | $23.00 |
我的查询如下所示:
SELECT
[Invoice Date],
[Invoice #],
[PO Number],
FORMAT$(Sum([Amount])),'$#,##0.00') AS [Amount]
FROM [Invoice Details]
GROUP BY
[Invoice Date],
[Invoice #],
[PO Number]
HAVING
[INVOICE DATE] BETWEEN [8/11/2022] AND [8/11/2022]
ORDER BY
[INVOICE #]
当涉及到SQL查询时,我仍然是一个新手,我不确定我在这里错过了什么.如有任何帮助,我们不胜感激.