Kamis, 09 Agustus 2012

RE: [MS_AccessPros] Daily Sales by query

 

Abdul-

Try this:

SELECT tblOrders.CustomerCode, tblOrders.InvoiceNumber, tblOrders.InvoiceDate,
Sum([tblOrder Details].Quantity) As QtySold, Sum(([tblOrder Details].Quantity *
[tblOrder Details].Rate) + [tblOrder Details].SalesTax) As Sales
FROM tblOrders INNER JOIN [tblOrder Details] ON tblOrders.OrderID =
[tblOrder Details].OrderID
GROUP BY tblOrders.CustomerCode, tblOrders.InvoiceNumber, tblOrders.InvoiceDate;

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Abdul Shakeel
Sent: Thursday, August 09, 2012 9:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Daily Sales by query

Dear All

I have the following query, I want to get customer wise daily sales report
having fields

1. Customer Code
2. Invoice Number
3. Invoice Date
4. Qty Sold
5. Sales

but when I run this query it not give me the total for all the field, I
have tried Totals

SELECT tblOrders.OrderID, tblOrders.CustomerCode, tblOrders.InvoiceNumber,
tblOrders.InvoiceDate, [tblOrder Details].Quantity, [tblOrder
Details].Rate, [tblOrder Details].SalesTax, ([Quantity]*[rate])+[SalesTax]
AS Sales
FROM tblOrders INNER JOIN [tblOrder Details] ON tblOrders.OrderID =
[tblOrder Details].OrderID;

--
Regards,
Abdul Shakeel

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar