Exact and/or Closest Match (1 Viewer)

[ed coleman]

I am trying to supply my sales manager with a report that will compare unit selling price for what was quoted versus what was actually billed.

Our quote table is based on items and these can be quoted at various levels of production, e.g. for 100 units, 300 units, 500 units, etc..

I have produced a query that will get the actual item, the order number and the quantity ordered that was shipped for that month.

Now, what I am having trouble with is getting an exact or a closest, but lower, match based on volume ordered versus volume quoted. I have attached an Excel sheet to give the two queries - one is the source query and one is the lookup query. The results expected are shown across from the source query. All would be fine if all orders coming in would have a corresponding quote for that production, however, this is sometimes not the case.

The older I get, the dumber I get and any assistance would be appreciated.

Thanks

 

[CJ_London]

you need to use a subquery

SELECT *
FROM OrderQuery INNER JOIN QuoteQuery ON OrderQuery.item=QuoteQuery.item
WHERE QuotePrice =(SELECT Max(QuotePrice) FROM QuoteQuery Q WHERE Item=OrderQuery.Item AND QuotePrice<=OrderQuery.Ord_Price)

 

[ed coleman]

Thanks CJ for your quick reply.

Two questions. One is what you mean by the "Q" in the attached code - " FROM QuoteQuery Q WHERE""

The other is where would I use this code? Can I use it in the design view or would I have to add it to SQL statement within the query?

Regards,
Ed

 

[kevlray]

The Q is an alias of the table QuoteQuery. Unfortunately Ed did not qualify any of the fields with the Q, so it was clear why he did it.

 

[CJ_London]

to complete Kevlrays comments - the why.

The why is because you need to refer to two instances of the same table one in the main query and one in the subquery, so they need to be aliased to differentiate them. I used 'Q' just to mean Query, but you can use anything you like - 'Elephant' for example

I often alias tables for a number of reasons, not just because it is necessary

reduces the amount of typing
makes the text of the query shorter and easier to read
can give meaning to the reason the table is in the query

 

[ed coleman]

CJ, I have tried using your code and get a syntax error in the sub-query.

Also, I looked at the way the matching should go to select the closest match and it has to be based on the closest quantities, not price. I have revised your code to try and address this but to no avail. I think the Q is doing me in. Not sure how to handle it. Also posted a revised example file with the actual table and query names and variable names.

Here is the code that I currently have as an output in the design view of the qryItemsForMatch.

ClosestMatch: SELECT *FROM [qryItemsForMatch] INNER JOIN [tblQuoteForPeriod] ON [qryItemsforMatch].[item] = [tblQuoteForPeriod].[item]
WHERE [Quote_Price] = (SELECT Max([Quote_Price]) FROM [tblQuoteForPeriod] Q WHERE [item] = [qryItemsForMatch].[item] AND [Qty] <= [qryItemsForMatch].[qty_ordered])

 

[CJ_London]

I provided a whole query, you have applied it as a column.

Subject to any name corrections, just copy and paste into the sql window

 

[ed coleman]

Hi CJ, used your code in the SQL window as you said and received an output.

I was expecting the number of rows to equal my qryItemsForMatch query but they were short.

I have attached a file and it looks to me like the code is going to the closest or exact match between ord_price and quote_price rather than Qty_ordered and Qty(quoted).

I am also getting duplicates in the output ie the same item and order number. When concatenated on the qryItemsForMatch query, there are no or should there be any duplicates.

Your assistance is muchly appreciated.

Regards
Ed

 

[CJ_London]

please clarify in simple English what the business rule is

As originally described it is

compare unit selling price for what was quoted versus what was actually billed....
find the exact or a closest, but lower, match based on volume ordered versus volume quoted

but lower - which? price or volume? - your example was showing price

 

[CJ_London]

I may have missed one of your comments to be based on quantity, not price

try

SELECT *
FROM OrderQuery INNER JOIN QuoteQuery ON OrderQuery.item=QuoteQuery.item
WHERE QuotePrice =(SELECT TOP 1 QuotePrice FROM QuoteQuery Q WHERE Item=OrderQuery.Item AND Qty<=OrderQuery.Qty_Ordered ORDER BY Qty Desc)

 

[ed coleman]

Hi CJ,
Thanks for your continuing patience. I put in the new code and I am getting a message saying "At most, only one record can be returned by this subquery". What I need is one line returned for every line in the OrderQuery.

Regards Ed

 

[CJ_London]

it will be because you have duplicates in your quote query. Change to

SELECT DISTINCT *
FROM OrderQuery INNER JOIN QuoteQuery ON OrderQuery.item=QuoteQuery.item
WHERE QuotePrice IN (SELECT TOP 1 QuotePrice FROM QuoteQuery Q WHERE Item=OrderQuery.Item AND Qty<=OrderQuery.Qty_Ordered ORDER BY Qty Desc)

May need to be

