Running into a query problem with to many ifs

[Marcel2586]

Good day all,

I have again a problem with access tables and query's.
Now i need to give parts a run time.
This is a complex situation.

On the work stock list i have to give all the parts total runtime (TAT) from start to finish.
We start with MAT numbers like Z42 is equal to 5 days run time.
But if the MAT is Z42 and the department code is 1400 and destination department is 1015 then the runtime is 2 days TAT. and if MAT is Z42 and the department is code 2900 and destination department is 1015 then the runtime is 2 days. and if department is 2300 and part description is fuel manifold then the runtime is 10 days TAT. now we calculate this in excel and in vba we give the definitions.
But this is very, very slow with update times from 20 to 40 minutes. so i was thinking convert the work stock list to access for more update speed.

Question.
Do i have to use VBA to do the calculations or can i do this with a query. (i hope so)
Now i have build a table with MAT, Department, Destination, customer, TAT.
I tried to join them all to the table work stock list but this is not working.
If need be, i can upload a scaled down version to see what is happening and where the * i am talking about.

Greeting,
Marcel.

Here a piece of Excel VBA that i try to translate to a query.

    Public Const target_Z01 As Integer = 2
    Public Const target_Z03 As Integer = 2
    Public Const target_Z11 As Integer = 5
    Public Const target_Z17 As Integer = 3
    Public Const target_Z18 As Integer = 2
    Public Const target_Z21 As Integer = 2
    Public Const target_Z31 As Integer = 5
    Public Const target_Z32 As Integer = 2
    Public Const target_Z41 As Integer = 2
    Public Const target_Z42 As Integer = 5
    Public Const target_Z42_1015_1400 As Integer = 2
    Public Const target_Z42_1015_2900 As Integer = 3
    Public Const target_Z42_fuel_manifold_2300 = 10
    Public Const target_Z43 As Integer = 5
    Public Const target_Z51 As Integer = 28
    Public Const target_Z51_1015 As Integer = 21
    Public Const target_Z51_1015_2900 As Integer = 15
    Public Const target_Z51_2600 As Integer = 15
    Public Const target_Z51_1015_28556 As Integer = 21 'CRMA

 

[vbaInet]

Create a table with the relevant fields and data and join this table to your master table via those fields. That will create an AND condition when joined.

 

[Marcel2586]

Create a table with the relevant fields and data and join this table to your master table via those fields. That will create an AND condition when joined.

I tried this and every part is now shown 3 times with different run times (TAT).

SQL

SELECT [tbl WVL_Data].[Serv order], [tbl WVL_Data].Project, [tbl WVL_Data].[Sales doc], [tbl WVL_Data].Customer, [tbl WVL_Data].CustName, [tbl WVL_Data].[Name 2], [tbl WVL_Data].[Customer pur nr], [tbl WVL_Data].Material, [tbl WVL_Data].Description, [tbl WVL_Data].MAT, [tbl WVL_Data].MainWrkctr, [tbl WVL_Data].Wrkctr, [tbl WVL_Data].Destination, [tbl WVL_Data].[Ord creation], [tbl WVL_Data].[Techniek codes], [tbl WVL_Data].Qty, [tbl WVL_Data].[TTG (Days)], [tbl WVL_Data].[TTG (hours)], [tbl TAT].TAT
FROM [tbl TAT] LEFT JOIN [tbl WVL_Data] ON ([tbl TAT].Destination = [tbl WVL_Data].Destination) AND ([tbl TAT].Customer = [tbl WVL_Data].Customer) AND ([tbl TAT].MAT = [tbl WVL_Data].MAT);

 

[vbaInet]

I can't see how that is happening without seeing the structure and data of your tables.

Oh by the way, it is possible for your recordset to be read-only only using this method.

You can also create a function with the necessary conditions and run this report in a query, passing the fields to the function.

 

[Marcel2586]

I can't see how that is happening without seeing the structure and data of your tables.

Oh by the way, it is possible for your recordset to be read-only only using this method.

