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]

What happens to the other year values?

It seems to me your Criteria ">=60000" is doing exactly what it is supposed to, as $52K < $60K.

 

[jakeman]

I think I didn't explain my requirement very well. I should say that yes, the above is working. My problem is that I do not want to exclude from my results any of the periods for a customer who has a combined sales total of >=60000 over a four year period. If over a four year period a customer does not have at least 60,000 in sales, I want to exclude those customers but for a customer that has more than 60,000 in sales, I want to show all of the periods. In the example above, 2018 is being excluded because it is less than 60,000.

 

[AlexB123]

I'm not very good with using the regular Design View ... if you can post the "SQL View" code of your query I would be happy to look that over, see if I can assist.

 

[AlexB123]

I think you're problem is that you need a sub query, as opposed to just using a single criteria in your sales column.

Off the top of my head, in SQL, it would look something like:

SELECT * FROM tblExample
WHERE 60,000 < (SELECT SUM(SALES), CustomerID
FROM tblExample
GROUPBY CustomerID)

So you need the total sum of sales for each Customer first, then use that result as your "filter". To do this without SQL, first create another query using your Query Design view and find the sum for all periods. Once that query returns the results you need, switch to SQL View, copy the code, and right click and paste under "Criteria" of the query you want to use to find your final answer (add the "60,000 < ").

If you had more than four years of data, you would have to add another condition to your sub query so that you only get every four years ... not sure at the moment how I would do that.

 

[AlexB123]

If you wanted to do it by year ... what I would do is make a spreadsheet. Your first column would be year, starting with the oldest year or period you have, and then each row under put the successive year for as many years as you'd foreseeably need. Your second column I would name something like "yearID", and manually key integers every four years. So 1, 1, 1, 1, 2, 2, 2, 2, .... etc. When done, I'd import or link the table, then add a new column to your table of interest, and finally update your table of interest with the new data.

UPDATE tblLinkYear
INNER JOIN tblExample on tblLinkYear.Year = tblExample.Period
SET tblExample.yearID = tblLinkYear.yearID

***Something like that****

Then in the subquery, instead of grouping just by sales it'd be:

SELECT SUM(Sales) as Criteria, yearID, Customer
FROM tblExample
Group By 1,2,3;

You'll have to play with the queries ... Definitely still a learner. But I'm sure you can do them all in the Design View to get it just right.
​

 

[jakeman]

Thanks, Alex, for taking a stab at this. So here is my SQL code:

SELECT [Customer Sales - 2014 to 2018].[Posting Year], [Customer Sales - 2014 to 2018].[Cust Nbr], [dbo_Stuart Hose & Pipe, Ltd_$Customer].Name AS [Cust Name], [Customer Sales - 2014 to 2018].Sales
FROM [Customer Sales - 2014 to 2018] INNER JOIN [dbo_Stuart Hose & Pipe, Ltd_$Customer] ON [Customer Sales - 2014 to 2018].[Cust Nbr] = [dbo_Stuart Hose & Pipe, Ltd_$Customer].No_;

Not sure where the placement of the subquery would go in the above.

 

[welshgasman]

I think your problem is the 4 year period.
Between 2018 and 2015 their might not be $62K, but between 2017 and 2014 there would be.?

So you need to get that query worked out, then join it back to the table.
However if it is not a rolling 4 year period, just sum the last 4 records for each customer or use a date range.

 

[jakeman]

Hi welshgasman - so I'm concerned with the entire period (2014 through 2018) and what the combined sales for that entire period is. If sales from 2014 to 2018 for a customer is less than $60k, I want to exclude them from my list. Given that, I'd like to know how to construct my query.

 

[welshgasman]

Not being an expert myself, I always break this down.

So something like this, gets me the IDs of >=60000 and the last 5 years (2014 would be 5 years, so adjust accordingly)
I would also include the values as a check before removing them so I only have the ID.

First query with checks

SELECT GMPData.[Sales Ref], Sum(GMPData.Value) AS SumOfValue, GMPData.[Date Submitted]
FROM GMPData
WHERE (((Year([Date Submitted]))>=2014))
GROUP BY GMPData.[Sales Ref], GMPData.[Date Submitted]
HAVING (((Sum(GMPData.Value))>=60000));

Final first query

SELECT GMPData.[Sales Ref]
FROM GMPData
WHERE (((Year([Date Submitted]))>=2014))
GROUP BY GMPData.[Sales Ref], GMPData.[Date Submitted]
HAVING (((Sum(GMPData.Value))>=60000));

I would then probably save that as qryLast4Years and then join in another query

SELECT DISTINCT qryLast4Years.[Sales Ref], GMPData.[Client Name]
FROM qryLast4Years INNER JOIN GMPData ON qryLast4Years.[Sales Ref] = GMPData.[Sales Ref];

Essentially get the summing query correct, that then acts as the criteria for the second query.

I tend to do most of my query work like this in the query IDE and perhaps tweak in the SQL view.

HTH

Hi welshgasman - so I'm concerned with the entire period (2014 through 2018) and what the combined sales for that entire period is. If sales from 2014 to 2018 for a customer is less than $60k, I want to exclude them from my list. Given that, I'd like to know how to construct my query.