Error message "Property Value to Large" Trying to save query

[newtoAccess]

Hi all I get this error message when I try to save query.


I am using 2007 and it is a Query.

Error msg "Property Value to Large"

Here is my code.

CollectionAgc02Precent: [SumOfCollection_agc02]/[CountOfCollection agc]

So how do I set the result limits?
I checked with the help window where it stated the results are to large over 2k.

[RuralGuy]

Is that the entire SQL for your query?

[newtoAccess]

see statement below.

SELECT Count(Table_Data_Details.ID) AS CountOfID, Count(Table_Data_Details.Age) AS CountOfAge, Sum(Table_Data_Details.Age15) AS SumOfAge15, [SumOfAge15]/[CountOfAge] AS Age15Percent, Sum(Table_Data_Details.Age16) AS SumOfAge16, [SumOfAge16]/[CountOfAge] AS Age16Percent, Sum(Table_Data_Details.Age17) AS SumOfAge17, [SumOfAge17]/[CountOfAge] AS Age17Percent, Sum(Table_Data_Details.Age18) AS SumOfAge18, [SumOfAge18]/[CountOfAge] AS Age18Percent, Count(Table_Data_Details.Grade) AS CountOfGrade, Sum(Table_Data_Details.Grade11) AS SumOfGrade11, [SumOfGrade11]/[CountOfGrade] AS Grade11Percent, Sum(Table_Data_Details.Grade12) AS SumOfGrade12, [SumOfGrade12]/[CountOfGrade] AS Grade12Percent, Sum(Table_Data_Details.Grade13) AS SumOfGrade13, [SumOfGrade13]/[CountOfGrade] AS Grade13Percent, Count(Table_Data_Details.Gender) AS CountOfGender, Sum(Table_Data_Details.GenderM) AS SumOfGenderM, [SumOfGenderM]/[CountOfGender] AS GenderMPercent, Sum(Table_Data_Details.GenderF) AS SumOfGenderF, [SumOfGenderF]/[CountOfGender] AS GenderFPercent, Count(Table_Data_Details.School) AS CountOfSchool, Sum(Table_Data_Details.SchoolTISS) AS SumOfSchoolTISS, [SumOfSchoolTISS]/[CountOfSchool] AS TissPercent, Sum(Table_Data_Details.SchoolBCI) AS SumOfSchoolBCI, [SumOfSchoolBCI]/[CountOfSchool] AS BCIPercent, Sum(Table_Data_Details.StMary) AS SumOfStMary, [SumOfStMary]/[CountOfSchool] AS StMaryPercent, Count(Table_Data_Details.[Bank Account]) AS [CountOfBank Account], Sum(Table_Data_Details.BankAccountYes) AS SumOfBankAccountYes, [SumOfBankAccountYes]/[CountOfBank Account] AS BankAccountYPercent, Sum(Table_Data_Details.BankAccountNo) AS SumOfBankAccountNo, [SumOfBankAccountNo]/[CountOfBank Account] AS BankAccountNPercent, Count(Table_Data_Details.Savings) AS CountOfSavings, Sum(Table_Data_Details.SavingsYes) AS SumOfSavingsYes, [SumOfSavingsYes]/[CountOfSavings] AS SavingYPercent, Sum(Table_Data_Details.SavingsNo) AS SumOfSavingsNo, [SumOfSavingsNo]/[CountOfSavings] AS SavingNPercent, Count(Table_Data_Details.[Debit Card]) AS [CountOfDebit Card], Sum(Table_Data_Details.DebitCard01) AS SumOfDebitCard01, [SumOfDebitCard01]/[CountOfDebit Card] AS DebitDard01Percent, Sum(Table_Data_Details.DebitCard02) AS SumOfDebitCard02, [SumOfDebitCard02]/[CountOfDebit Card] AS DebitDard02Percent, Sum(Table_Data_Details.DebitCard03) AS SumOfDebitCard03, [SumOfDebitCard03]/[CountOfDebit Card] AS DebitDard03Percent, Sum(Table_Data_Details.DebitCard04) AS SumOfDebitCard04, [SumOfDebitCard04]/[CountOfDebit Card] AS DebitDard04Percent, Sum(Table_Data_Details.DebitCard05) AS SumOfDebitCard05, [SumOfDebitCard05]/[CountOfDebit Card] AS DebitDard05Percent, Count(Table_Data_Details.Cheques) AS CountOfCheques, Sum(Table_Data_Details.Cheques01) AS SumOfCheques01, [SumOfCheques01]/[CountOfCheques] AS Cheques01Percent, Sum(Table_Data_Details.Cheques02) AS SumOfCheques02, [SumOfCheques02]/[CountOfCheques] AS Cheques02Percent, Sum(Table_Data_Details.Cheques03) AS SumOfCheques03, [SumOfCheques03]/[CountOfCheques] AS Cheques03Percent, Sum(Table_Data_Details.Cheques04) AS SumOfCheques04, [SumOfCheques04]/[CountOfCheques] AS Cheques04Percent, Sum(Table_Data_Details.Cheques05) AS SumOfCheques05, [SumOfCheques05]/[CountOfCheques] AS Cheques05Percent, Count(Table_Data_Details.[Math Skills]) AS [CountOfMath Skills], Sum(Table_Data_Details.Math_Skills01) AS SumOfMath_Skills01, [SumOfMath_Skills01]/[CountOfMath Skills] AS MathSkills01Percent, Sum(Table_Data_Details.Math_Skills02) AS SumOfMath_Skills02, [SumOfMath_Skills02]/[CountOfMath Skills] AS MathSkills02Percent, Sum(Table_Data_Details.Math_Skills03) AS SumOfMath_Skills03, [SumOfMath_Skills03]/[CountOfMath Skills] AS MathSkills03Percent, Sum(Table_Data_Details.Math_Skills04) AS SumOfMath_Skills04, [SumOfMath_Skills04]/[CountOfMath Skills] AS MathSkills04Percent, Sum(Table_Data_Details.Math_Skills05) AS SumOfMath_Skills05, [SumOfMath_Skills05]/[CountOfMath Skills] AS MathSkills05Percent, Count(Table_Data_Details.[Understand Bank Account]) AS [CountOfUnderstand Bank Account], Sum(Table_Data_Details.Understand_Bank_Account01) AS SumOfUnderstand_Bank_Account01, [SumOfUnderstand_Bank_Account01]/[CountOfUnderstand Bank Account] AS UnderstandBankAccount01Percent, Sum(Table_Data_Details.Understand_Bank_Account02) AS SumOfUnderstand_Bank_Account02, [SumOfUnderstand_Bank_Account02]/[CountOfUnderstand Bank Account] AS UnderstandBankAccount02Percent, Sum(Table_Data_Details.Understand_Bank_Account03) AS SumOfUnderstand_Bank_Account03, [SumOfUnderstand_Bank_Account03]/[CountOfUnderstand Bank Account] AS UnderstandBankAccount03Percent, Sum(Table_Data_Details.Understand_Bank_Account04) AS SumOfUnderstand_Bank_Account04, [SumOfUnderstand_Bank_Account04]/[CountOfUnderstand Bank Account] AS UnderstandBankAccount04Percent, Sum(Table_Data_Details.Understand_Bank_Account05) AS SumOfUnderstand_Bank_Account05, [SumOfUnderstand_Bank_Account05]/[CountOfUnderstand Bank Account] AS UnderstandBankAccount05Percent, Count(Table_Data_Details.[Bank Services]) AS [CountOfBank Services], Sum(Table_Data_Details.Bank_Services01) AS SumOfBank_Services01, [SumOfBank_Services01]/[CountOfBank Services] AS BankServices01Percent, Sum(Table_Data_Details.Bank_Services02) AS SumOfBank_Services02, [SumOfBank_Services02]/[CountOfBank Services] AS BankServices02Percent, Sum(Table_Data_Details.Bank_Services03) AS SumOfBank_Services03, [SumOfBank_Services03]/[CountOfBank Services] AS BankServices03Percent, Sum(Table_Data_Details.Bank_Services04) AS SumOfBank_Services04, [SumOfBank_Services04]/[CountOfBank Services] AS BankServices04Percent, Sum(Table_Data_Details.Bank_Services05) AS SumOfBank_Services05, [SumOfBank_Services05]/[CountOfBank Services] AS BankServices05Percent, Count(Table_Data_Details.[Credit Cards Statements]) AS [CountOfCredit Cards Statements], Sum(Table_Data_Details.Credit_Cards_Statements01) AS SumOfCredit_Cards_Statements01, [SumOfCredit_Cards_Statements01]/[CountOfCredit Cards Statements] AS CreditCardStatments01Percent, Sum(Table_Data_Details.Credit_Cards_Statements02) AS SumOfCredit_Cards_Statements02, [SumOfCredit_Cards_Statements02]/[CountOfCredit Cards Statements] AS CreditCardStatment02Percent, Sum(Table_Data_Details.Credit_Cards_Statements03) AS SumOfCredit_Cards_Statements03, [SumOfCredit_Cards_Statements03]/[CountOfCredit Cards Statements] AS CreditCardStatment03Percent, Sum(Table_Data_Details.Credit_Cards_Statements04) AS SumOfCredit_Cards_Statements04, [SumOfCredit_Cards_Statements04]/[CountOfCredit Cards Statements] AS CreditCardStatment04Percent, Sum(Table_Data_Details.Credit_Cards_Statements05) AS SumOfCredit_Cards_Statements05, [SumOfCredit_Cards_Statements05]/[CountOfCredit Cards Statements] AS CreditCardStatment05Percent, Count(Table_Data_Details.[Calc interest on purchases]) AS [CountOfCalc interest on purchases], Sum(Table_Data_Details.Calc_interest_on_purchases0 1) AS SumOfCalc_interest_on_purchases01, [SumOfCalc_interest_on_purchases01]/[CountOfCalc interest on purchases] AS CalcInterest01Percent, Sum(Table_Data_Details.Calc_interest_on_purchases0 2) AS SumOfCalc_interest_on_purchases02, [SumOfCalc_interest_on_purchases02]/[CountOfCalc interest on purchases] AS CalcInterest02Percent, Sum(Table_Data_Details.Calc_interest_on_purchases0 3) AS SumOfCalc_interest_on_purchases03, [SumOfCalc_interest_on_purchases03]/[CountOfCalc interest on purchases] AS CalcInterest03Percent, Sum(Table_Data_Details.Calc_interest_on_purchases0 4) AS SumOfCalc_interest_on_purchases04, [SumOfCalc_interest_on_purchases04]/[CountOfCalc interest on purchases] AS CalcInterest04Percent, Sum(Table_Data_Details.Calc_interest_on_purchases0 5) AS SumOfCalc_interest_on_purchases05, [SumOfCalc_interest_on_purchases05]/[CountOfCalc interest on purchases] AS CalcInterest05Percent, Count(Table_Data_Details.[Compound Interest]) AS [CountOfCompound Interest], Sum(Table_Data_Details.Compound_Interest01) AS SumOfCompound_Interest01, [SumOfCompound_Interest01]/[CountOfCompound Interest] AS CompoundInterest01percent, Sum(Table_Data_Details.Compound_Interest02) AS SumOfCompound_Interest02, [SumOfCompound_Interest02]/[CountOfCompound Interest] AS CompoundInterest02percent, Sum(Table_Data_Details.Compound_Interest03) AS SumOfCompound_Interest03, [SumOfCompound_Interest03]/[CountOfCompound Interest] AS CompoundInterest03percent, Sum(Table_Data_Details.Compound_Interest04) AS SumOfCompound_Interest04, [SumOfCompound_Interest04]/[CountOfCompound Interest] AS CompoundInterest04percent, Sum(Table_Data_Details.Compound_Interest05) AS SumOfCompound_Interest05, [SumOfCompound_Interest05]/[CountOfCompound Interest] AS CompoundInterest05percent, Count(Table_Data_Details.[Resources Available]) AS [CountOfResources Available], Sum(Table_Data_Details.[Resources Available01]) AS [SumOfResources Available01], [SumOfResources Available01]/[CountOfResources Available] AS ResourceAvailable01percent, Sum(Table_Data_Details.[Resources Available02]) AS [SumOfResources Available02], [SumOfResources Available02]/[CountOfResources Available] AS ResourceAvailable02percent, Sum(Table_Data_Details.[Resources Available03]) AS [SumOfResources Available03], [SumOfResources Available03]/[CountOfResources Available] AS ResourceAvailable03percent, Sum(Table_Data_Details.[Resources Available04]) AS [SumOfResources Available04], [SumOfResources Available04]/[CountOfResources Available] AS ResourceAvailable04percent, Sum(Table_Data_Details.[Resources Available05]) AS [SumOfResources Available05], [SumOfResources Available05]/[CountOfResources Available] AS ResourceAvailable05percent, Count(Table_Data_Details.[Budget and save for]) AS [CountOfBudget and save for], Sum(Table_Data_Details.Budget_and_save_for01) AS SumOfBudget_and_save_for01, [SumOfBudget_and_save_for01]/[CountOfBudget and save for] AS BudetSave01Percent, Sum(Table_Data_Details.Budget_and_save_for02) AS SumOfBudget_and_save_for02, [SumOfBudget_and_save_for02]/[CountOfBudget and save for] AS BudetSave02Percent, Sum(Table_Data_Details.Budget_and_save_for03) AS SumOfBudget_and_save_for03, [SumOfBudget_and_save_for03]/[CountOfBudget and save for] AS BudetSave03Percent, Sum(Table_Data_Details.Budget_and_save_for04) AS SumOfBudget_and_save_for04, [SumOfBudget_and_save_for04]/[CountOfBudget and save for] AS BudetSave04Percent, Sum(Table_Data_Details.Budget_and_save_for05) AS SumOfBudget_and_save_for05, [SumOfBudget_and_save_for05]/[CountOfBudget and save for] AS BudetSave05Percent, Count(Table_Data_Details.Investing) AS CountOfInvesting, Sum(Table_Data_Details.Investing01) AS SumOfInvesting01, [SumOfInvesting01]/[CountOfInvesting] AS Investing01Percent, Sum(Table_Data_Details.Investing02) AS SumOfInvesting02, [SumOfInvesting02]/[CountOfInvesting] AS Investing02Percent, Sum(Table_Data_Details.Investing03) AS SumOfInvesting03, [SumOfInvesting03]/[CountOfInvesting] AS Investing03Percent, Sum(Table_Data_Details.Investing04) AS SumOfInvesting04, [SumOfInvesting04]/[CountOfInvesting] AS Investing04Percent, Sum(Table_Data_Details.Investing05) AS SumOfInvesting05, [SumOfInvesting05]/[CountOfInvesting] AS Investing05Percent, Count(Table_Data_Details.[debt reduction]) AS [CountOfdebt reduction], Sum(Table_Data_Details.debt_reduction01) AS SumOfdebt_reduction01, [SumOfdebt_reduction01]/[CountOfdebt reduction] AS DebtReduction01Percent, Sum(Table_Data_Details.debt_reduction02) AS SumOfdebt_reduction02, [SumOfdebt_reduction02]/[CountOfdebt reduction] AS DebtReduction02Percent, Sum(Table_Data_Details.debt_reduction03) AS SumOfdebt_reduction03, [SumOfdebt_reduction03]/[CountOfdebt reduction] AS DebtReduction03Percent, Sum(Table_Data_Details.debt_reduction04) AS SumOfdebt_reduction04, [SumOfdebt_reduction04]/[CountOfdebt reduction] AS DebtReduction04Percent, Sum(Table_Data_Details.debt_reduction05) AS SumOfdebt_reduction05, [SumOfdebt_reduction05]/[CountOfdebt reduction] AS DebtReduction05Percent, Count(Table_Data_Details.[rights and responsibilities]) AS [CountOfrights and responsibilities], Sum(Table_Data_Details.rights_and_responsibilities 01) AS SumOfrights_and_responsibilities01, [SumOfrights_and_responsibilities01]/[CountOfrights and responsibilities] AS RightsResponsibilities01Percent, Sum(Table_Data_Details.rights_and_responsibilities 02) AS SumOfrights_and_responsibilities02, [SumOfrights_and_responsibilities02]/[CountOfrights and responsibilities] AS RightsResponsibilities02Percent, Sum(Table_Data_Details.rights_and_responsibilities 03) AS SumOfrights_and_responsibilities03, [SumOfrights_and_responsibilities03]/[CountOfrights and responsibilities] AS RightsResponsibilities03Percent, Sum(Table_Data_Details.rights_and_responsibilities 04) AS SumOfrights_and_responsibilities04, [SumOfrights_and_responsibilities04]/[CountOfrights and responsibilities] AS RightsResponsibilities04Percent, Sum(Table_Data_Details.rights_and_responsibilities 05) AS SumOfrights_and_responsibilities05, [SumOfrights_and_responsibilities05]/[CountOfrights and responsibilities] AS RightsResponsibilities05Percent, Count(Table_Data_Details.[saving strategies]) AS [CountOfsaving strategies], Sum(Table_Data_Details.[saving strategies01]) AS [SumOfsaving strategies01], [SumOfsaving strategies01]/[CountOfsaving strategies] AS SavingStrat01Percent, Sum(Table_Data_Details.[saving strategies02]) AS [SumOfsaving strategies02], [SumOfsaving strategies02]/[CountOfsaving strategies] AS SavingStrat02Percent, Sum(Table_Data_Details.[saving strategies03]) AS [SumOfsaving strategies03], [SumOfsaving strategies03]/[CountOfsaving strategies] AS SavingStrat03Percent, Sum(Table_Data_Details.[saving strategies04]) AS [SumOfsaving strategies04], [SumOfsaving strategies04]/[CountOfsaving strategies] AS SavingStrat04Percent, Sum(Table_Data_Details.[saving strategies05]) AS [SumOfsaving strategies05], [SumOfsaving strategies05]/[CountOfsaving strategies] AS SavingStrat05Percent, Count(Table_Data_Details.fraud) AS CountOffraud, Sum(Table_Data_Details.fraud01) AS SumOffraud01, [SumOffraud01]/[CountOffraud] AS Fraud01Percent, Sum(Table_Data_Details.fraud02) AS SumOffraud02, [SumOffraud02]/[CountOffraud] AS Fraud02Percent, Sum(Table_Data_Details.fraud03) AS SumOffraud03, [SumOffraud03]/[CountOffraud] AS Fraud03Percent, Sum(Table_Data_Details.fraud04) AS SumOffraud04, [SumOffraud04]/[CountOffraud] AS Fraud04Percent, Sum(Table_Data_Details.fraud05) AS SumOffraud05, [SumOffraud05]/[CountOffraud] AS Fraud05Percent, Count(Table_Data_Details.[Collection agc]) AS [CountOfCollection agc], Sum(Table_Data_Details.Collection_agc01) AS SumOfCollection_agc01, [SumOfCollection_agc01]/[CountOfCollection agc] AS CollectionAgc01Percent, Sum(Table_Data_Details.Collection_agc02) AS SumOfCollection_agc02, [SumOfCollection_agc02]/[CountOfCollection agc] AS CollectionAgc02Precent, Sum(Table_Data_Details.Collection_agc03) AS SumOfCollection_agc03, [SumOfCollection_agc03]/[CountOfCollection agc] AS CollectionAgc03Precent, Sum(Table_Data_Details.Collection_agc04) AS SumOfCollection_agc04, [SumOfCollection_agc04]/[CountOfCollection agc] AS CollectionAgc04Precent, Sum(Table_Data_Details.Collection_agc05) AS SumOfCollection_agc05, [SumOfCollection_agc05]/[CountOfCollection agc] AS CollectionAgc05Precent, Count(Table_Data_Details.[credit history]) AS [CountOfcredit history], Sum(Table_Data_Details.credit_history01) AS SumOfcredit_history01, [SumOfcredit_history01]/[CountOfcredit history] AS CreditHistory01Percent, Sum(Table_Data_Details.credit_history02) AS SumOfcredit_history02, [SumOfcredit_history02]/[CountOfcredit history] AS CreditHistory02Percent, Sum(Table_Data_Details.credit_history03) AS SumOfcredit_history03, [SumOfcredit_history03]/[CountOfcredit history] AS CreditHistory03Percent, Sum(Table_Data_Details.credit_history04) AS SumOfcredit_history04, [SumOfcredit_history04]/[CountOfcredit history] AS CreditHistory04Percent, Sum(Table_Data_Details.credit_history05) AS SumOfcredit_history05, [SumOfcredit_history05]/[CountOfcredit history] AS CreditHistory05Percent, Count(Table_Data_Details.[online banking]) AS [CountOfonline banking], Sum(Table_Data_Details.online_banking01) AS SumOfonline_banking01, Sum(Table_Data_Details.online_banking02) AS SumOfonline_banking02, Sum(Table_Data_Details.online_banking03) AS SumOfonline_banking03, Sum(Table_Data_Details.online_banking04) AS SumOfonline_banking04, Sum(Table_Data_Details.online_banking05) AS SumOfonline_banking05, Sum(Table_Data_Details.copy_of_credit_bureau01) AS SumOfcopy_of_credit_bureau011, Sum(Table_Data_Details.copy_of_credit_bureau01) AS SumOfcopy_of_credit_bureau01, Sum(Table_Data_Details.copy_of_credit_bureau02) AS SumOfcopy_of_credit_bureau02, Sum(Table_Data_Details.copy_of_credit_bureau03) AS SumOfcopy_of_credit_bureau03, Sum(Table_Data_Details.copy_of_credit_bureau04) AS SumOfcopy_of_credit_bureau04, Sum(Table_Data_Details.copy_of_credit_bureau05) AS SumOfcopy_of_credit_bureau05, Count(Table_Data_Details.[consumer problems]) AS [CountOfconsumer problems], Sum(Table_Data_Details.consumer_problems01) AS SumOfconsumer_problems01, Sum(Table_Data_Details.consumer_problems02) AS SumOfconsumer_problems02, Sum(Table_Data_Details.consumer_problems03) AS SumOfconsumer_problems03, Sum(Table_Data_Details.consumer_problems04) AS SumOfconsumer_problems04, Sum(Table_Data_Details.consumer_problems05) AS SumOfconsumer_problems05 INTO Table_Sum_All
FROM Table_Data_Details;

