error message

aneats

Registered User.
Local time
Today, 22:19
Joined
Nov 13, 2002
Messages
70
I'm building what i thought wasa straightforward query, though i keep getting the following message when i try to run it:
"you tried to execute a query that does not include the specified expression 'PREFIX' as part of an aggregate function."

'PREFIX' is a field, the following is the expression it is in:
IIf(([APPLICATION]![TOWN]=[CRISP]![TOWN]) And ([APPDATES]![APP_DBM_DA]>=[CRISP]![DESIGNATED]) And ([APPDATES]![APP_DBM_DA]<=[CRISP]![END_DESIGN]),'UDP 08',IIf((([APPLICATION]![PREFIX]='IF3')),'UDP 03',IIf(([APPLICATION]![PREFIX]='IF4'),'UDP 04','UDP 05')))

This is another expression i have created. Perhaps the problem lies there:

LIVE?: IIf([APPLICATION]![PREFIX]<>'ENQ' And ([APPLICATION]![POSITION]='UC' Or [POSITION]='DBM' Or [POSITION]='AIP' Or [POSITION]='APA' Or [POSITION]='FA' Or [POSITION]='FAA'),'YES','NO')

Can anybody see where i am going wrong, or is it maybe something else altogether.
thanks.
 
I took out these two expression, and i continued to get the same message. So i took 'PREFIX' out of the query, and i got another message saying:

"you tried to execute a query that does not include the specified expression 'FILE' as part oof an aggregate function."

When i delete 'PREFIX' out of the query, 'FILE' becomes the first field in the query. So i'm thinking it has something to do with this (if i delete 'FILE' from the query the field beside it will be named in the error message). Does anybody know how I can stop this happening.

cheers
 
please post the whole SQL for your Query.
 
SELECT APPLICATION.PREFIX, APPLICATION.[FILE REFERENCE], APPLICATION.[PROPERTY NUMBER], APPLICATION.[PROPERTY ADDRESS], APPLICATION.TOWN, APPLICATION.[PROPERTY POSTCODE], APPLICATION.POSITION, APPLICATION.DESCRIPTION, ENQUIRY.CONDITION, APPLICATION.TYPE, ENQUIRY.VACANCY, APPLICATION.DIVISION, APPLICATION.[DISTRICT COUNCIL], [DC CODES].DC_CODE, IIf([APPLICATION]![DISTRICT COUNCIL]="Antrim" Or [APPLICATION]![DISTRICT COUNCIL]="Ballymena" Or [APPLICATION]![DISTRICT COUNCIL]="Banbridge" Or [APPLICATION]![DISTRICT COUNCIL]="Ards" Or [APPLICATION]![DISTRICT COUNCIL]="Carrickfergus" Or [APPLICATION]![DISTRICT COUNCIL]="Castlereagh" Or [APPLICATION]![DISTRICT COUNCIL]="North Down",'NO','YES') AS DISADVAREA, IIf([APPLICATION]![TOWN]=[CRISP]![TOWN],'CR',IIf([DISADVAREA]='YES','DI','ND')) AS [CR/DI/ND], IIf(([APPLICATION]![TOWN]=[CRISP]![TOWN]) And ([APPDATES]![APP_DBM_DA]>=[CRISP]![DESIGNATED]) And ([APPDATES]![APP_DBM_DA]<=[CRISP]![END_DESIGN]),'CR',IIf([DISADVAREA]='YES','DI','ND')) AS [CURRENT CRISP STATUS], APPLICANT.APPLICANT_TITLE, APPLICANT.APPLICANT_FORENAME, APPLICANT.APPLICANT_SURNAME, APPLICANT.APPLICANT_COMPANY, APPLICANT.APPLICANT_ADDRESS, APPLICANT.APPLICANT_TOWN, APPLICANT.APPLICANT_COUNTY, APPLICANT.APPLICANT_POSTCODE, APPLICANT.APPLICANT_PHONE_NO, AGENT.AGENT_TITL, AGENT.AGENT_FORE, AGENT.AGENT_SURN, AGENT.AGENT_COMP, AGENT.AGENT_PHON, AGENT.AGENT_FAX, APPDATES.APPLICATI2, NOTES.APPLICATIO, APPDATES.APP_BRANCH, APPDATES.APP_DBM_DA, DECISIONS.APP_DBM_DECISION, APPDATES.FULL_BOARD_DATE, APPDATES.APP_DATE_N, JOBS.FT_JOBS, JOBS.PT_JOBS, JOBS.RECIPIENT_, APPDATES.APP_APPEAL, APPDATES.APP_APPEA3, APPDATES.DECISION, [APPLICATION APPRAISERS].APP_APPRAISER, [APPLICATION APPRAISERS].APP_APPRAISER_DATE_ISSUED, [APPLICATION APPRAISERS].APP_APPRAISER_DATE_DUE, [APPLICATION APPRAISERS].APP_APPRAISER_REMINDER, [APPLICATION APPRAISERS].APP_APPRAISER_DATE_RECEIVED, APPDATES.PROJECT_SS, NOTES.PSSC_BY, APPDATES.ELEVATIONS, NOTES.ESS_BY, APPDATES.REVISED_GR, NOTES.RGCSS_BY, APPDATES.AIP_DATE, APPDATES.AIPA_DATE, APPDATES.DISPENSATI, APPDATES.WORK_START, APPDATES.WORK_COMPL, APPDATES.POST_SCHEM, STATAPPS.TITLE, STATAPPS.INSURANCE_, STATAPPS.BC_APPROVA, STATAPPS.PLANNING_P, STATAPPS.TENDERS, APPDATES.PD_DATE, APPDATES.RJ_DATE, APPDATES.WD_DATE, APPDATES.REVISED_G2, NOTES.RGCSS_FA_B, APPDATES.FA_CHECK_D, APPDATES.FA_DATE, APPDATES.FAA_DATE, IIf((('TOTAL GRANT'-'TOTAL DBM GRANT')/'TOTAL GRANT')*100>=10,'POSSIBLE REFERRAL','N/A') AS [% CHANGE OF FA], STATAPPS.BC_LETTER_, STATAPPS.PC_CERT, STATAPPS.FINAL_CERT, STATAPPS.FINAL_A_C, STATAPPS.FEE_A_C, FINANCE.OTHER_GRAN, GRANTS.GRANT_SOUG, [GRANTS]![APPLICATION]+[GRANTS]![DBM_APPROV] AS [TOTAL DBM GRANT], GRANTS.APPLICATION, GRANTS.DBM_APPROV, GRANTS.PRIVATE_CO, GRANTS.PUBLIC_CON, AMENDMENTS.REASON, AMENDMENTS.AMEND_DATE, AMENDMENTS.NET_GRANT_ADJUSTMENT, AMENDMENTS.FEES_ADJUSTMENT, AMENDMENTS.PRIVATE_CONTRIBUTION_ADJUSTMENT, AMENDMENTS.PUBLIC_CONTRIBUTION_ADJUSTMENT, Sum([AMENDMENTS]![NET_GRANT_ADJUSTMENT]) AS A, Sum([AMENDMENTS]![FEES_ADJUSTMENT]) AS B, Sum([AMENDMENTS]![PRIVATE_CONTRIBUTION_ADJUSTMENT]) AS C, Sum([AMENDMENTS]![PUBLIC_CONTRIBUTION_ADJUSTMENT]) AS D, GRANTS.GECS, IIf([APPLICATION]![POSITION]='WD','0','TOTAL NET GRANT'+'TOTAL FEES') AS [TOTAL GRANT], [GRANTS]![APPLICATION]+'A' AS [TOTAL NET GRANT], [GRANTS]![DBM_APPROV]+'B' AS [TOTAL FEES], IIf([APPLICATION]![POSITION]='WD' Or [APPLICATION]![POSITION]='RJ','0','TOTAL GRANT'-'TOTAL PAYMENTS') AS BALANCE, Sum([PAYMENTS]![PAYMENT_AM]) AS [TOTAL PAYMENTS], Sum([AMENDMENTS]![PUBLIC_CONTRIBUTION_ADJUSTMENT]) AS [TOTAL PUBLIC CONTRIB], Sum([AMENDMENTS]![PRIVATE_CONTRIBUTION_ADJUSTMENT]) AS [TOTAL PRIVATE CONTRIB], 'TOTAL PUBLIC CONTRIB'+'TOTAL PRIVATE CONTRIB'+'TOTAL GRANT' AS [PROJECT COSTS], NOTES.DATE, NOTES.NAME, NOTES.NOTES, PAYMENTS.PAYMENT_NU, PAYMENTS.PAYMENT_AM, BFS.BF_STATUS, BFS.FILE_TO, BFS.ISSUE_DATE, APPLICATION.PROFILE, [qry_Live Prog].[LIVE?], [qry_Live Prog].[PROG STATUS AGENT]
FROM [qry_Live Prog], [DC CODES] INNER JOIN ((((((((((((((APPDATES INNER JOIN APPLICATION ON APPDATES.APPDATES_FILE_ID = APPLICATION.[APPLICATION FILE ID]) INNER JOIN AGENT ON APPLICATION.[APPLICATION FILE ID] = AGENT.AGENT_FILE_ID) INNER JOIN AMENDMENTS ON APPLICATION.[APPLICATION FILE ID] = AMENDMENTS.AMENDMENTS_FILE_ID) INNER JOIN APPLICANT ON APPLICATION.[APPLICATION FILE ID] = APPLICANT.APPLICANT_FILE_ID) INNER JOIN [APPLICATION APPRAISERS] ON APPLICATION.[APPLICATION FILE ID] = [APPLICATION APPRAISERS].APP_APPRAISERS_FILE_ID) INNER JOIN BFS ON APPLICATION.[APPLICATION FILE ID] = BFS.BFS_FILE_ID) INNER JOIN DECISIONS ON APPLICATION.[APPLICATION FILE ID] = DECISIONS.DECISIONS_FILE_ID) INNER JOIN ENQUIRY ON APPLICATION.[APPLICATION FILE ID] = ENQUIRY.ENQUIRY_FILE_ID) INNER JOIN FINANCE ON APPLICATION.[APPLICATION FILE ID] = FINANCE.FINANCE_FILE_ID) INNER JOIN GRANTS ON APPLICATION.[APPLICATION FILE ID] = GRANTS.GRANTS_FILE_ID) INNER JOIN JOBS ON APPLICATION.[APPLICATION FILE ID] = JOBS.JOBS_FILE_ID) INNER JOIN NOTES ON APPLICATION.[APPLICATION FILE ID] = NOTES.NOTES_FILE_ID) INNER JOIN PAYMENTS ON APPLICATION.[APPLICATION FILE ID] = PAYMENTS.PAYMENTS_FILE_ID) INNER JOIN STATAPPS ON APPLICATION.[APPLICATION FILE ID] = STATAPPS.STATAPPS_FILE_ID) ON [DC CODES].DISTRICT_C = APPLICATION.[DISTRICT COUNCIL];
 
This is a shorter query, which gives me the same error message, referring to the first field "APPLICATION FILE ID"



SELECT APPLICATION.[APPLICATION FILE ID], APPLICATION.PREFIX, APPLICATION.TOWN, APPLICATION.[DISTRICT COUNCIL], [DC CODES].DC_CODE, APPLICATION.DIVISION, [qry_CURRENT CRISP STATUS].[CURRENT CRISP STATUS], [qry_PROG STATUS].[PROG STATUS], APPLICANT.APPLICANT_TITLE, APPLICANT.APPLICANT_FORENAME, APPLICANT.APPLICANT_SURNAME, APPLICANT.APPLICANT_COMPANY, APPLICATION.[PROPERTY NUMBER], APPLICATION.[PROPERTY ADDRESS], APPLICATION.[PROPERTY POSTCODE], APPLICATION.DESCRIPTION, JOBS.PT_JOBS, JOBS.FT_JOBS, JOBS.RECIPIENT_, APPDATES.APP_DBM_DA, APPDATES.FULL_BOARD_DATE, APPDATES.AIP_DATE, APPDATES.FA_DATE, APPLICATION.POSITION, APPDATES.WD_DATE, GRANTS.GRANT_SOUG, [GRANTS]![APPLICATION]+[GRANTS]![DBM_APPROV] AS [TOTAL DBM GRANT], GRANTS.APPLICATION, GRANTS.DBM_APPROV, GRANTS.PRIVATE_CO, GRANTS.PUBLIC_CON, Sum([AMENDMENTS]![NET_GRANT_ADJUSTMENT]) AS [NET GRANT ADJUSTMENT], Sum([GRANTS]![PUBLIC_CON]) AS [FEES ADJUSTMENTS], Sum([AMENDMENTS]![PRIVATE_CONTRIBUTION_ADJUSTMENT]) AS [PRIVATE CONTRIB ADJUSTMENTS], Sum([AMENDMENTS]![PUBLIC_CONTRIBUTION_ADJUSTMENT]) AS [PUBLIC CONTRIB ADJUSTMENTS], IIf([POSITION]='WD','0','TOTAL NET GRANT'+'TOTAL FEES') AS [TOTAL GRANT], [GRANTS]![APPLICATION]+'NET GRANT ADJUSTMENTS' AS [TOTAL NET GRANT], [GRANTS]![DBM_APPROV]+'FEES ADJUSTMENTS' AS [TOTAL FEES], GRANTS.GECS, 'TOTAL PUBLIC CONTRIBUTION'+'TOTAL PRIVATE CONTRIBUTION'+'TOTAL GRANT' AS [PROJECT COSTS]
FROM [DC CODES] INNER JOIN (((((APPDATES INNER JOIN ((APPLICATION INNER JOIN [qry_CURRENT CRISP STATUS] ON APPLICATION.[APPLICATION FILE ID] = [qry_CURRENT CRISP STATUS].[APPLICATION FILE ID]) INNER JOIN [qry_PROG STATUS] ON APPLICATION.[APPLICATION FILE ID] = [qry_PROG STATUS].[APPLICATION FILE ID]) ON APPDATES.APPDATES_FILE_ID = APPLICATION.[APPLICATION FILE ID]) INNER JOIN AGENT ON APPLICATION.[APPLICATION FILE ID] = AGENT.AGENT_FILE_ID) INNER JOIN APPLICANT ON APPLICATION.[APPLICATION FILE ID] = APPLICANT.APPLICANT_FILE_ID) INNER JOIN GRANTS ON APPLICATION.[APPLICATION FILE ID] = GRANTS.GRANTS_FILE_ID) INNER JOIN JOBS ON APPLICATION.[APPLICATION FILE ID] = JOBS.JOBS_FILE_ID) ON [DC CODES].DISTRICT_C = APPLICATION.[DISTRICT COUNCIL];
 
When you include aggregate functions such as Sum() in your Select, you need to Group By all non-aggregated columns. Ie, in a Totals query, EVERY selected or calculated column must be aggregated in some way. So you may Sum() some columns, Avg() others, but you must Group By the rest,

In any event, I'm not sure your query won't work as written. I believe you will need to aggregate the many-side data in one query and then join it to the other tables.

FYI, if you want people to look at your queries, you will need to make them more readable. One thing you can do to shorten a long SQL string is to use alias table names. For example, you can say:

From Application As A

Using that method, all columns would be prefixed by "A" rather than by "Application". This goes a long way toward enhancing readablility in a lengthy query. Another thing is to fix up the parentheses. The query builder frequently goes overboard with parentheses and you can remove the extraneous ones since they just make the query look more complicated.

There are several other problems:
1. single quotes around column names. That is not valid Access syntax. If the column name follows proper naming standards (no spaces or special characters), no delimiters are required. If the name does not conform to proper naming standards, Access uses the square brackets to delimit it.

2. outputting both numeric and text values from an IIf(). For example - IIf([POSITION]='WD','0','TOTAL NET GRANT'+'TOTAL FEES') - in the true case, the result will be a text 0 and the false case will be a numeric value. Remove the quotes from around the zero.

3. Also regarding the above example. If any of the columns involved in an arithmetic operation contain a null value, you will not get the results you expect. Change the statement to - IIf([POSITION]='WD', 0, Nz([TOTAL NET GRANT], 0) + Nz([TOTAL FEES], 0))

4. In the future, use better naming techniques. You're going to run into trouble with these names if you need to write VBA.
 
Thanks a milion. with a bit of playing around and a few more errors cropping up, i got there in the end.
 

Users who are viewing this thread

Back
Top Bottom