Need to know the logic behind the formula

[Johin.b]

Hi guys,

Could you please help me understand the logic used in this query? There are 'n' number of tables and queries in a access database and need to know the function of one query. How is this query doing the calculations? I am new to this and using simple words would help a lot.

Thanks in advance.

SELECT Month1.CUIC, Month1.Region, Month1.Branch, Month1.Assign, Month1.RoleCD, Month1.[CIR Name], Month1.[Call Number], Month1.[Self Ref Number], Month1.Type AS Type1, Month1.FVF, Month1.[POP Line], Month1.[Urg CD], Month1.CNT, Month1.[Opn-Date], Month1.[Opn-Time], Month1.[Cmp-Date], Month1.[Cmp-Time], Month1.[Sys-Cmp-Date], Month1.[Sys-Cmp-Time], Month1.[Rep-Style], Month1.[Rep-Serial-number], Month1.[CIR-Serial-number], Month1.[Customer Name], Month1.[Problem Description], Month1.Resolution, [Non prod Hrs]+[Prod act Hrs] AS [Call Time], Month1.[Travel Hrs], Month1.Distance, Month1.Pcode, Month1.[Prod act Hrs], Month1.NPcode, Month1.[Non prod Hrs] AS [Non prod Hrs1], IIf([NPcode]=43 Or [NPcode]=45 Or [NPcode]=61,[Non prod Hrs1],0) AS [Non prod Hrs], Month1.[Emp ID], Month1.[Tel-Type], Month1.[Ons-Type], Month1.[Res-Type], IIf([Urg CD] Like "IN*","Install",IIf([Res-Type] Like "#*BD*","Other",IIf([Customer Name] Like "*EMC*","EMC",IIf([ProbStyle] Like "DELLSERVERS*","ESF",IIf([ProbStyle] Like "*SRV*","SBD",IIf([Indicator1]=1,"NBD",IIf([Indicator2]=3,"SBD",IIf([Blank]="5","NBD",IIf([Indicator1]=3,"UTS","NBD"))))))))) AS Type, Month1.[IA Number], Month1.City, Month1.State, Month1.Zipcd, Month1.NAID, Month1.ProbStyle, Month1.Address, [Call Time]+[Travel Hrs] AS [Total Time], IIf([RoleCD]="W00564" Or [RoleCD]="W00565" Or [RoleCD]="W00566" Or [RoleCD]="W00567" Or [RoleCD]="W00568" Or [RoleCD]="W00569" Or [RoleCD]="W00611" Or [RoleCD]="W00613" Or [RoleCD]="W00614" Or [RoleCD]="W00615" Or [RoleCD]="W00696" Or [RoleCD]="W00697" Or [RoleCD]="W00698" Or [RoleCD] Like "ZZ*","CIR",IIf([RoleCD] Like "TP*" Or [RoleCD] Like "XX*","Contractor",IIf([RoleCD]="W00298" Or [RoleCD]="W00299" Or [RoleCD]="W00426" Or [RoleCD]="W00427" Or [RoleCD]="W00455" Or [RoleCD]="W00649" Or [RoleCD]="W00652","CRC",IIf([RoleCD]="W00300" Or [RoleCD]="W00304" Or [RoleCD]="W00305" Or [RoleCD]="W00306" Or [RoleCD]="W00307" Or [RoleCD]="W00323" Or [RoleCD]="W00475","CSC",IIf([RoleCD]="W00345" Or [RoleCD]="W00346" Or [RoleCD]="W00347" Or [RoleCD]="W00348" Or [RoleCD]="W00349" Or [RoleCD]="W00350","SDM","Other"))))) AS [Employee type], IIf([Urg CD]="ND" And [Customer Name] Like "Dell/*","3",IIf([Res-Type] Like "*BD" Or [Ons-Type] Like "*BD" Or [Ons-Type] Like "NSL" Or [Res-Type] Like "NSL",1,2)) AS Indicator1, IIf([Indicator1]>1 And [Ons-Type]<"1600",3,4) AS Indicator2, IIf(IsNull([Res-Type]) And ([Ons-Type])," ","5") AS Blank, Weekday([Cmp-Date]) AS [Day of week], IIf([Res-Type] Like "#*BD*",1,2) AS Indicator3
FROM Month1;

