访问数据库的复杂查询设计。 [英] Complex Query design for access database.

问题描述

你好



我有一个三表，它包含数据和结构，如下所示。我的表数据结构很大，但我在这里只代表必填字段。



ProductMaster

Hello

I have a three table it contain data and structure like below.My table data structure is big but i represent here only required fields.

ProductMaster

ID	ProductName
1	Jens
2	T-shirt
3	Shirt
4	Cap

BiilMaster

BiilMaster

ID	Invoiceno	Date
1	INV001	     19/9/2013
2	INV002	     20/9/2013
3	INV003	     20/9/2013

InvoiceMaster

InvoiceMaster

ID	Invoiceno	Productid	Qyt
	INV001	           1	         2
	INV001	           2	         3
	INV001	           4	         1
	INV002	           2	         1
	INV002	           3	         2
	INV003	           1	         3
	INV003	           4	         2

现在我想要这样的输出



SalesReport（任意两个日期之间的每日）

Now i want output like this way

SalesReport(Daily between any two date)

ProductName	TotalSales	Date
Jens	            2	      19/9/2013
T-shirt	            4	      19/9/2013
Shirt	            2	      19/9/2013
Cap	            1	      19/9/2013
Jens	            3	      20/9/2013
T-shirt	            0	      20/9/2013
Shirt	            0	      20/9/2013
Cap	            2	      20/9/2013

i尝试了许多加入查询，但我没有成功。我收到错误或错误的数据选择。

请告诉我如何解决这个问题。

推荐答案

选择productMaster.productname，sum（qty）as totalsale，billmaster .date



from productmaster inner join productmaster.productid = invoicemaster.productid



billmaster.invoiceno内部加入billmaster



group by productmaster.productname，billmaster .date

select productMaster.productname,sum(qty) as totalsale,billmaster .date

from productmaster inner join productmaster.productid=invoicemaster.productid

inner join billmaster on billmaster.invoiceno

group by productmaster.productname,billmaster .date

我自己解决了。

为了计算每天的总销售额，我必须按日期对数据进行分组。对于指定期间，

i必须使用having子句。

I solve it my self.
In order to compute total sales per day, i have to group the data by date. For specifying period,
i have to use the having clause.

SELECT product_master.ProductName, Sum(billDetails.Qty) AS SumOfQty, bill.Dated
FROM bill 
INNER JOIN (
product_master INNER JOIN billDetails ON product_master.ID = billDetails.ProductId
) ON bill.InvoiceNo = billDetails.InvoiceNo
GROUP BY product_master.ProductName, bill.Dated
HAVING bill.Dated Between #9/19/2013# And #9/20/2013#
ORDER BY bill.Dated, product_master.ProductName

什么是错误

将它贴在这里

What is the error
will paste it here

这篇关于访问数据库的复杂查询设计。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！