Day of brain death, i swear. testing a very small query to get my logic straight before rolling it into a larger BAQ.
One table OrderDtl - just displays OrderNum, LineNum, PartNum. i am trying to make a subquery with the OrderRel table. i have an order that has two open releases. one release is a partial ship and the other is open
order 11111, line 1, release 1, openqty is 106 (ourreqqty-ourstockshipqty)
order 11111, line 1. release 2, open qty is 200.
i want to pass the value of 306 to the main query and i keep getting both release lines showing up in the main query. i have tried creating a calc field to get the 106 and 200 and then another calc field to sum the those numbers. i have also tried just a sum of the (ourreqqty-ourstockshipqty).
i have joined the subquery (that has just OrderRel) to the OrderDtl main query by order & line. with inner, and outer joins. makes no diff. i have tried various group by’s with no luck. i am sure this is so stupidly simple, and am embarrassed to post this, but i am i am in short-circuit mode.
You are so kind to post back so quickly. the first is the subquery that will throw errors if i don’t group by everything but calculated fields.
the main query throws error unless i add part number and calc field from subquery to the group by field, and then i am back to two lines for a reult
Group by will group by everything that’s displayed, so you can’t group by order and line, then also display the release. How would you imagine that would be displayed in the grid?
Let’s back up, you are trying to show the SUM of 2 releases in your query (I think) but the query you have written is “By Release” meaning 1 Row per Release.
That is not really possible unless you do something like a single subselect but then you’ll end up with 2 rows one for each release each of which says 302
Can you explain a little more what you are trying to accomplish?
I am attempting to get a list of order number, line number, part number, (from main query of OrderDtl) main query and total quantity outstanding from OrderRel subquery. i had thought the best way to do this was have a subquery of the OrderRel table that calculated what was open on each release line, sum that, and just pass the single number to the main query that has other fields.
thank you all for your input. this is just a fail from the start.my final output should be
order#, Line#, part#, total open. i don’t get why i cannot make this work and i will try again tomorrow.
i was making this so over-complicated. a good night’s rest and the subQ is just what you suggested… i then take that total and do the math in an upper level query and get the reult i am expecting. now i will roll it into the main BAQ and see what happens