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.
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.
@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.
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.
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.
