Text field causing trouble in query (continued)

[Guest]

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 EM
FROM qry_CIFs_APPEND_DATA_0
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") o
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

 

[ChrisJ]

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

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.

 

[Gary Walter]

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

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

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.