Regards,
Joe

[rpeare]

SELECT Month1.CUIC, 
Month1.Region, 
Month1.Branch, 
Month1.Assign, 
Month1.RoleCD, 
Month1.[CIR Name], 
Month1.[Call Number], 
Month1.[Self Ref Number], 
Month1.Type AS Type1, 
Month1.FVF, 
Month1.[POP Line], 
Month1.[Urg CD], 
Month1.CNT, 
Month1.[Opn-Date], 
Month1.[Opn-Time], 
Month1.[Cmp-Date], 
Month1.[Cmp-Time], 
Month1.[Sys-Cmp-Date], 
Month1.[Sys-Cmp-Time], 
Month1.[Rep-Style], 
Month1.[Rep-Serial-number], 
Month1.[CIR-Serial-number], 
Month1.[Customer Name], 
Month1.[Problem Description], 
Month1.Resolution, 
[Non prod Hrs]+[Prod act Hrs] AS [Call Time], 
Month1.[Travel Hrs], 
Month1.Distance, 
Month1.Pcode, 
Month1.[Prod act Hrs], 
Month1.NPcode, 
Month1.[Non prod Hrs] AS [Non prod Hrs1], 
IIf([NPcode]=43 Or [NPcode]=45 Or [NPcode]=61,[Non prod Hrs1],0) AS [Non prod Hrs], 
Month1.[Emp ID], 
Month1.[Tel-Type], 
Month1.[Ons-Type], 
Month1.[Res-Type], 
IIf([Urg CD] Like "IN*","Install",
    IIf([Res-Type] Like "#*BD*","Other",
        IIf([Customer Name] Like "*EMC*","EMC",
            IIf([ProbStyle] Like "DELLSERVERS*","ESF",
                IIf([ProbStyle] Like "*SRV*","SBD",
                    IIf([Indicator1]=1,"NBD",
                        IIf([Indicator2]=3,"SBD",
                            IIf([Blank]="5","NBD",
                                IIf([Indicator1]=3,"UTS","NBD"))))))))) AS Type, 


Month1.[IA Number], 
Month1.City, 
Month1.State, 
Month1.Zipcd, 
Month1.NAID, 
Month1.ProbStyle, 
Month1.Address, 

[Call Time]+[Travel Hrs] AS [Total Time], 

IIf([RoleCD]="W00564" Or [RoleCD]="W00565" Or [RoleCD]="W00566" Or [RoleCD]="W00567" Or [RoleCD]="W00568" Or [RoleCD]="W00569" Or [RoleCD]="W00611" Or [RoleCD]="W00613" Or [RoleCD]="W00614" Or [RoleCD]="W00615" Or [RoleCD]="W00696" Or [RoleCD]="W00697" Or [RoleCD]="W00698" Or [RoleCD] Like "ZZ*","CIR",IIf([RoleCD] Like "TP*" Or [RoleCD] Like "XX*","Contractor",IIf([RoleCD]="W00298" Or [RoleCD]="W00299" Or [RoleCD]="W00426" Or [RoleCD]="W00427" Or [RoleCD]="W00455" Or [RoleCD]="W00649" Or [RoleCD]="W00652","CRC",IIf([RoleCD]="W00300" Or [RoleCD]="W00304" Or [RoleCD]="W00305" Or [RoleCD]="W00306" Or [RoleCD]="W00307" Or [RoleCD]="W00323" Or [RoleCD]="W00475","CSC",IIf([RoleCD]="W00345" Or [RoleCD]="W00346" Or [RoleCD]="W00347" Or [RoleCD]="W00348" Or [RoleCD]="W00349" Or [RoleCD]="W00350","SDM","Other"))))) AS [Employee type], 

