I am trying to create a BAQ to sum the invoice totals for each customer. I can not get the sum feature to work. I am sure that I am missing something… I would appreciate any help.
Userlevel 3
In the formula, remove the “InvoiceHead.CustNum”. Save the formula. Now back on Display Fields, click the check box in the “Group By” column for CustNum. Remember that once you do a group by, every field EXCEPT formulas need to be part of the group by. If you want to display additional customer information, make what you have a subquery, then make a top query and join your subquery with the customer table to get the rest of the fields. Otherwise, you’ll have to make all those extra fields part of the group by.
Thanks Fred! I still can’t get it to work. Take a look at these two screen shots and see if you see an issue.
Userlevel 2
You’ll need to check the group by on all fields except your aggregate calculated fields. In this case every field except your invoice total.
For a more complicated query where you don’t want to check all the fields as group by, you can put the invoice header and calculation in a subquery. As luck would have it, I’m giving a session in a webinar on using subqueries for this type of thing this Friday. Let me know if you’re interested and I’ll get you an invite.
Thanks,
Joe
Userlevel 3
Yup, what jtrent said and what I put in my post. Once you have a “group by”, everything in that query has to be either a calculated field or also click the group by.
Now I see what you are saying!!! That was easier than I thought! Thanks for all the help.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.