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

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
.

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