SELECT DISTINCT OrderQuery.*,QuoteQuery.QtyOrdered, QuoteQuery.QuotePrice
FROM OrderQuery INNER JOIN QuoteQuery ON OrderQuery.item=QuoteQuery.item
WHERE QuotePrice IN (SELECT TOP 1 QuotePrice FROM QuoteQuery Q WHERE Item=OrderQuery.Item AND Qty<=OrderQuery.Qty_Ordered ORDER BY Qty Desc)

 

[ed coleman]

Hi CJ, again thanks for your patience and being here in my time of stupidity.



For the month I am reviewing, I have 1767 unique records in the order file, i.e. a concatenation of the Item and the PO.


When I perform the query to select quantities and corresponding pricing from the quote file using the SQL query, I get 2618 records. Of the original 1767, the SQL query did not return matches for 489 of these records. My problem appears to be, at least on a review of the first 15 records or so, that the lowest quantity in the quote file is above what is in the order file and therefore is returning nothing. The ones that matched appear to have done so properly based on a sample of about 20 records.
Is there a way to add this criteria: if, for that item, all the quantities in the quote file are higher than the quantity in the order file then use the lowest quantity in the quote file with the corresponding quote price?


There appears to be another problem with duplicating records from the order file, but perhaps we could look at one problem at a time.
Regards, Ed

 

[CJ_London]

My problem appears to be, at least on a review of the first 15 records or so, that the lowest quantity in the quote file is above what is in the order file and therefore is returning nothing.

I did ask for a clear description of the business rule. But to address this issue - see below. For duplicated records - see my last post, I updated it and is included below

SELECT DISTINCT OrderQuery.*,QuoteQuery.QtyOrdered, QuoteQuery.QuotePrice
FROM OrderQuery INNER JOIN QuoteQuery ON OrderQuery.item=QuoteQuery.item
WHERE QuotePrice IN nz((SELECT TOP 1 QuotePrice FROM QuoteQuery Q WHERE Item=OrderQuery.Item AND Qty<=OrderQuery.Qty_Ordered ORDER BY Qty Desc),(SELECT TOP 1 QuotePrice FROM QuoteQuery Q WHERE Item=OrderQuery.Item AND Qty>OrderQuery.Qty_Ordered ORDER BY Qty))

 

[ed coleman]

Hi CJ, sorry I couldn't have been more definitive on my needs but I am running this from a ODBC where I analyze results to see what needs tweaking and learn as I go along.

There was an error message about ()'s with an IN statement, so I added brackets after the IN and at the end. However, when I run this, I get a message saying that it can only return one record.

SELECT DISTINCT qryItemsForMatch.*, TblQuoteForPeriod.Qty, tblQuoteForPeriod.Quote_Price
FROM qryItemsForMatch INNER JOIN tblQuoteForPeriod ON qryItemsforMatch.item=tblQuoteforPeriod.item
WHERE Quote_Price IN (nz((SELECT TOP 1 Quote_Price FROM tblQuoteforperiod Q WHERE Item=qryitemsformatch.Item AND Qty<=qryitemsformatch.Qty_Ordered ORDER BY Qty Desc),(SELECT TOP 1 Quote_Price FROM tblquoteforperiod Q WHERE Item=qryitemsformatch.Item AND Qty>qryitemsformatch.Qty_Ordered ORDER BY Qty)));

Thanks for your valued assistance.

Regards
Ed

 

[CJ_London]

I reached the end of what I can do without any data in a table.

Go back to my query changing the names as required. Run it, and provide me with the exact error message - take a screenshot if necessary.

Also be aware it will still fail if there are no quotes for comparison

 

[plog]

Sorry to bud in, but using the intial data you posted I have a solution. If it doesn't work immediately, disregard my post and keep working with CJ.

First you will need a subquery:

SELECT qryItemsForMatch.item, Max(tblQuotesForPeriod.QTY) AS QtyToUse
FROM qryItemsForMatch INNER JOIN tblQuotesForPeriod ON qryItemsForMatch.item = tblQuotesForPeriod.item
WHERE (((IIf([QTY_ORDERED]>=[QTY],1,0))=1))
GROUP BY qryItemsForMatch.item;

Paste the above into a new query and name it '_sub1'. It determines what record to use from the Quote table. Next, paste this into a new query and it will produce the results you want (at least based on your sample data):

SELECT tblQuotesForPeriod.item, tblQuotesForPeriod.QTY, tblQuotesForPeriod.QUOTE_PRICE
FROM _sub1 INNER JOIN tblQuotesForPeriod ON ([_sub1].QtyToUse = tblQuotesForPeriod.QTY) AND ([_sub1].item = tblQuotesForPeriod.item);

 

[ed coleman]

Hi Plog, thanks for your help, tried your queries but to no avail. My source query has 1767 records comprised partly of an item and a PO number, the combination of which is unique. I had to do it this way because each different PO for that item can have a different quantity ordered and therefore a different price based on the efficiencies of volume. The lookup query/table is made up of records containing an item number, quote quantity and a price based on the quantity. Each item can have many records depending on the different quantities quoted.

Again thanks for the try.

 

[ed coleman]

Hi CJ,
Would it be of any help to give you the source and lookup tables, if there were a way to get them to you?

Regards,
Ed

 

[CJ_London]

possibly. Would rather you provided the error message etc per post #16