BAQ: Questioning My Table Joins

This must get asked a lot and I apologize, I have no experience with SQL. I have to give the BAQ designer some credit, it’s very easy to use. I’ve written a couple reports before, but the one I’m writing now I am really struggling with.

I was asked to create a report that shows Estimated vs Actual labor hours so we can see our over/under on jobs. I also need to group it by the type of job it is, since it’s easier to understand why a job would go over if it is more complicated than most others.

Issue I’m having is I’ve added the tables I want to get the fields I need, I’m not seeing enough results and I’m not sure why. 18 results when there should be several hundred. It must be something simple, I’m just not sure if I’m missing a table, or if the table order matters, or if it’s strictly table joins.
2023-05-24 12_14_43-Business Activity Query Designer

You are doing an inner join on all your tables. Your result will only show where all of the items match each other.

Based on your tables you can say this.

You are showing only quotes that have only have jobs,
jobs that only have assemblies and jobs that are associated to orders that have a customer on them.

When you say 100’s what do you mean?

I’d start by changing all your joins to left joins. (i.e., Show all rows from ParentTable)

That’ll help you see which tables are limiting your results. My gut says it’s probably make-to-order vs make-to-stock jobs.

@knash Thank you for the explanation. Before I made this, I created a test BAQ added JobHead table, and just the JobNum display field to get an idea of how many records I should be seeing. It gave me 318 results. I did the same with QuoteHed and QuoteNum, 250 results.

@Waffqle_De_LaCroix I’ve done as you’ve suggested and now I’m getting 254 rows, which is great news, but the majority of the fields are blank except for the one being pulled from QuoteHed, which is the Estimator’s ID. I want to say the QuoteHed table is the limiting factor. This field was used on the original report I’m re-creating. I don’t actually have much use for QuoteHed here. If a job goes over on hours the person who did the quote estimation would certainly have nothing to do with it.
2023-05-24 13_04_05-Business Activity Query Designer

If I remove QuoteHed I get a bad SQL statement

That’s going to be a calculated field that is using a field from quote hed. You have to fix that.

1 Like

Estimator_c is a custom field we’ve added to QuoteHed. I understand that a report won’t run if there are calculated fields using tables that aren’t present. The only table I’m using in calculated fields is JobAsmbl.

Without seeing more of your query, I don’t know. We don’t have all of the peices.

I do know that this left join is going the wrong way. I’m assuming you want all jobs, and not all quotes.


Might be easier to show the query phase to hunt down the field.

Yes I do want all jobs. I’ll repost everything as it is now as I made some changes just trying to troubleshoot it.

2023-05-24 14_46_34-Business Activity Query Designer

Joining JobHead to QuoteHed feels strange. Try joining OrderDtl to JobProd then QuoteHed to OrderDtl.

OrderDtl.QuoteNum = QuoteHed.QuoteNum

Thank you for that, Zack, and thanks everyone else for all your advice. I wish I could mark everyone’s replies as solutions because you are all so helpful, but this one made me rethink the entire query. I rebuilt it from scratch and it is functioning as I expected now.

Here is what I ended up with:

2023-05-25 12_03_08-Business Activity Query Designer

1 Like