Complex Query design for access database

Question

I have the following three tables (only showing the required fields for representation reasons).

Table 'product_master'

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

Table 'bill'

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

Table 'billDetails'

ID  Invoiceno   Productid       Qyt
1   INV001             1             2
2   INV001             2             3
3   INV001             4             1
4   INV002             2             1
5   INV002             3             2
6   INV003             1             3
7   INV003             4             2

The output I want

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

This query didn't work:

        SELECT
        [PM.product_master], [SUM(IM.Qyt)], [BM.Date] FROM
        [product_master] AS PM
        INNER JOIN 
        [billDetails] AS IM
        ON
        [PM.sno] = [IM.prod_sno]
        INNER JOIN
        [bill] AS BM
        ON
        [IM.Invoiceno] = [BM.Invoiceno]

Answer 1

Now i want output like this way SalesReport(Daily between any two date)

In order to compute total sales per day, you will have to group the data by date. For specifying period, you will 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

Answer 2

Try this query

select product_master.ProductName
       ,SUM(billDetails.Qyt) as TotalSales
       ,bill.Date 
from billDetails
inner join bill on billDetails.Invoiceno = bill.Invoiceno 
inner join product_master on product_master.Id = billDetails.Productid