Hello all,
I’m creating a dashboard with a few tabs. I thought this particular one was fairly simple, but it’s not returning correctly. All I need are all customers with no invoices for the past two years. I’m clearly not entering the criteria correctly so if someone could point me in the right direction, I’d appreciate it! I’ll post my criteria below. I will post the questions I have on the other 2 BAQ’s separately.
Thank you!
Erin
Best answer by fred
View original
Couple ways to do this:
Either route should get you there.
Fred,
Thanks so much for your quick response! I’m modifying my query to match your first option, and I have done everything except “ Set the SubQuery Criteria so that you are getting where InvcHead is null.”
How do I do that? Would I not already get that by making it a right join?
Hello Erin,
What you need to do is create a subquery with just the InvcHead table, where you query the count of invoice numbers (with your date criteria set on the table), grouped by CustNum. Then you bring that subquery to the top level and join it to the Customer table on the CustNum field. Set the join so that you retrieve all rows from the Customer table, and when you run the query, the customers with no invoices will have a NULL for the number of invoices field (see image). Then you can create a calculated field in the top level return 0 if the field is null, and the original value otherwise.
Let me know if you have any questions.
Thanks,
Matthew Morgan
DESHAZO Crane Company
The Subquery criteria is on the bottom of the canvas where you define table criteria and relations. It’s another tab.
You want to add a criteria where the subquery’s value is null so that you ONLY get customers who don’t have rows in the subquery. When they don’t have rows, the subquery’s values will be null. Easy way to get the “where they don’t have orders” that you are wanting.
Matthew,
Thank you for your response. I’m a newb and need things broken down at times. ;)
I have created my query but the customers with no invoices are displaying with zeroes already. They are not null. I am posting my BAQ in case you see something I’ve done wrong. If this is correct (and I’ve tested quite a few to check invoice dates to make sure they match with what I need) then I can filter out the non-zeroes in the db and I’m good. I do have one question however. I pulled up Customer Tracker and ran a search to see how many customers we had, and we have 3,960. So shouldn’t there also be 3,960 results in my query? How do I end up with 4,094?
Thanks to both of you, I think I finally have it!
Now to move on to my next queries - the trickier ones. I’m not very good yet with subqueries, so this should be an awesome learning experience!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.