You can also create a function with the necessary conditions and run this report in a query, passing the fields to the function.

Here is a copy of my databese, it is zipped.
Look at the query qry test to..............
tbl TAT is the lookup for days.

http://www.edsretired.nl/forumposts/WVL.zip

 

[vbaInet]

Your TAT table is not supposed to contain any blanks. You are supposed to join via the three relevant fields you mentioned in your post and include data that corresponds to your exact conditions.

So first of all, create a table that contains data matching the criteria you explained in your first post and show us. I will advise afterwards.

 

[Marcel2586]

Your TAT table is not supposed to contain any blanks. You are supposed to join via the three relevant fields you mentioned in your post and include data that corresponds to your exact conditions.

So first of all, create a table that contains data matching the criteria you explained in your first post and show us. I will advise afterwards.

vbaInet,

Oke, i think i see your point.
i have to divide it up-to multiple query's.
First a query that i use for strictly Z numbers and days.
A second query that filters Z numbers customers, destination and days.
from this query i can build a query that filters Z numbers and department and days. And then i can query that one on part name and give that a day number. from that i can make a make table query.

I will give that a try.

 

[vbaInet]

We start with MAT numbers like Z42 is equal to 5 days run time.
But if the MAT is Z42 and the department code is 1400 and destination department is 1015 then the runtime is 2 days TAT. and if MAT is Z42 and the department is code 2900 and destination department is 1015 then the runtime is 2 days. and if department is 2300 and part description is fuel manifold then the runtime is 10 days TAT.

Sorry, before you start working on this, let's look at it again. It seems not all your conditions are based on a combination of three fields. In that case you will need to use a function to calculate it:

public function GetRuntimeDays(optional MAT as variant, _
                               optional Destination as variant, _
                               optional Department) as variant
    if isnull(MAT) then
        exit function
    end if
 
    if MAT = "Z42" then
        if department = 1400 and destination = 1015 then
            GetRuntimeDays = 2
        elseif department = 2900 and destination = 1015 then
            GetRuntimeDays = 2
        elseif department = 2300 and description = "fuel manifold" then
            GetRuntimeDays = 10
        end if
    end if
end function

Please amend where necessary. Then use this function in query and pass in the relevant fields .

 

[Marcel2586]

Oke, i got it working from a test query, with some adjustments made to the code.

But my colleague warned me that you could better set it into tables and not into a VBA module. his argument was, you do not want people from sales rummaging into your code every time the customer has shorten there return time. it is better that they use a form for that. and i agreed with that.

here is the code but dlookup is not good at this moment.
if you remove the ' and remove the dlookup part, it works very nice.

Public Function TAT(Optional MAT As Variant, _
                               Optional Destination As Variant, _
                               Optional MainWrkctr As Variant, _
                               Optional Description As Variant, _
                               Optional Material As Variant, _
                               Optional Customer As Variant) As Double
    
    
    If IsNull(MAT) Then
        Debug.Print ("No MAT.")
        Exit Function
    End If
If MAT = "Z01" Then DLookup("TAT", "tbl Z numbers TAT") = TAT
'Give Z numbers a TAT in days
'    If MAT = "Z01" Then TAT = 2
'    If MAT = "Z03" Then TAT = 2
'    If MAT = "Z11" Then TAT = 5
'    If MAT = "Z17" Then TAT = 3
'    If MAT = "Z18" Then TAT = 2
'    If MAT = "Z21" Then TAT = 2
'    If MAT = "Z31" Then TAT = 5
'    If MAT = "Z32" Then TAT = 2
'    If MAT = "Z41" Then TAT = 2
'    If MAT = "Z42" Then TAT = 5
'    If MAT = "Z43" Then TAT = 5
'    If MAT = "Z51" Then TAT = 28
'    If MAT = "Z54" Then TAT = 5
'    If MAT = "Z58" Then TAT = 2
'    If MAT = "Z59" Then TAT = 3
    
    
'If destination, WorkCentre, customer and description are equal then change TAT
'        If MainWrkctr = 1400 And Destination = 1040 Then
'            TAT = 2000
'        ElseIf MainWrkctr = 2900 And Destination = 1015 Then
'            TAT = 2
'        ElseIf MainWrkctr = 2300 And Description = "fuel manifold" Then
'            TAT = 10: End If