IIf([Urg CD]="ND" And [Customer Name] Like "Dell/*","3",
    IIf([Res-Type] Like "*BD" Or [Ons-Type] Like "*BD" Or [Ons-Type] Like "NSL" Or [Res-Type] Like "NSL",1,2)) AS Indicator1, 

IIf([Indicator1]>1 And [Ons-Type]<"1600",3,4) AS Indicator2, 

IIf(IsNull([Res-Type]) And ([Ons-Type])," ","5") AS Blank, Weekday([Cmp-Date]) AS [Day of week], 
    IIf([Res-Type] Like "#*BD*",1,2) AS Indicator3

FROM Month1;

I've split out your query so it's easier to see the formulas (at least for me.

You have the following calculated values in your query:


Non Prod Hrs1
Month1.[Non prod Hrs] AS [Non prod Hrs1]
This is saying use the field [Non Prod Hrs] but give it an alias (a different reference name) of [Non Prod Hrs1]

Non Prod Hrs
IIf([NPcode]=43 Or [NPcode]=45 Or [NPcode]=61,[Non prod Hrs1],0)
This is saying if the NP code is 43, 45 or 61 use the value in the field [Non Prod Hrs], otherwise use the value of 0

Call Time
[Non prod Hrs]+[Prod act Hrs] AS [Call Time]
This is saying add the fields [prod act hrs] and [non prod hrs] to make the value for [call time]

NOTE: if you actually have a field in your tables called [NON PROD HRS] you are creating a circular reference and you should not be re-aliasing a field using the same name. Your CALCULATED [NON PROD HRS] should probably be something like [NON_PROD_HRS_CALC] to differentiate it from your actual table name.

Type

IIf([Urg CD] Like "IN*","Install",
IIf([Res-Type] Like "#*BD*","Other",
IIf([Customer Name] Like "*EMC*","EMC",
IIf([ProbStyle] Like "DELLSERVERS*","ESF",
IIf([ProbStyle] Like "*SRV*","SBD",
IIf([Indicator1]=1,"NBD",
IIf([Indicator2]=3,"SBD",
IIf([Blank]="5","NBD",
IIf([Indicator1]=3,"UTS","NBD"))))))))) AS Type,

This calculation is basically just cycling through the fields enclosed in the square brackets ([]) to output a string to the field [TYPE]

        If [URG CD] starts with the string IN                Type = Install
        If [RES-TYPE] starts with the string #*BD*           Type = Other
        If [Customer Name ] contains the string EMC          Type = EMC
        If [ProbStyle] starts with the string DELLSERVERS    Type = ESF
        If [ProbStyle] cotnains the string SRV               Type = SBD
        If [Indicator1] = 1                                  Type = NBD
        If [Indicator2] = 3                                  Type = SBD
        If [Blank] = 5                                       Type = NBD
        If [Indicator1] = 3                                  Type = UTS
        Othewise                                             Type = NBD

Total Time
[Call Time]+[Travel Hrs] AS [Total Time]
Just adds [Call Time] and [Travel Hrs] to give a field with the alias [Total Time]
NOTE: travel hrs is a field in a table call time is a calculated value

Employee Type
IIf([RoleCD]="W00564" Or [RoleCD]="W00565" Or [RoleCD]="W00566" Or [RoleCD]="W00567" Or [RoleCD]="W00568" Or [RoleCD]="W00569" Or [RoleCD]="W00611" Or [RoleCD]="W00613" Or [RoleCD]="W00614" Or [RoleCD]="W00615" Or [RoleCD]="W00696" Or [RoleCD]="W00697" Or [RoleCD]="W00698" Or [RoleCD] Like "ZZ*","CIR",
IIf([RoleCD] Like "TP*" Or [RoleCD] Like "XX*","Contractor",
IIf([RoleCD]="W00298" Or [RoleCD]="W00299" Or [RoleCD]="W00426" Or [RoleCD]="W00427" Or [RoleCD]="W00455" Or [RoleCD]="W00649" Or RoleCD]="W00652","CRC",
IIf([RoleCD]="W00300" Or [RoleCD]="W00304" Or [RoleCD]="W00305" Or [RoleCD]="W00306" Or [RoleCD]="W00307" Or [RoleCD]="W00323" Or [RoleCD]="W00475","CSC",
IIf([RoleCD]="W00345" Or [RoleCD]="W00346" Or [RoleCD]="W00347" Or [RoleCD]="W00348" Or [RoleCD]="W00349" Or [RoleCD]="W00350","SDM","Other"))))) AS [Employee type]
This formula is just testing the ROLECD field for specific values
Just follow the indents to group which roleCD's belong to which Employee Type (CIR, Contractor, CRC, CSC, SDM or Other)

