Solved

Unique values from multiple results

  • 13 April 2023
  • 5 replies
  • 92 views

Hello,

 

This might be a simple question (I hope so anyway). I am trying to pull a ‘Items sold in the past ## days’. So I have the order header, order detail and part tables linked. I filter the header ‘orderdate >= 01/01/2023’

I am getting the desired results, however I am getting multiple items (as expected since we sell parts all day long every day).

How would I get the unique values only reported?

 

Thanks!

Arno

icon

Best answer by fred 13 April 2023, 21:50

View original

5 replies

Userlevel 3

Depending on what all is there, you can do a “distinct” to get distinct rows.  This is on the subquery options tab.

Userlevel 3

If you need to summarize all the items sold for a given day, either quantity and/of dollars, you can create a Subquery (type InnerSubQuery) that will summarize the sales quantity by item and the sales dollars by item.

You can then use this SubQuery as input to your Top-level query (it becomes another table you can link too) in the Top-level query.

I’ve used this technique to summarize data and then bring in the summarized data to be displayed in my Top-level query.  Gets rid of all the extra data you may not need.

Group by is the way to go. 

 

Example:

you can remove the orderdate if not wanting to see by day, remove the partnum if not wanting see to partnums

 

Select a.company, b.partnum, a.orderdate, sum( your qty field here)

from orderhed a 

left join orderdtl b on a.company = b.company and a.ordernum = b.ordernum

where a.orderdate >= your date here

group by a.company, b.partnum, a.orderdate 

 

Userlevel 4

Group By can also be used in the BAQ Designer; there are checkboxes on the field chooser form.

Good question, I’ve often encountered that in BAQ, so good to get some ideas here, thanks !

Reply