End Function

 

[vbaInet]

Yes it would be better to put it into tables, not for security reasons (because your code should be protected) but for performance benefits. However your explanations made me go down the coding route.

Now, your last piece of code isn't what you initially explained so I think you need to clarify exactly what conditions you want and we can advise.

NB: Don't use the one-line IF statements because ALL IF blocks will have to be tested each time the function is called whereas in my piece of code when it finds a match, it will break out from the IF block. You can use a CASE statement if you don't like IF...ELSE blocks.

 

[Marcel2586]

I think that i am on the right track.
But i don't think my module deserves a beauty contests prize.

Public Function ZTAT(Znummer0Str As String) As Variant

    ZTAT = DLookup("[TAT]", "tbl Z numbers TAT", "[MAT]= '" & Znummer0Str & "'")

    If IsNull(ZTAT) Then
    MsgBox "The MAT " & Znummer0Str & " does not exist. Adjust the table.", _
    vbOKOnly + vbCritical, "KLM Engineering & Maintenance Engine Services WVL REPAIR"
    End If

End Function

Public Function DestTAT(MatStr As String, Znumber1Str As String) As Variant


    DestTAT = DLookup("[TAT]", "tbl Dest TAT", _
    "[MAT]= '" & MatStr & "'" & "And [Destination]= '" & Znumber1Str & "'")

End Function

Public Function custTAT(MatStr As String, Customer1Str As String, Destination1Str As String) As Variant

    custTAT = DLookup("[TAT]", "tbl Customer TAT", _
    "[MAT]= '" & MatStr & "'" & "And [Customer]= '" & Customer1Str & "'" & "And [destination]= '" & Destination1Str & "'")
    
End Function

 

[vbaInet]

Do they give you desired outputs?

 

[Marcel2586]

yes, it actually do, but not into one column all separated in there own column.
Now i need to find a way to look at the lowest value and put that one into the official column

 

[vbaInet]

You will need a query for that using the Min aggregate function.

 

[Marcel2586]

I am stuck with my public function.

I have 4 columns
Column 1 named TAT
Column 2 named Expr1
Column 3 named Expr2
Column 4 named Test

What i want to do is place all data from column 1 to column 4
look at column 2 for data. if this is true, replace the old data from column 4.
look at column 3 for data. if this is true, replace the old data from column 4.

So let's say record 200 column 1 has a value of 10 and column 3 is empty, then 10 should be placed at column 4 at record 200
Record 300 column 1 has a value of 2 and column 2 is empty and column 3 has a value of 8 then column 4 needs to be 8 at record 300
if record 325 column 1 has a value of 12 and column 2 and 3 are empty then column 4 needs to be 12
i tried to solve this with the code below, it does do something on every record of column 4 it is filled with #error exept where column 2 and 3 are filled with data.

Public Function CombineResults(Expr1 As String)

If IsNull(Expr1) Then
        Exit Function
    End If

If [Expr1] = Not Isemty Then CombineResults = "[TAT]"
Else: CombineResults = DLookup("TAT")
End If
End Function

 

[vbaInet]

I'm not going to attempt to fix your function because we're still not clear what you want to achieve. I've already asked for you to clarify exactly what you want to do and then we can take it from there.

Clearly explain what it is you're trying to do using the REAL LIFE situation.

 

[Marcel2586]

VbaInet,

Oke, i understand and made a .doc and uploaded this as attachment where i tried to explain as best as i can how it is done. if there are still questions, please let me know.

I'm not going to attempt to fix your function because we're still not clear what you want to achieve. I've already asked for you to clarify exactly what you want to do and then we can take it from there.

Clearly explain what it is you're trying to do using the REAL LIFE situation.