Indicator 1
IIf([Urg CD]="ND" And [Customer Name] Like "Dell/*","3",
IIf([Res-Type] Like "*BD" Or [Ons-Type] Like "*BD" Or [Ons-Type] Like "NSL" Or [Res-Type] Like "NSL",1,2)) AS Indicator1,
This formula says:
if the string in [URG CD] is ND and the [Customer Name] field starts with the string Dell/ Then [Indicator1] is 3
if the string in [Res-Type] ENDS in BD or the string in the field [Ons-Type] ENDS in BD or the string in [Ons-Type] EQUALS NSL or the string in the field [Res-Type] EQUALS NSL then [Indicator1] is 1
Otherwise [Indicator1] is 2

NOTE: There may be a problem with your formula in that you are using a LIKE statement with a static string and no wildcard You may want to change it to LIKE *NSL if it ends with, *NSL* if it is anywhere in the string or NSL* if it starts the strig in [Res-Type] or [Ons-Type].

Indicator 2
IIf([Indicator1]>1 And [Ons-Type]<"1600",3,4) AS Indicator2
This formula is saying if the CALCULATED field [INDICATOR1] is greater than 1 and the field [ONS-TYPE] is less than the TEXT VALUE "1600" then [INDICATOR2] is 3
Otherwise [INDICATOR2] is 4

NOTE: you may get some unexpected results if you are checking a TEXT field for a numeric value. For instance if you were to sort the 16, 168 and 1600 your sort and you were checking for anything less than 1600 it would not pick up the value 168 because the sort order of these values when evaluated as STRINGS (text fields) is

16
1600
168

Blank
IIf(IsNull([Res-Type]) And ([Ons-Type])," ","5") AS Blank
This formula is in error
You are not comparing the value of [Ons-Type] to anything. if you are testing both for null you would have to have:
IIf(IsNull([Res-Type]) And isnull([Ons-Type]),null,"5") AS Blank
This would mean if both [Res-Type] and [Ons-Type] were null, you want a null value (I don't know how you're using this so it could be stored as a space like you originally had it), otherwise the field BLANK should be the value 5

Day of Week
Weekday([Cmp-Date]) AS [Day of week]
This just looks like it's taking a date in one of your tables and renaming it to the alias [Day of Week]

Indicator3
IIf([Res-Type] Like "#*BD*",1,2) AS Indicator3
I don't know what you're trying to do with this * indicates a wildcard and you can't check for two different strings in the same like statement in other words, are you trying to check to see if your [RES-TYPE] string starts with # or starts with BD, or does (can your) your [RES-TYPE] contain a text value of * for instance can you have a value of"

#*BDXXXYYYY.

If your string CAN contain what is a reserved wild card indicator (*) you would be better of using the LEFT function

if (Left([Res-Type], 4) = "#*BD", 1, 2) as Indicator3

[Johin.b]

Thanks a ton rpeare