If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ack!!! simplify to resolve Query too complex ack!!!
Yesterday I posted a request to simplify an IIf and Doug Steele (any
relation to Marion L. Steele of Amherst, OH fame?) helped to go from this: UNEstNtWt: IIf([tblFGUOM].[UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt], 6),IIf([tblFGUOM].[UOMType]="VOL",Round([UNNtWt],6))) to this: UNEstNtWt: Switch([tblFGUOM].[UOMType]="WT",Round([UNNtWt]*0.05+ [UNNtWt],6),[tblFGUOM]*.[UOMType]="VOL",Round([UNNtWt],6)) While this is more simple in my mind - Access doesn't think so. The reason I posted this problem is because I've got a VERY complex query and this "little" seemingly benign IIf statement has taken things into the Query Too Complex Zone. I did some more query design and whittled things down to this: UNEstNtWt: Switch([UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],6), [UOMType]="VOL",Round([UNNtWt],6)) Still too complex. I even tried this: UNEstNtWt: IIf([UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],6) Or [UOMType]="VOL",Round([UNNtWt],6)) I tried removing 1 joined subquery and 2 joined tables as well as about 20 other fields - compacted and repaired - I then got the "System resource exceeded" deal. !!!!! The query has about 72 fields. When I leave them ALL in and everything else as is and then change the IIf to this: UNEstNtWt: Round([UNNtWt]*0.05+[UNNtWt],6) then everything is fine! I absolutely NEED to distinguish when a UOMType is WT or VOL and then calculate accordingly but this makes things too complex and it doesn't make sense considering my test of removing the stuff as noted above. WHAT THE HECK!!!??? The "solution" to a Query too complex is to REMOVE fields, tables, etc. I did that to no end! WHY is this one little IIf creating such havoc? Any help is greatly appreciated. This is without doubt the most aggravating problem I've ever had. THANKS! |
#2
|
|||
|
|||
ack!!! simplify to resolve Query too complex ack!!!
I just stripped away everything about the query except what's involved
with the problem here and the query ran (wow!) _BUT_ the calculation returned "-1" for EVERY record with a UOMType "VOL" value! It calculated properly for UOMType "WT" values! Here is the query's SQL: SELECT qryPKProfilesAssociationsPKWTsFGsULDims.txtProfile ID, tblProfiles.Version, qryPKProfilesAssociationsPKWTsFGsULDims.UnitCount, qryPKProfilesAssociationsPKWTsFGsULDims.SubUnitCou nt, qryPKProfilesAssociationsPKWTsFGsULDims.UnitSize, qryPKProfilesAssociationsPKWTsFGsULDims.UnitUOM, qryPKProfilesAssociationsPKWTsFGsULDims.UOMType, qryPKProfilesAssociationsPKWTsFGsULDims.UnitSizeCt , qryPKProfilesAssociationsPKWTsFGsULDims.UnitSizeCt c, qryPKProfilesAssociationsPKWTsFGsULDims.UnitSizec, qryPKProfilesAssociationsPKWTsFGsULDims.Densitylbg al, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNP RODWtlb AS SUBUNPRODWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNP RODWtlbTotal AS SUBUNPRODWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPROD Wtlb AS PRODWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPPRO DWtlbTotal AS PRODWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNP KWtlbTotal AS SUBUNPKWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWt lb AS UNPKWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWt lbTotal AS UNPKWtTotal, Round([UnitSizec]*[lbConvFactor],6) AS UNNtWtConvlb, Round([UnitSizec]*[galConvFactor],6) AS UNNtWtConvgal, IIf(IsNull([SubUnitCount]),[UnitCount]*[PRODWt], [UnitCount]*[SUBUNPRODWtTotal]) AS UNPRODNtWt, Nz([UNNtWtConvlb], [UNNtWtConvgal]*[Densitylbgal]) AS UNNtWtNz, Nz([UNPRODNtWt], [UNNtWtNz]) AS UNNtWtNzc, Round([UNNtWtNzc],6) AS UNNtWt, IIf([UOMType]="WT",Round([UNNtWt]*0.05+[UNNtWt],6) Or [UOMType]="VOL",Round([UNNtWt],6)) AS UNEstNtWt FROM (qryPKProfilesAssociationsPKWTsFGsULDims INNER JOIN tblProfiles ON qryPKProfilesAssociationsPKWTsFGsULDims.txtProfile ID = tblProfiles.txtProfileID) LEFT JOIN qryPKProfilesAssociationsPKWTsFGs ON qryPKProfilesAssociationsPKWTsFGsULDims.txtProfile ID = qryPKProfilesAssociationsPKWTsFGs.txtProfileID; Any ideas why "-1" would result for WT values??? THANKS!!! |
#3
|
|||
|
|||
ack!!! simplify to resolve Query too complex ack!!!
My apologies. I meant to say:
"-1" for EVERY record with a UOMType "WT" value! It calculated properly for UOMType "VOL" values! |
#4
|
|||
|
|||
ack!!! simplify to resolve Query too complex ack!!!
OK. I corrected the statement to Doug's suggestion:
UNEstNtWt: Switch([tblFGUOM].[UOMType]="WT",Round([UNNtWt]*0.05+ [UNNtWt],6),[tblFGUOM]*.[UOMType]="VOL",Round([UNNtWt],6)) This is behaving. Obviously, my "or" setup was faulty. Now I'm back to square one! Query too complex / System resource exceeded. Simplify to this and all is good: UNEstNtWt: Round([UNNtWt]*0.05+[UNNtWt],6) Sorry for all of this craziness. I'm trying to work it out but not doing so well. I'll leave it here and wait for any replies. |
Thread Tools | |
Display Modes | |
|
|