CODEWITHSUNDEEP
ms-access
Anchor Man
Anchor Man

Reputation: 1

Sum of orders Based on Customer Name

I have a problem with trying to generate a new query based on the total number of orders by customer.

I have tried going on the internet and found several solutions, but I can't seem to solve it.

I only require to access two columns which are from two different tables The tblCustomers which has the Customer Name and the tblOrder Line Item which has the quantity

tblCustomer

Customer ID, Customer Name, ... etc .. etc

And the following tblOrder Line Item

Order Number, Product ID, Quantity,...etc, ...etc

Query and Database relationship

Query Result

As you can see on the right, the total order should be sorted by customer. A running sum based on each customer name. I generally use the expression builder for most of the SQL queries.

I have tried using a DSUM, but it was on a specific table, however my customer name is in the tblCustomers whereas my quantity of orders are in the tblOrder Line Item.

The other method of using a running sum failed, unsuccessfully, as I can't seem to insert a nested Select query into my original query.

Does anyone know a method to solve this predicament.

//////Update/////

This is the Sql code

SELECT 
    tblCustomers.[Customer Name], 
    [tblOrder Line Item].[Order Number], 
    tblProducts.[Product Description], 
    [tblOrder Line Item].Size, 
    [tblOrder Line Item].Color, 
    tblProducts.Price, 
    [tblOrder Line Item].Quantity,
FROM 
    tblProducts 
INNER JOIN 
    ((tblCustomers INNER JOIN tblOrders ON tblCustomers.[Customer ID] = tblOrders.[Customer ID]) 
INNER JOIN 
    [tblOrder Line Item] ON tblOrders.[Order Number] = [tblOrder Line Item].[Order Number]) ON tblProducts.[Product ID] = [tblOrder Line Item].[Product ID]
GROUP BY 
    tblCustomers.[Customer Name], 
    [tblOrder Line Item].[Order Number], 
    tblProducts.[Product Description], 
    [tblOrder Line Item].Size, 
    [tblOrder Line Item].Color, 
    tblProducts.Price, 
    [tblOrder Line Item].Quantity;

I am trying to another select with 2 inner joins. But I can't seem to insert the code into the above SQL code.

This is the inner join I am thinking to add

(SELECT [Quantity] 
 FROM [tblOrder Line Item] c,  
 INNER JOIN [tblOrders] o on c.Order Number = o.Order Number 
 INNER JOIN [tblCustomers] a on o.Customer ID = a.Customer ID)

Upvotes: 0

Views: 617

Answers (1)

mazoula
mazoula

Reputation: 1321

It is unclear what running sum you mean but assuming a running sum of quantity as order number increases for each customer. We can use either Dlookup or a correlated subquery for the running sum.

First Foreign Keys are not Primary Keys. You do not have to remove the space in the variable names. I just couldn't get the sql with all the inner joins to work and thought the space might be the problem.

enter image description here

enter image description here

Working from qryCustomerOrderLineItems and using Dlookup for the running sum:

enter image description here


Total: Min(DSum("Quantity","qryCustomerOrderLineItems","OrderNumber<= " & [OrderNumber] & " AND CustomerID= " & [CustomerID]))
'to group by price or size just expand the where clause of the DSUM
'min, max, first, and last are equivalent for grouping 1 number
' in the totals row access changes summary functions like min to expression by moving them inside

You can also grab everything from qryCustomerLineItems and or use a correlated subquery for the running sum

enter image description here

Total: (SELECT Sum(Quantity) FROM [qryCustomerOrderLineItems] AS q WHERE q.OrderNumber <= [qryCustomerOrderLineItems].[OrderNumber]
AND q.CustomerID=[qryCustomerOrderLineItems].[CustomerID])

Both queries give the same result:

enter image description here

Upvotes: 0

Related Questions