You have to stop losing the ring I gave you!!
Clipped and sent to the ole Obisidian Vault this time! Thanks again, I did forget that.
Thanks folks, it is going to take me a fair bit of time to understand and digest these replies.
OK so doing a little bit of digging, I can join GAJrnDtl to TranGLC on ABTUID as discussed and I have then found that the field TranGLC.GLAccount brings up a format of
010100|00|||27238|2723801
and from looking into this end number, 2723801 I find it is the Job Number.
I also find that TranGLC.Key1 is the Invoice Number.
perhaps I may be able to use this to link to the Custom table we have for QualityInspection to get the FinalInspection Completed Date.
I used this to get Job Number:
RIGHT(TranGLC.GLAccount, CHARINDEX(â|â, REVERSE(TranGLC.GLAccount)) - 1)
Just a âwarningâ.. baqs that tie things between jobs, parttran, and the GL can sometimes be the biggest problems in crashing out⌠why? because the sheer volume of records. You need to be very selective and limit the BAQs to small date ranges so that the number of records that need to be matched are limited. otherwise you will get sql timeouts.
Yeah, as I am discovering as I try to add this to my main BAQ.
I need to do a bit more work with it for sure.
personally, i have written 1000s of BAQs, created 100s of dashboards⌠and ONE TIME (over 15 years ago) I attempted to create a BAQ that linked to the GL⌠I decided that it was not really needed. The GL is a summary of what happened. The details are stored in various places such as PartTran, LaborDtl, etc.. when I was a consultant, i always drove my customers to trust the system, use the detail data for detail, use the GL for the financial reporting.
Well I wish it was my choice but Accounts asked for specific fields and the one that is causing all the issue is this one.
I donât know why it is so important 
have you tried using the Chart Tracker functionality? it supports accountants looking at specific gl accounts and then you can drill into the details and see everything that hit that account. This does not need any BAQs becuase it is built into the software.
Another tool, is the Invnetory/WIP Reconciliation Report⌠it allows you to look at the GL by job, or to look at a specific GL account and see what hit it⌠or by transaction type, etc⌠it is very detailed.
Strangely enough it was from the chart tracker that the request started originally.
Finally got there but on this occasion it was simplifying that got me what I need.
I had spent a lot of time trying a subquery with links to JobProd or ProdGrup or Project or InvcHead tables etc but going back to my original query and just adding the custom quality inspection table and then a criteria on it FinalInsp = Yes - boom, cut the lines down and only showed one date per job for QualityInspectionHead Completed Date.
Learned a lot on the way so thanks again for all the input! 