While x do y Wend - Query Function Speed up tips (1 Viewer)

[plog]

That's not enough information. The expected results include data that is not in the initial table. Give me more data.

 

[Vagus14]

That's not enough information. The expected results include data that is not in the initial table. Give me more data.

Hey I am fairly sure I gave you everything. What are you missing?

 

[plog]

For instance your result query contains the field [Model]. That's not in the source table you provided.

 

[Vagus14]

For instance your result query contains the field [Model]. That's not in the source table you provided.

Hey Plog,

Model is derived from PN_Identifer using the code below:

Function TestPNModelLookup(strModel As String) As String

Dim Categories As Variant
Categories = Array("S50-1", "S50-2", "S35-1", "S35-2")

For Each Category In Categories
If strModel Like DLookup("[PN_Identifier]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'") Then
strModel = DLookup("[PN_AC_Category]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'")
TestPNModelLookup = strModel
End If
Next Category

'Blank Calculation
If TestPNModelLookup = "" Then
strModel = DLookup("[PN_AC_Category]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & "Blank" & "'")
TestPNModelLookup = strModel
End If

'Returns Orginal Value if Null but not used as Null will be Cotsi.
'PartModelLookup = strModel

End Function

Rate is also derived from PN_Identifer using the code below:

Function TestPNPricingRate(strRate As String) As String

Dim Categories As Variant
Categories = Array("S50-1", "S50-2", "S35-1", "S35-2")

For Each Category In Categories
If strRate Like DLookup("[PN_Identifier]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'") Then
strRate = DLookup("[PN_Rate]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & Category & "'")
TestPNPricingRate = strRate
End If
Next Category

'Blank Calculation
If TestPNPricingRate = "" Then
strRate = DLookup("[PN_Rate]", "[tblTemp_Rates_A]", "[PN_AC_Category] = '" & "Blank" & "'")
TestPNPricingRate = strRate
End If

'Returns Orginal Value if Null but not used as Null will be Cotsi.
'PartPricingRate = strRate

End Function

Both of these get the results in B for the query I want to generate.

 

[The_Doc_Man]

From your description, there is an issue that should have stopped you from implementation in the first place. However, it is possible that you have no control over the issue.

Put your answer in English as though you were explaining your problem to a chowderhead (which I can be at times):

From where do these matches originate? What are the dependent and independent parts of the match-up?

Is this originally something chosen by someone via a form or do you have some other data source?

Describe the CONCEPTS (not the technical steps) leading up to the point where this query became necessary.

I'm with the others. You just FLAT OUT DON'T want to do this the way you are doing it. I'm not surprised at all that you are claiming performance issues. TRUST ME when I say that I fully and firmly believe you. If we have enough of a grasp on the problem we might be able to find a way to deal with this.

I will not commit to this as "cast in concrete" but I'm thinking this is a table-design problem from the get-go. So as part of your description, let us know what can be changed structurally in your data sources and working tables.

And PLEASE understand that for complex problems, it will take us more than just a moment to "wrap our heads around it" so that we can give you best advice. That is why you need to give us a good description of the problem as seen from a distance. You are rather close to it and so your focus colors your explanation. (Trust us on this one: It ain't the first time we've seen that, so don't feel bad.)

 

[Vagus14]

Alternate Solution to an Wildcard Array in a Dlookup

From your description, there is an issue that should have stopped you from implementation in the first place. However, it is possible that you have no control over the issue.

Put your answer in English as though you were explaining your problem to a chowderhead (which I can be at times):

From where do these matches originate? What are the dependent and independent parts of the match-up?

Is this originally something chosen by someone via a form or do you have some other data source?

Describe the CONCEPTS (not the technical steps) leading up to the point where this query became necessary.

I'm with the others. You just FLAT OUT DON'T want to do this the way you are doing it. I'm not surprised at all that you are claiming performance issues. TRUST ME when I say that I fully and firmly believe you. If we have enough of a grasp on the problem we might be able to find a way to deal with this.

I will not commit to this as "cast in concrete" but I'm thinking this is a table-design problem from the get-go. So as part of your description, let us know what can be changed structurally in your data sources and working tables.

And PLEASE understand that for complex problems, it will take us more than just a moment to "wrap our heads around it" so that we can give you best advice. That is why you need to give us a good description of the problem as seen from a distance. You are rather close to it and so your focus colors your explanation. (Trust us on this one: It ain't the first time we've seen that, so don't feel bad.)

Doc thank you for chiming in. I don't need a fast solution but a quality one and I am here to learn.

I want the user to be able to edit the wildcards at will in a table and have them pop out on the end query with the correct rates.

I posted more information above that should help everyone. Thank you again for your help.

 

[jleach]

Just out of curiosity, I did give a rough draft, general approach for how I might handle wildcard pricing in my previous reply. Did you happen to take a look at that (I didn't see any indication, or answer to the question I had)?

It certainly wasn't a plug-n-play solution, but was intended to maybe (hopefully) give a different perspective to the thought process:

What happens if there's rate that matches more than one wildcard?

For example: you have two wildcard rates entered: S3-###-### for $10.99 and S3-ASD-### for $11.99... presumably the latter takes precedence as it's more specific?

In which case, I think you might be able to start out with something like:

(this is a rough draft approach which is not technically complete, nor guaranteed to work depending on how your pricing needs to be applied: for think-tank purposes only)

1) Query a rank for each wildcard: strip out the separators, and rank on a percentage of specified characters vs the total length of the wildcard. Example S3-XX-XX would have a rank of 33 as 33% of it's characters are specified.
2) Look up wildcard rates for products by joining your product table to your rates table using a LIKE operator as the join criteria (if you can... not sure about joining on a LIKE - not something I recall having to do before).
3) Sort the results by descending rank. Then you can grab the highest record for any given part number and theoretically get the best matched price.

That makes a lot of assumptions, but maybe gives you an idea of the right path?

I'd also note that if this data is contained elsewhere (SQL Server, for example), I'd be much more inclined to utilized the more advanced engine there instead of trying to do this in Access (features such as ROW_NUMBER OVER() can be useful for stuff like this, but there's no real Access equiv)

 

[Vagus14]

Just out of curiosity, I did give a rough draft, general approach for how I might handle wildcard pricing in my previous reply. Did you happen to take a look at that (I didn't see any indication, or answer to the question I had)?

It certainly wasn't a plug-n-play solution, but was intended to maybe (hopefully) give a different perspective to the thought process:

Hey I like solution #2 using LIKE for the join. I will try this right now. Thank you again for your reply and help.

 

[Vagus14]

@jleach, great idea with the #2 SQL. I found my quick solution below:

SELECT tblTemplRateAudit.PN_Ext,IIF( tblTemp_Rates_A.PN_Rate is null,Dlookup("[PN_Rate]","[tblTemp_Rates_A]","[PN_AC_Category]='"&"Blank"&"'"), tblTemp_Rates_A.PN_Rate) AS Rate, IIf(tblTemp_Rates_A.PN_AC_Category is null,Dlookup("[PN_AC_Category]","[tblTemp_Rates_A]","[PN_AC_Category]='"&"Blank"&"'"),tblTemp_Rates_A.PN_AC_Category) AS AC_Model
FROM tblTemplRateAudit LEFT JOIN tblTemp_Rates_A ON tblTemplRateAudit.PN_Ext LIKE tblTemp_Rates_A.PN_Identifier;

 

[Mark_]

Vagus, is this a "Magic Number" issue?

To explain, often a system will develop where how a "Number" is generated is very obscure since said "Number" attempts to encode multiple values we would reference as fields.

To illustrate, company decides that the first digit of each customer account should represent which territory the customer is in. As time goes by they discover one digit isn't enough so they rework things so that if the first digit is 1-8 it is for territories 1-8, otherwise if its a 9 the second digit tells them which territory it is.

Time goes by and they have more territories so they realize 18 isn't enough for their needs so they change it around and just use the first to digits (if after a certain date) to represent territories. Then they discover they have international clients so now 9+2 digits becomes "International territories", except for 9+1 digit which are the legacy clients.

As you can see, a system like this becomes very convoluted very quickly. Had they simply used a multi-part key (Country/Territory/Auto inc number) to track customers they'd have been much better off. More to the point if customer account was simply an auto inc number with the others used when needed they'd be even better off than some "Magic Number" with obscure rules for creation/interpretation.

From the data you've posted I've a feeling you are dealing with a "Magic Number". If so, I'd do my best to break out the pieces its made of and use THOSE rather than some strange set of characters that only have meaning in one specific context.

 

[Vagus14]

Vagus, is this a "Magic Number" issue?

To explain, often a system will develop where how a "Number" is generated is very obscure since said "Number" attempts to encode multiple values we would reference as fields.

To illustrate, company decides that the first digit of each customer account should represent which territory the customer is in. As time goes by they discover one digit isn't enough so they rework things so that if the first digit is 1-8 it is for territories 1-8, otherwise if its a 9 the second digit tells them which territory it is.

Time goes by and they have more territories so they realize 18 isn't enough for their needs so they change it around and just use the first to digits (if after a certain date) to represent territories. Then they discover they have international clients so now 9+2 digits becomes "International territories", except for 9+1 digit which are the legacy clients.

As you can see, a system like this becomes very convoluted very quickly. Had they simply used a multi-part key (Country/Territory/Auto inc number) to track customers they'd have been much better off. More to the point if customer account was simply an auto inc number with the others used when needed they'd be even better off than some "Magic Number" with obscure rules for creation/interpretation.

From the data you've posted I've a feeling you are dealing with a "Magic Number". If so, I'd do my best to break out the pieces its made of and use THOSE rather than some strange set of characters that only have meaning in one specific context.

Hey Mark,

Thank you for your take on this. I think I have figured it out. Thank god it is not what you were mentioning above haha. I just had to do a Like Left Join. See below:

SELECT tblTemplRateAudit.PN_Ext,IIF( tblTemp_Rates_A.PN_Rate is null,Dlookup("[PN_Rate]","[tblTemp_Rates_A]","[PN_AC_Category]='"&"Blank"&"'"), tblTemp_Rates_A.PN_Rate) AS Rate, IIf(tblTemp_Rates_A.PN_AC_Category is null,Dlookup("[PN_AC_Category]","[tblTemp_Rates_A]","[PN_AC_Category]='"&"Blank"&"'"),tblTemp_Rates_A.PN_AC_Category) AS AC_Model
FROM tblTemplRateAudit LEFT JOIN tblTemp_Rates_A ON tblTemplRateAudit.PN_Ext LIKE tblTemp_Rates_A.PN_Identifier;

Thank you all to who have helped me eventually reach this efficient conclusion.