We are trying to measure our quote accuracy against the actual results from a job. Most of what we do is build to order, so none of the parts we make are in inventory… I am working on a BAQ that joins the OrderRel table to the QuoteQty table. The trouble is selecting the QuoteQty that was used to put together the job details. ex: we have a part with quoted quantities of 1, 5, 10, and 50. The customer orders 7, so the 5 PC price is what gets used to build out the job details. I need to get some other information out of the quoteqty table. I can sort of do in Excel, but would prefer to have it joined up in a single dashboard table.
Any idea on how to do this? All I would need is to get the QuoteQty.QtyNum field from the OrderRel table.QuoteNum and QuoteLine are part of the OrderDtl table, and OrderRel.OurReqQty is the other key field I need to use.
make the quote data a subquery and group on quotenum, quoteline, partnum, quote quantity. Then in the top query if you are sure you will want the first result where quantity is equal to or less than the sales quantity.