Hello,
I got internally promoted to a new position and I’m slowly learning from some non-techsavy older gentlemen. I’m trying to create my first BASIC BAQ report. I have it mostly figured out…I want it to pull part numbers, part descriptions, unit weight, unit cost, most up-to-date supplier, and OVERALL quantity of each site.
We have two sites. E & B. The way I have it set up now, it’s pulling the quantities of every bin each part number is located in in each site, so my results have multiple entries for the same part number. In reality, there should be two results per part number: Quantity for Site E and Quantity for Site B.
I’ve dabbled a little in creating a subquery for it but i’m getting lost on calculated fields, the “HAVING vs. WHERE”, and how to insert my subquery into my main query.
Forgive me if this is a simple problem to fix, like i said earlier, i’m new and pretty much teaching myself.
Any input would greatly be appreciated!!! Thanks in advanced!
@jpdegen Welcome. I think you need a sub query for PartBin grouped by Company and PartNum to get the quantities in a single row and then join that back to the main by Company and PartNum.
Please also remember to only write in a dev or test instance as a query gone wild can cause issues in a live system.
Make sure you’re grouping by site (or, technically, warehouse). There should be a checkbox for that in the Display Columns page.
All the calculated fields just have to be SUM(dbField).
If you’ve got multiple warehouses per site, then you’ll have to create a calculated field that uses a CASE statement and group by that instead of the db field.
Welcome aboard John! You’ll find plenty of helpful interaction here as you dive in!
Depending on where your unit weight and cost is stored you should just need Part and PartWhse tables … group by the 5 other fields then your calc field is sum(PartWhse.OnHandQty)