Results 1 to 1 of 1
Thread: Nested query (Access 2000)
2002-01-21, 12:56 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
Nested query (Access 2000)
I have a query giving the results of the sales for each month.It consists if two coloumns: month name and sum of liters.The query is the following:
SELECT Format([InvoiceDate],"mmmm") AS MonthName, Sum(qrySalesBranchBulemsSmall.SumOfliters) AS SumOfSumOfliters
GROUP BY Format([InvoiceDate],"mmmm");
However, in order to build this query, i have to base my query on a preliminary query called
My question is, can i do it with a nested sql clause,with only ne query,since in this way i can save a lot of work. If i put additiional coloumns in the Grid, as invoice date, afid and size, then my query contains many rows for january etc, and not just one, as in the firts case.Here is the trial query i have made:
SELECT Format([InvoiceDate],"mmmm") AS MonthName, DatePart("m",[invoicedate]) AS MonthNumber, Sum([order details].liters) AS SumOfLiters, orders.invoicedate
FROM (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID
GROUP BY Format([InvoiceDate],"mmmm"), DatePart("m",[invoicedate]), customers.afid, orders.paymentid, orders.invoicedate
HAVING (((customers.afid)=1) AND ((orders.paymentid)>0) AND ((orders.invoicedate)>#1/1/2001#));
So in few words, can i make a query with only one row for each month, like the first case,
but with the restriction for: afid( which means affiliateID), paymentID and InvoiceDate.?
If i succed to build only one SQL clause instead of 2 queries, i will save a lot of addional queries.
I will be extremely grateful for any help in this respect.