[RuralGuy]

Well I asked for it didn't I. I'm showing ~17,304 characters here. Could that be your problem?

[newtoAccess]

What the above is.
Students were asked 27 questions there responses were between "1" no experience or knowledge of that question to "5" I am and expert of that question.

Question "What is your experience in writing cheques?"

First column count the response "countCheques" this confirmed the total count, the next summed "SumCheques01" up the response that are 1, the next column is the percentage "Cheques01Persent" of response 1, the next column is "SumCheques02"the sum of response 2, the next is the "Cheques02ersent" percentage of response 2, the next the "SumCheques03" of response 3, the next the "Cheques03Persent" percentage of 3, this continues up to 5.

The same is repeated for each of the 27 questions

[newtoAccess]

What's my name. hint hint "New To Access"
This is a create new table query.
Should I split the query and have Part 2 could be used to update the table?
Do you think that would work?

[newtoAccess]

By the way the response is very fast tonight. Other times I have to wait over night.

Thanks for responding.

[RuralGuy]

What's my name. hint hint "New To Access"
This is a create new table query.
Should I split the query and have Part 2 could be used to update the table?
Do you think that would work?

I caught the "New to Access" part before. Queries are actually saved as what you see in the SQL view. That needs to be < 2048 characters. Whatever approach you use needs to adhere to that limitation. Breaking down the problem into smaller tasks seems like an excellent approach here.

[newtoAccess]

I learn something new every time I touch Access.
Are there any limits on the size of tables?

Thanks again

[RuralGuy]

If you ask Access Help (not VBA Help) about Specifications, you'll see all of the limits. Just press F1 at the Database window for Access Help.

[RuralGuy]

BTW, are you ready to use the Thread tool and mark this thread as Solved?

[newtoAccess]

Thanks will do