aggregate function
in [General]
|
Prev: samira
Next: Albert Kallal
From: ddoblank on 22 Apr 2010 20:56 I have been running a series of queries now for about a year and a half with no problems, that I know of anyways. Today, I found out that a job didn't show up in a report that I run daily and I can not figure out why. I took a look in one query and the job is there, I take a look in the next query that runs off that one and it is gone, so I entered a filter to see if I could just pull that one job out and I got this error message. You tried to execute a query that does not include the specified expression 'qryJrun_Step_5.[Kitting Job #] Like “27489765-001” as part of an aggregate function. Any ideas? The format for the column hasn't changed, the data for this job is the same as every other job that is showing up.
From: John W. Vinson on 22 Apr 2010 21:20 On Thu, 22 Apr 2010 17:56:01 -0700, ddoblank <ddoblank(a)discussions.microsoft.com> wrote: >I have been running a series of queries now for about a year and a half with >no problems, that I know of anyways. Today, I found out that a job didn't >show up in a report that I run daily and I can not figure out why. I took a >look in one query and the job is there, I take a look in the next query that >runs off that one and it is gone, so I entered a filter to see if I could >just pull that one job out and I got this error message. > >You tried to execute a query that does not include the specified expression >�qryJrun_Step_5.[Kitting Job #] Like �27489765-001� as part of an aggregate >function. > >Any ideas? The format for the column hasn't changed, the data for this job >is the same as every other job that is showing up. This is odd. It sounds like the query thinks you're using the expression qryJrun_Step_5.[Kitting Job #] Like �27489765-001� as the name of a field, which doesn't make much sense at all. Did you introduce the "smart quotes" �� in place of "" in copying the message to this forum? or are they in the query somewhere? Perhaps you should open the entire query in SQL view and post it here. -- John W. Vinson [MVP]
From: ddoblank on 23 Apr 2010 20:32 Hi John, I may have inadvertently typed in the wrong quotation marks on this request. In the query itself, I just typed in the job number and tabbed out. The program put in the quotation marks it normally uses. With that said, here is the SQL statement from this query. SELECT qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem Qty], Sum(qryJrun_Step_5.[Prev Ordered]) AS [SumOfPrev Ordered], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #] FROM qryJrun_Step_5 GROUP BY qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem Qty], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #] HAVING (((qryJrun_Step_5.[Kitting Job #])="27489765-001")); I don't normally have a job # filter running here. I just entered in this query to see if it would pull this job out only. If I remove the filter, the query runs fine and this job is in there. "John W. Vinson" wrote: > On Thu, 22 Apr 2010 17:56:01 -0700, ddoblank > <ddoblank(a)discussions.microsoft.com> wrote: > > >I have been running a series of queries now for about a year and a half with > >no problems, that I know of anyways. Today, I found out that a job didn't > >show up in a report that I run daily and I can not figure out why. I took a > >look in one query and the job is there, I take a look in the next query that > >runs off that one and it is gone, so I entered a filter to see if I could > >just pull that one job out and I got this error message. > > > >You tried to execute a query that does not include the specified expression > >'qryJrun_Step_5.[Kitting Job #] Like “27489765-001” as part of an aggregate > >function. > > > >Any ideas? The format for the column hasn't changed, the data for this job > >is the same as every other job that is showing up. > > This is odd. It sounds like the query thinks you're using the expression > > qryJrun_Step_5.[Kitting Job #] Like “27489765-001” > > as the name of a field, which doesn't make much sense at all. > > Did you introduce the "smart quotes" “” in place of "" in copying the message > to this forum? or are they in the query somewhere? > > Perhaps you should open the entire query in SQL view and post it here. > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 24 Apr 2010 19:12 On Fri, 23 Apr 2010 17:32:01 -0700, ddoblank <ddoblank(a)discussions.microsoft.com> wrote: >Hi John, > >I may have inadvertently typed in the wrong quotation marks on this request. >In the query itself, I just typed in the job number and tabbed out. The >program put in the quotation marks it normally uses. With that said, here is >the SQL statement from this query. Hrm. Not sure why it's getting that error, but try moving the criterion to the WHERE clause: SELECT qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem Qty], Sum(qryJrun_Step_5.[Prev Ordered]) AS [SumOfPrev Ordered], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #] FROM qryJrun_Step_5 GROUP BY qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem Qty], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #] WHERE (((qryJrun_Step_5.[Kitting Job #])="27489765-001")); -- John W. Vinson [MVP]
From: ddoblank on 25 Apr 2010 09:08
The WHERE clause is not normally there. I just put it in to see if I could see this one job. Normally this query does not have a WHERE clause of any kind in it. Maybe I am not explainging things clearly. The job number that I have in the WHERE clause is in this query when I run it without, however, there is a report that prints out that shows all of the jobs that I have to order for. This particular job didn't show up in this report even though it is in the query. So I put the WHERE clause in to see what would happen and that is when I got the "aggregate function" error in my original message. Does that help at all? Thanks Darren "John W. Vinson" wrote: > On Fri, 23 Apr 2010 17:32:01 -0700, ddoblank > <ddoblank(a)discussions.microsoft.com> wrote: > > >Hi John, > > > >I may have inadvertently typed in the wrong quotation marks on this request. > >In the query itself, I just typed in the job number and tabbed out. The > >program put in the quotation marks it normally uses. With that said, here is > >the SQL statement from this query. > > Hrm. Not sure why it's getting that error, but try moving the criterion to the > WHERE clause: > > SELECT qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem > Qty], Sum(qryJrun_Step_5.[Prev Ordered]) AS [SumOfPrev Ordered], > qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #] > FROM qryJrun_Step_5 > GROUP BY qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem > Qty], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #] > WHERE (((qryJrun_Step_5.[Kitting Job #])="27489765-001")); > > -- > > John W. Vinson [MVP] > . > |