aggregate

I used the COUNT function in a query to count the number of fields. I gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and I
get an error message telling me that I tried to execute a query the doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?

Could you post the SQL for your queries?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Brenda @ AIM" wrote in message
...
I used the COUNT function in a query to count the number of fields. I
gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?

Hi Brenda,

I don't suppose you could post your SQL? Did you say you are counting
fields? Did you mean rows (records)?

Any time you use an aggregate function in a query you must "group" the
non-aggregated fields. Lets say you had 3 fields in your query:

[Order Period], [Product Name] and [Quantity Ordered]. [Quantity Ordered]
is the aggregated field. You would structure the SQL something like this:

--NB: This SQL not tested

SELECT Format(OrderDate, "YYYY/MM") AS [Order Period],
ProductName AS Product,
COUNT(*) AS [Quantity Ordered]
FROM ((Orders AS O INNER JOIN [Order Details] AS OD ON O.OrderID =
OD.OrderID)
INNER JOIN Products P ON OD.ProductID = P.ProductID)
GROUP BY Format(OrderDate, "YYYY/MM") , ProductName

The number of rows are counted for each change in Period and Product Name.
Do not include the alias names (i.e. [Order Period], etc) in the GROUP BY
clause.

Jamie

"Brenda @ AIM" wrote in message
...
I used the COUNT function in a query to count the number of fields. I gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?

This is my first query
SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);

This is the query I'm getting the error message on.
SELECT LEFT(UNIZIP,3) AS ZIP3, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION ALL
SELECT UNIZIP, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP;



"Lynn Trapp" wrote:

Could you post the SQL for your queries?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Brenda @ AIM" wrote in message
...
I used the COUNT function in a query to count the number of fields. I
gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?

This is my first query:
SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);
Here is the query I'm getting the error message on:
SELECT LEFT(UNIZIP,3) AS ZIP3, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION ALL
SELECT UNIZIP, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP;

"Jamie Richards" wrote:

Hi Brenda,

I don't suppose you could post your SQL? Did you say you are counting
fields? Did you mean rows (records)?

Any time you use an aggregate function in a query you must "group" the
non-aggregated fields. Lets say you had 3 fields in your query:

[Order Period], [Product Name] and [Quantity Ordered]. [Quantity Ordered]
is the aggregated field. You would structure the SQL something like this:

--NB: This SQL not tested

SELECT Format(OrderDate, "YYYY/MM") AS [Order Period],
ProductName AS Product,
COUNT(*) AS [Quantity Ordered]
FROM ((Orders AS O INNER JOIN [Order Details] AS OD ON O.OrderID =
OD.OrderID)
INNER JOIN Products P ON OD.ProductID = P.ProductID)
GROUP BY Format(OrderDate, "YYYY/MM") , ProductName

The number of rows are counted for each change in Period and Product Name.
Do not include the alias names (i.e. [Order Period], etc) in the GROUP BY
clause.

Jamie

"Brenda @ AIM" wrote in message
...
I used the COUNT function in a query to count the number of fields. I gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?

I realize now that I need to put COUNTOFZIP in my GROUPBY clause. But if I
write it like this (GROUP BY UNIZIP, COUNTOFZIP) then I have multiple
instances of my UNIZIP in the result. I need these grouped together. If i
write it this way (GROUP BY COUNTOFZIP, UNIZIP) then it is grouped by the
count(ascending). I need it grouped by UNIZIP (ascending)

"Brenda @ AIM" wrote:

This is my first query
SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);

This is the query I'm getting the error message on.
SELECT LEFT(UNIZIP,3) AS ZIP3, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION ALL
SELECT UNIZIP, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP;



"Lynn Trapp" wrote:

Could you post the SQL for your queries?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Brenda @ AIM" wrote in message
...
I used the COUNT function in a query to count the number of fields. I
gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?

Get it to be in the order you want by using an Order By clause at the end of
the second query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Brenda @ AIM" wrote in message
...
I realize now that I need to put COUNTOFZIP in my GROUPBY clause. But if
I
write it like this (GROUP BY UNIZIP, COUNTOFZIP) then I have multiple
instances of my UNIZIP in the result. I need these grouped together. If
i
write it this way (GROUP BY COUNTOFZIP, UNIZIP) then it is grouped by the
count(ascending). I need it grouped by UNIZIP (ascending)

"Brenda @ AIM" wrote:

This is my first query
SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);

This is the query I'm getting the error message on.
SELECT LEFT(UNIZIP,3) AS ZIP3, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION ALL
SELECT UNIZIP, COUNTOFZIP
FROM QRYUNIQUEZIP
WHERE COUNTOFZIP 15
GROUP BY UNIZIP;



"Lynn Trapp" wrote:

Could you post the SQL for your queries?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Brenda @ AIM" wrote in message
...
I used the COUNT function in a query to count the number of fields.
I
gave
it the name COUNTOF.
Now I want to make a query from that query and I try to select
COUNTOF and
I
get an error message telling me that I tried to execute a query the
doesn't
include COUNTOF as part of an aggregate function.
Why can't I just select it as a field name in the second query?