BAQ - parts with most recent job # (if exists)?

(Adam) #1

I’m trying to make a BAQ that displays certain part numbers (active, standard cost), but would like to display the most recent completed job number.

In BAQ designer, what connections do I need to make to get the job number (if one exists… if not, return blank)? Connect Part to JobHead somehow?


(Brandon Anderson) #2

Do you want parts that are in a job? Or a job that made that specific part? For the first, it would be either the JobMtl table, or the JobAsm table (you’ll need a union for that probably), and for the latter it would be the JobHead table.

You’ll probably need to do a subquery where you get the part number from the job table and group by it, and use a calculated field to get the latest required by date. Add in the job number. Then feed that sub query up the top level with your part table and join by part number, and you should have every part number, with the last job it that made it/or was used on depending on what you are looking for.

Just curious, are on E10 or E9? Your profile says 9, but you posted this in 10. I don’t know how to make queries in 9 so what I’m saying may not be valid if you are on 9.

Secondly, if you are on 10 and new to BAQ’s and don’t understand what I am saying, let me know. What you are asking for is relatively simple so I can make up some screen shots to walk you through it if you need them.