Exact and/or Closest Match (1 Viewer)

ed coleman · Jun 26, 2017

HiCJ,

I have attached the error message. I tried to copy and paste the PrintScreen on Friday but it obviously didn't work.

This error message occurred when I tried to save the code.

Regards Ed

CJ_London · Jun 26, 2017

the query is not as I suggested - it should be

....IN (nz(....

not as you have it

....IN nz((....

ed coleman · Jun 27, 2017

Hi CJ,
Looks like the same error message,

This occurs when I try to save the code.

Regards Ed

CJ_London · Jun 27, 2017

OK, better upload your db. Remove any tables/queries/forms not relevant to this question. Then compact/repair and then zip before uploading

ed coleman · Jun 27, 2017

Hi CJ,
Have attached my skinnied down version of my database. Only two tables.

Regards,
Ed

ed coleman · Jun 27, 2017

CJ PS There are about 5 records in the formatch table that do not have a set of corresponding records in the quote file. I am reviewing this with the user. I assume this will not cause a problem, just there will be no match for these records.

Thanks
Ed

CJ_London · Jun 27, 2017

here is a revised query

Code:

SELECT DISTINCT tblItemsForMatch.*, Nz([L].[QTY],[H].[QTY]) AS qtyCompare, Nz([L].[Quote_Price],[H].[Quote_Price]) AS QuoteCompare
FROM (tblItemsForMatch LEFT JOIN tblQuoteForPeriod AS L ON tblItemsForMatch.item = L.item) LEFT JOIN tblQuoteForPeriod AS H ON tblItemsForMatch.item = H.item
WHERE (((L.QTY)=(SELECT Max(qty) FROM [tblQuoteForPeriod] WHERE item=[tblItemsForMatch].[item] and qty<=[tblItemsForMatch].[QTY_ORDERED]))) OR ((((SELECT Max(qty) FROM [tblQuoteForPeriod] WHERE item=[tblItemsForMatch].[item] and qty<=[tblItemsForMatch].[QTY_ORDERED])) Is Null) AND ((H.QTY)=(SELECT Min(qty) FROM [tblQuoteForPeriod] WHERE item=[tblItemsForMatch].[item] and qty>[tblItemsForMatch].[QTY_ORDERED])));

Note that you have a number of duplicates, identified with this query

Code:

SELECT Query1.[item], Query1.[ORDER_NO], Query1.[QTY_ORDERED], Query1.[PRICING_METH], Query1.[ORD_PRICE], Query1.[IHI_PP], Query1.[qtyCompare], Query1.[QuoteCompare]
FROM Query1
WHERE (((Query1.[item]) In (SELECT [item] FROM [Query1] As Tmp GROUP BY [item],[ORDER_NO],[QTY_ORDERED],[PRICING_METH],[ORD_PRICE],[IHI_PP] HAVING Count(*)>1  And [ORDER_NO] = [Query1].[ORDER_NO] And [QTY_ORDERED] = [Query1].[QTY_ORDERED] And [PRICING_METH] = [Query1].[PRICING_METH] And [ORD_PRICE] = [Query1].[ORD_PRICE] And [IHI_PP] = [Query1].[IHI_PP])))
ORDER BY Query1.[item], Query1.[ORDER_NO], Query1.[QTY_ORDERED], Query1.[PRICING_METH], Query1.[ORD_PRICE], Query1.[IHI_PP];

The duplicates are because you have, for a given product, multiple prices for the same qty in tblQuoteforperiod

Note the data you supplied is unindexed which means the queries are very slow. If not already in your production data, at the very least index the item fields and ideally the quantity fields as well - you will see a 100 fold improvement in running time

ed coleman · Jun 28, 2017

Hi CJ,
Looks like your query is working. I didn't notice the multiple quote records for same item with the same quantity with the same or different prices. I have gone back to the supplier of our system to see how they select a price from the quotes to put on the order when there are multiple prices. It may involve just selecting the record with the most recent version date, which would not affect your query. You've also picked up the lowest quantity on file when the ordered quantity is less than the lowest. Still addressing with the supplier why there are no quotes for some items.

Appreciate your input tremendously.

Regards Ed

CJ_London · Jun 28, 2017

You've also picked up the lowest quantity on file when the ordered quantity is less than the lowest

isn't that what you asked for?

use the lowest quantity in the quote file with the corresponding quote price

ed coleman · Jun 28, 2017

Yes, I did. Just saying that you hit it out of the park!!!!

ed coleman · Jun 29, 2017

Hi CJ,

I was doing some more reviewing into the query results and noticed some problems with too many output records for an input. I zeroed in on item Z102823-1-- and have attached a file that shows input data, lookup data, actual output data and what I was expecting.

Both of the input records have ordered quantities below the minimum in the lookup data (tblQuoteForPeriod) and the query should have picked up the 1500 quantity and $2402 price for each of the two input records IE if the ordered quantity is below the minimum of the lookup data quantity, the minimum lookup quantity and corresponding price should be used.

I have also been told that a ship to code may change the pricing of a quote due to the distance the same item, produced at the same quantity may have to be trucked. I am looking at the effect on the results if I ignore this ship to wrinkle.

CJ_London · Jun 30, 2017

Sorry, I have no idea.

If you modify the query to take out the lower price determination, it still produces 3 rows per order

And if you run the subquery on its own (substituting for the item code and quantity in the criteria) it produces the correct result.

Think you need to check for other items where this is happening, I can't see a pattern as to why it is not producing the required result

I did try compacting the db, changed the price field to long (decimal is a bit extreme), removed and added indexes, exported both tables to text files and reimported to remove potential corrupted records, but still get the 3 rows per order.

Also changed the subquery to TOP 1 type but still get the 3 records per order.

So this does imply there is something wrong with the query, but I can't see it

ed coleman · Jul 1, 2017

I'll try to look at more examples.

Happy Canada Day to All

Regards
Ed

ed coleman · Jul 4, 2017

Hi CJ,

I have done some more analysis and it appears the problem occurs when the ordered quantity is lower than the lowest quote quantity. When this occurs and there is only one quantity in the quote file, there is no problem. However, when there is more than one quantity for that item, it seems to pick them all up.

I looked at one example of this where there were three records with quantities in the quote file. Two of the three had the same quantity due to different ship to locations. In this case, the code returned, not all of the records as it had when all quote quantities were different, but only the two where the quantities differed. I have since refined my quote table to contain only one record for each concatenation of item and quantity. But the original-explained problem still exists.

Hope this assists in ferreting out the problem?

Regards,
Ed

CJ_London · Jul 4, 2017

try this sql - works for Z102823-1--

Code:

SELECT tblItemsForMatch.*, Nz([L].[QTY],[H].[QTY]) AS qtyCompare, Nz([L].[Quote_Price],[H].[Quote_Price]) AS QuoteCompare
FROM (tblItemsForMatch LEFT JOIN (SELECT DISTINCT tblItemsForMatch.item, tblItemsForMatch.ORDER_NO, tblItemsForMatch.QTY_ORDERED, L.QTY, L.QUOTE_PRICE
FROM tblItemsForMatch LEFT JOIN tblQuoteForPeriod AS L ON tblItemsForMatch.item = L.item
WHERE (((L.QTY)=(SELECT Max(qty) FROM [tblQuoteForPeriod] WHERE item=[tblItemsForMatch].[item] and qty<=[tblItemsForMatch].[QTY_ORDERED]))))  AS L ON (tblItemsForMatch.ORDER_NO = L.ORDER_NO) AND (tblItemsForMatch.item = L.item)) LEFT JOIN (SELECT DISTINCT tblItemsForMatch.item, tblItemsForMatch.ORDER_NO, H.QTY, H.QUOTE_PRICE
FROM tblItemsForMatch LEFT JOIN tblQuoteForPeriod AS H ON tblItemsForMatch.item = H.item
WHERE (((H.QTY)=(SELECT Min(qty) FROM [tblQuoteForPeriod] WHERE item=[tblItemsForMatch].[item] and qty>[tblItemsForMatch].[QTY_ORDERED]))))  AS H ON (tblItemsForMatch.ORDER_NO = H.ORDER_NO) AND (tblItemsForMatch.item = H.item)
WHERE (((tblItemsForMatch.item)="Z102823-1--"));

ed coleman · Jul 4, 2017

Hi CJ, this looks good.

Changed it to Z102959--1- and it worked fine for all 14 items from the tblItemsForMatch. No duplicates or triplicates from the tblQuoteForPeriod side.

To make it universal, do I just remove the item number and insert an asterisk?

Regards,
Ed

CJ_London · Jul 4, 2017

no just remove the criteria

but look at the code, break it down and understand how it works. You should never use code you do not understand. I (and others) may not be around if you wanted to change it

ed coleman · Jul 4, 2017

Thanks for all your valuable assistance.

Ed, eh

Exact and/or Closest Match (1 Viewer)

Registered User.

Attachments

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Attachments

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Registered User.

Attachments

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread