A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

ack!!! simplify to resolve Query too complex ack!!!



 
 
Thread Tools Display Modes
  #1  
Old October 24th, 2008, 11:26 AM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 90
Default 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  
Old October 24th, 2008, 12:19 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 90
Default 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  
Old October 24th, 2008, 12:38 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 90
Default 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  
Old October 24th, 2008, 01:02 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 90
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.