Creating a query to filter customers

jakeman

Active Member
Joined
Apr 29, 2008
Messages
325
Office Version
  1. 365
Platform
  1. Windows
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:
customer
period
sales
CUSTXYZ
2014
$62,000
CUSTXYZ
2015
$86,000
CUSTXYZ
2016
$92,000
CUSTXYZ
2017
$72,000
CUSTXYZ
2018
$52,000

<tbody>
</tbody>

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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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.

Code:
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:

Code:
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.
 
Last edited:
Upvote 0
Thanks, Alex, for taking a stab at this. So here is my SQL code:

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
Code:
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
Code:
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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top