try changing the join from the invoice to part.
Also, do all of your parts have the reference category populated? If not you should get 14 lines. 13 cat’s and 1 blank.
I have some blank, thought I got them all, but didn’t. Anyway, tried that, same info. BTW, what version are you running? Your join type is a little different to mine, although same info.
10.1.600. They changed it from left/right/inner/outer, to what you see in my screen from 10.0 to 10.1. Does the same thing.
This has me puzzled. I’m thinking you should get 0’s for everything, or no 0’s. Some 0’s isn’t right.
hmmm
Here’s the problem. You probably have line in there with no customer? That’s the line for the include all. The customer is blank on that line, not duplicated. We need to find a way to reference the customer to the reference category
The other approach would be to do the count on each row with a case statement for each reference category, like I outlined above.
1 Like
Interesting…When I don’t want it to duplicate it does, and when I want i to add the line, it won’t! LOL!!! Guess time to do some case statements. I don’t know anyway to reference the customer when there is no order for that part.
If you want all Customers with a list of all RefCategories, start with the Customer table linked to the RefCategory table joined by Company field only in the Top Level Query.
Next, build a subQuery grouped by RefCategory and CustNum with a count of the InvcDtl.InvoiceNum lines.
Set the InvcDtl_CustNum field from the subQuery to the Customer.CustNum field
That last piece about having customer and refcategory linked by company only did the trick. Thanks to @Banderson and @knash I was able to get most of the way there. Took notes, so next time should n’t be so hard. LOL! Thanks everybody!!