Creating a query to filter customers

[jakeman]

Hi All - I have a working query that allows me to filter out customers who have less than $60,000 in sales. However, I needed to add a Period field to the Query to see sales in each of the last four years. The only thing now is that using a simple filter in my query after adding the Period field, doesn't work for me anymore.

For example, CUSTXYZ has sales like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]customer
[/TD]
[TD]period
[/TD]
[TD]sales
[/TD]
[/TR]
[TR]
[TD]CUSTXYZ
[/TD]
[TD]2014
[/TD]
[TD]$62,000
[/TD]
[/TR]
[TR]
[TD]CUSTXYZ
[/TD]
[TD]2015
[/TD]
[TD]$86,000
[/TD]
[/TR]
[TR]
[TD]CUSTXYZ
[/TD]
[TD]2016
[/TD]
[TD]$92,000
[/TD]
[/TR]
[TR]
[TD]CUSTXYZ
[/TD]
[TD]2017
[/TD]
[TD]$72,000
[/TD]
[/TR]
[TR]
[TD]CUSTXYZ
[/TD]
[TD]2018
[/TD]
[TD]$52,000
[/TD]
[/TR]
</tbody>[/TABLE]

When I apply my Criteria to the Sales field (>=60000), sales for period 2018 gets eliminated. Is there a way that allows me to filter out records in my query where the combined sales for a customer over a 4 year period are less than $60,000?

 

[AlexB123]

I created a small test database, with two tables: tblSales and tblCustomer. tblSales was as your example, with a second company, COMPANYSTU, with sales over the period < 60,000. First, I rewrote your query to get a similar join.

SELECT [tblSales].[Posting Year],
 [tblSales].[Cust Nbr],
 [tblCustomer].[Cust Name] AS [Cust Name],
 [tblSales].Sales
FROM [tblSales] INNER JOIN [tblCustomer]
 ON [tblSales].[Cust Nbr] = [tblCustomer].No_;

Then, I used those results to calculate the sum over the period, then grouped by Cust Nbr.

SELECT  SUM(A.Sales), A.[Cust Nbr] FROM (SELECT [tblSales].[Posting Year],
 [tblSales].[Cust Nbr],
 [tblCustomer].[Cust Name] AS [Cust Name],
 [tblSales].Sales
FROM [tblSales] INNER JOIN [tblCustomer]
 ON [tblSales].[Cust Nbr] = [tblCustomer].No_) as A
GROUP BY A.[Cust Nbr]
HAVING SUM(A.Sales) >60000 
;

I then saved that the above query as Query2, and created another, Query 3, to join back to tblCustomer to get the names. I used the Design View for this step (Note my earlier suggestion, build step by step with Design View, then copy and paste into the criteria fields). The calculated sql was:

SELECT tblCustomer.[Cust Name], Query2.Expr1000
FROM Query2 INNER JOIN tblCustomer ON Query2.[Cust Nbr] = tblCustomer.No_;