Results 1 to 7 of 7
  1. #1
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55

    Need Help with Error “Too Many Fields Defined” When Running a Query

    I cannot seem to figure out a way around the error produced in qryRankings. Attached is a mock-up of the database.

    The original table “tblProjectDetails” has about 30 fields but I have reduced the fields down to 4 for ease in the mock-up. Table “tblRankings” only has 33 fields so it can’t be caused by too many fields.

    I think the fields listed below are the cause of the error but can’t figure out a way around it. All three fields are calculations and the CostEffectiveness fields is a calculation of six fields that are all calculations themselves.

    CostEffectiveness: IIf([CategoryID]=1 And [StatusID]=3 And [RequestedAmt]>0 And [Subpoints]>0 Or [CategoryID]=1 And [StatusID]=6 And [RequestedAmt]>0 And [Subpoints]>0,([RequestedAmt]/([LocalMatchPoints]+[CriticalOppPoints]+[ProjectReadinessPoints]+[SafetyPoints]+[CongestionPoints])),0)

    MinCostEff: IIf([CostEffectiveness]>1,FormatCurrency(DMin("CostEffectiveness","qryMin CostEff1"),0),0)

    CostEffectivenessPoints:
    IIf([MinCostEff]>0,FormatNumber(IIf(([MinCostEff]/[CostEffectiveness])*15>15,15,([MinCostEff]/[CostEffectiveness])*15),2),0)

    Some observations:

    1. If I remove the TotalPoints calculation from the query, it runs with no issues.
    2. If I remove the CosteffectivenessPoints field from the TotalPoints calculation, the query runs with no issues.
    3. I created a separate query to calculate the minimum cost effectiveness and then pulled the value into qryRankings but still get the same error.
    4. I have two other fields (MaxWeightCrash and MaxVCRatio) that are both obtaining the maximum amounts but they work with no issues. They are also based on the raw data which I can’t do with MinCostEff.

    It’s a lot of calculations and is per my client’s requirements.

    Any help will be greatly appreciated.

    Attachment 43418
    Attached Files Attached Files
    Last edited by PSSMargaret; 11-08-2020 at 03:19 PM. Reason: Attached an updated mock-up database. Same issue still occurring.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't know if you read up on this error, but there is an internal field count that increases by one as soon as you add table fields or alter their properties. Removing a field doesn't reduce the count but a compact/repair is supposed to fix it. However that didn't work for me. I see that the query has about 40 fields, many of them calculated. I can only surmise that the total count involves every field reference in all of your calculations (most of them have at least 2 field references) as well as the underlying table fields from each table. Perhaps that count exceeds 255. The oft posted solution is to break up a query into parts if a c/r doesn't work. I was going to try a decompile for fun but my company is here now and I have to quit.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    haven't checked all your calculations in qryrankings but

    MinCostEff: IIf([CostEffectiveness]>0,FormatCurrency(DMin("CostEffectiveness","qryran kings","[CostEffectiveness]>0"),0),0)

    is calling itself in your dMin function. Must admit, never tried it but would guess it is not a workable.

    Also, you are using left/right joins, but so far as I can see, not handling nulls. For example your CriticalOpp field has nulls but your code for calculating the points is

    IIf([CriticalOpp]="N/A",0,IIf([CriticalOpp]="Yes",5,0))

    further this code could be simplified to

    IIf([CriticalOpp]="Yes",5,0)

    Are the left/right joins really necessary? if not just use inner joins. And if they are, handle nulls

    you also appear to be trying to keep formatting throughout your calculations which is just unnecessary until you present the data in a form or report and only serves to confuse an already complex requirement. This is not excel.

    you have not used any brackets and have a random OR in the middle in this part of your iif

    [CategoryID]=1 And [StatusID]=3 And [RequestedAmt]>0 And [Subpoints]>0 Or [CategoryID]=1 And [StatusID]=6 And [RequestedAmt]>0 And [Subpoints]>0

    if you mix ands and ors, you need to use brackets to keep them apart, otherwise is that [Subpoints]>0 Or [CategoryID]=1? or perhaps [RequestedAmt]>0 And [Subpoints]>0 Or [CategoryID]=1

    Personally I would start again. Do your base calculations in one query and summarise in a second query

  4. #4
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    I have done the following:

    1. Created a second query (qryRankings2) and moved MinCostEff, CostEffectivenessPoints and Total Points to that query. MinCostEff is now pulling from qryRankings1.
    2. Removed all formatting and slimmed down the formulas as much as possible.
    3. Changed left and right joins to inner joins.

    I’m not exactly sure what you mean by handling nulls. If a record doesn’t have a value in Critical Opp the CriticalOppPoints formula is returning a 0.

    qryRankings2 still returns the same error.
    Attached Files Attached Files

  5. #5
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    If no one has anything else to offer here, is it a problem if I post my issue on another forum?

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I suspect if you ae still using the Domain functions, they effectively drag in the entire table.

    So, 30 + 30 fields in your original query + possibly another 60 if you there is a DMax() in the the underlying query = 120.
    Join that to itself and you are up to 240 - and the max is 256. As you can see it may not take much to get there.

    I had some very "wide" (unnormalised) tables with about 130 fields in I was doing some data cleaning on, and I couldn't run any updates on them as joining to themselves(searching missing values between an old and new version) went over the limit.
    I ended up doing that bit in SQL Server, as it doesn't have the same limitations.

    I haven't had time to look at your data unfortunately, but may do later if time permits.

    And as long as you mention this thread on other forums and vice versa fire away with additional assistance requests.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your costeffectivenesspoints calculation has a potential division by zero, so this needs to be handled properly (2 of your records have costeffectiveness=0) i.e.

    CostEffectivenessPoints: IIf([MinCostEff]>0 and [CostEffectiveness]<>0,IIf(([MinCostEff]/[CostEffectiveness])*15>15,15,([MinCostEff]/[CostEffectiveness])*15),0)

    also, removing this from your TotalPoints calcuation and your query runs - so this is where you need to try to simplify things further

    I've taken the time to rewrite your query and it runs - copy and paste this into a new query

    Code:
    SELECT qryRankings1.RankingID, qryRankings1.ProjectID, [LocalMatchPoints]+[CriticalOppPoints]+[ProjectReadinessPoints]+[SafetyPoints]+[CongestionPoints]+[CostEffectivenessPoints] AS TotalPoints, T.MinCostE, IIf([MinCostE]>0 And [costEffectiveness]<>0,IIf(([MinCostE]/[CostEffectiveness])*15>15,15,([MinCostE]/[CostEffectiveness])*15),0) AS CostEffectivenessPoints, qryRankings1.RequestedAmt, qryRankings1.WeightedCrashes, qryRankings1.MaxWeightCrash, qryRankings1.SafetyPoints, qryRankings1.VCRatio, qryRankings1.MaxVCRatio, qryRankings1.CongestionPoints, qryRankings1.Subpoints, qryRankings1.CostEffectiveness, IIf([LocalMatch]="25%",5,IIf([LocalMatch]="30%",10,IIf([LocalMatch]="35%",15,IIf([LocalMatch]="40%",20,IIf([LocalMatch]="45%",20,IIf([LocalMatch]="50% or >",25,0)))))) AS LocalMatchPoints, IIf([CriticalOpp]="Yes",5,0) AS CriticalOppPoints, IIf([ProjectReadiness]="Design",5,IIf([ProjectReadiness]="ROW",10,IIf([ProjectReadiness]="Bid Phase",15,0))) AS ProjectReadinessPoints
    FROM qryRankings1, (SELECT Min([CostEffectiveness]) AS MinCostE FROM [qryRankings1] WHERE [CostEffectiveness]>0)  AS T
    Up to you to check whether the results are as expected

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 01-10-2020, 04:50 PM
  2. Replies: 4
    Last Post: 10-16-2017, 09:09 AM
  3. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  4. Replies: 4
    Last Post: 06-08-2012, 09:08 AM
  5. Replies: 5
    Last Post: 03-13-2012, 11:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums