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
|
|||
|
|||
Text field causing trouble in query (continued)
Hi,
This is a continuation of a post a few days ago. Apologies about the time lag, but the question is from a contract I am working on only 1 day per week, evenings and weekends - and last night I was more concerned with writing a new job application. The two responses to my earlier post both said that my expression 'should' have worked and could I post the SQL - so I have included more info below: I have three columns in my query: customer ID(CIF), Age, Emp (there will be more when I solve this problem with text fields). The expressions in Age and Emp are each reading four columns (fields) in my table (as shown in SQL below). CIF is the table key and is an autonumber, Age is numerical, Emp is text. In the table the four Emp fields hold either "Y", "N", or "" (most are ""), for any Customer ID record: if any of the four Emp fields holds "Y" then I want the query expression to return "Y". This is the SQL: SELECT qry_CIFs_APPEND_DATA_01.CIF, IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) AS AGE, IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N") AS EMP FROM qry_CIFs_APPEND_DATA_01 GROUP BY qry_CIFs_APPEND_DATA_01.CIF; These are the expressions as written in the query's design view: AGE: IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) - This works with no problems. EMP: IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N") - This returns an error. I am getting an error on the 'Emp' expression: IIf([Emp Save]="Y","Y","N") or IIF([Emp Save]="Y" Or [Emp Inv]="Y" Or...,"Y","N") gives error 'trying to use a function that is not part of an aggregate function'. If this were numbers I was working with I would simply use IIF(Sum([Emp Save])=1,,) etc. but I can't find a text function that I can aggregate with (as I can 'aggregate' any number field by simply wrapping it in Sum()). I am not trying an 'aggregate query' as such to my knowledge, I just want the expression to return a "Y" if any of these four fields in the table are "Y" - note that "Y" is text field, not a yes/no, and most records are empty. The Age part of the query if functioning perfectly, if any of the fours age fields holds data then data is being returned by the expression (I don't actually care what the data is, so long as it is anything not null - if one record holds more than one age field populated then they are always the same, so catching any age value works, and *one* of them will be not null, I just don't know which one for any given record). The queries have 'append' in their names because this is leading to an append query that will be used to build a new table that has only one [Emp] field rather than four. This weekend I am going to try another way around this ( Sum(IIf([Emp ...]"",1,0))+Sum(IIf([Emp...]"",1,0)...) which may or may not work, but I am frustrated by this error message about trying to aggregate functions when the data is text and Access help and the Microsoft knowledge base do not seem to provide any clues as to how I am meant to aggregate a text function, or conversely, how to write an expression to work with text fields that does not incur an 'aggregate' error message. I think I have included everything. Any help would be greatly appreciated. Regards, Kai Richmond |
#2
|
|||
|
|||
Text field causing trouble in query (continued)
Your problem is being caused by the "Group By" bit.
This makes your query an "aggregate" query. What this is saying is "I only want one row in my output for each .CIF". By including the other fields in your output you are now saying "But I also want to see these calulated values for every row in the source query" Remove the "Group by" clause. See what the output looks like. If you get multiple .CIFs and want to get rid of some, then you need to re-think your criteria -----Original Message----- Hi, This is a continuation of a post a few days ago. Apologies about the time lag, but the question is from a contract I am working on only 1 day per week, evenings and weekends - and last night I was more concerned with writing a new job application. The two responses to my earlier post both said that my expression 'should' have worked and could I post the SQL - so I have included more info below: I have three columns in my query: customer ID(CIF), Age, Emp (there will be more when I solve this problem with text fields). The expressions in Age and Emp are each reading four columns (fields) in my table (as shown in SQL below). CIF is the table key and is an autonumber, Age is numerical, Emp is text. In the table the four Emp fields hold either "Y", "N", or "" (most are ""), for any Customer ID record: if any of the four Emp fields holds "Y" then I want the query expression to return "Y". This is the SQL: SELECT qry_CIFs_APPEND_DATA_01.CIF, IIf(Sum([Age Save]) "",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) AS AGE, IIf([Emp Save] ="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over] ="Y","Y","N") AS EMP FROM qry_CIFs_APPEND_DATA_01 GROUP BY qry_CIFs_APPEND_DATA_01.CIF; These are the expressions as written in the query's design view: AGE: IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) - This works with no problems. EMP: IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan] ="Y" Or [Emp Over]="Y","Y","N") - This returns an error. I am getting an error on the 'Emp' expression: IIf([Emp Save]="Y","Y","N") or IIF([Emp Save]="Y" Or [Emp Inv]="Y" Or...,"Y","N") gives error 'trying to use a function that is not part of an aggregate function'. If this were numbers I was working with I would simply use IIF(Sum([Emp Save])=1,,) etc. but I can't find a text function that I can aggregate with (as I can 'aggregate' any number field by simply wrapping it in Sum()). I am not trying an 'aggregate query' as such to my knowledge, I just want the expression to return a "Y" if any of these four fields in the table are "Y" - note that "Y" is text field, not a yes/no, and most records are empty. The Age part of the query if functioning perfectly, if any of the fours age fields holds data then data is being returned by the expression (I don't actually care what the data is, so long as it is anything not null - if one record holds more than one age field populated then they are always the same, so catching any age value works, and *one* of them will be not null, I just don't know which one for any given record). The queries have 'append' in their names because this is leading to an append query that will be used to build a new table that has only one [Emp] field rather than four. This weekend I am going to try another way around this ( Sum(IIf([Emp ...]"",1,0))+Sum(IIf([Emp...]"",1,0)...) which may or may not work, but I am frustrated by this error message about trying to aggregate functions when the data is text and Access help and the Microsoft knowledge base do not seem to provide any clues as to how I am meant to aggregate a text function, or conversely, how to write an expression to work with text fields that does not incur an 'aggregate' error message. I think I have included everything. Any help would be greatly appreciated. Regards, Kai Richmond . |
#3
|
|||
|
|||
Text field causing trouble in query (continued)
Hi Kai,
Some alternatives to what Chris has aptly stated... If you need the "group by", then just add the expresion to your group by clause. SELECT qry_CIFs_APPEND_DATA_01.CIF, IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) AS AGE, IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N") AS EMP FROM qry_CIFs_APPEND_DATA_01 GROUP BY qry_CIFs_APPEND_DATA_01.CIF, IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N") ; or just wrap expression in aggregate like MAX SELECT qry_CIFs_APPEND_DATA_01.CIF, IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) AS AGE, Max(IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N")) AS EMP FROM qry_CIFs_APPEND_DATA_01 GROUP BY qry_CIFs_APPEND_DATA_01.CIF; I believe either solution will work. Gary Walter "KaiRich" wrote Hi, This is a continuation of a post a few days ago. Apologies about the time lag, but the question is from a contract I am working on only 1 day per week, evenings and weekends - and last night I was more concerned with writing a new job application. The two responses to my earlier post both said that my expression 'should' have worked and could I post the SQL - so I have included more info below: I have three columns in my query: customer ID(CIF), Age, Emp (there will be more when I solve this problem with text fields). The expressions in Age and Emp are each reading four columns (fields) in my table (as shown in SQL below). CIF is the table key and is an autonumber, Age is numerical, Emp is text. In the table the four Emp fields hold either "Y", "N", or "" (most are ""), for any Customer ID record: if any of the four Emp fields holds "Y" then I want the query expression to return "Y". This is the SQL: SELECT qry_CIFs_APPEND_DATA_01.CIF, IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) AS AGE, IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N") AS EMP FROM qry_CIFs_APPEND_DATA_01 GROUP BY qry_CIFs_APPEND_DATA_01.CIF; These are the expressions as written in the query's design view: AGE: IIf(Sum([Age Save])"",Sum([Age Save]),IIf(Sum([Age Inv])"",Sum([Age Inv]),IIf(Sum([Age Loan])"",Sum([Age Loan]),IIf(Sum([Age Over])"",Sum([Age Over]),"")))) - This works with no problems. EMP: IIf([Emp Save]="Y" Or [Emp Inv]="Y" Or [Emp Loan]="Y" Or [Emp Over]="Y","Y","N") - This returns an error. I am getting an error on the 'Emp' expression: IIf([Emp Save]="Y","Y","N") or IIF([Emp Save]="Y" Or [Emp Inv]="Y" Or...,"Y","N") gives error 'trying to use a function that is not part of an aggregate function'. If this were numbers I was working with I would simply use IIF(Sum([Emp Save])=1,,) etc. but I can't find a text function that I can aggregate with (as I can 'aggregate' any number field by simply wrapping it in Sum()). I am not trying an 'aggregate query' as such to my knowledge, I just want the expression to return a "Y" if any of these four fields in the table are "Y" - note that "Y" is text field, not a yes/no, and most records are empty. The Age part of the query if functioning perfectly, if any of the fours age fields holds data then data is being returned by the expression (I don't actually care what the data is, so long as it is anything not null - if one record holds more than one age field populated then they are always the same, so catching any age value works, and *one* of them will be not null, I just don't know which one for any given record). The queries have 'append' in their names because this is leading to an append query that will be used to build a new table that has only one [Emp] field rather than four. This weekend I am going to try another way around this ( Sum(IIf([Emp ....]"",1,0))+Sum(IIf([Emp...]"",1,0)...) which may or may not work, but I am frustrated by this error message about trying to aggregate functions when the data is text and Access help and the Microsoft knowledge base do not seem to provide any clues as to how I am meant to aggregate a text function, or conversely, how to write an expression to work with text fields that does not incur an 'aggregate' error message. I think I have included everything. Any help would be greatly appreciated. Regards, Kai Richmond |
Thread Tools | |
Display Modes | |
|
|