Complex Query design for access database.

Question

0.00/5 (No votes)

See more:

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

SQL

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

BiilMaster

SQL

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

InvoiceMaster

SQL

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

Now i want output like this way

SalesReport(Daily between any two date)

SQL

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 tried many join querys but i do not succeed . i get error or wrong data selection .
please tell me how to solve this one.

Posted 6-Oct-13 22:53pm

Archit Patel

Updated 6-Oct-13 23:16pm

v7

Add a Solution

Comments

BulletVictim 7-Oct-13 5:12am

Could you post your join queries?

[no name] 7-Oct-13 5:15am

it's something like this

select
product_master.*,
invoicemaster.*,
BiilMaster.*
from
product_master,
invoicemaster,
BiilMaster
where
product_master.ID=cint(invoicemaster.Id)

BulletVictim 7-Oct-13 5:39am

try this one.

SELECT
[PM.ProductName], [SUM(IM.Qyt)], [BM.Date] FROM
[ProductMaster] AS PM
INNER JOIN
[InvoiceMaster] AS IM
ON
[PM.ID] = [IM.Productid]
INNER JOIN
[BillMaster] AS BM
ON
[IM.Invoiceno] = [BM.Invoiceno]
WHERE [BM.Date] Between [Date1] and [Date2]

[no name] 7-Oct-13 5:50am

no it's also not work

3 solutions

Solution 1

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

Posted 6-Oct-13 23:24pm

Atul Pachori

v2

Comments

[no name] 7-Oct-13 5:32am

It given syntax error to me.

Solution 2

What is the error
will paste it here

Posted 7-Oct-13 0:34am

Atul Pachori

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Archit Patel · Accepted Answer · 2013-10-07T02:05:00

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.

SQL

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