ERROR 3125 switching to "totals" aggregate function

M

M.D. Exausted

I have a query which grabs a set of records based on a set of criteria. I
then need to perform some analysis on that record set, however when I enable
the "totals" line, I get error 3125 " " is not a valid name...." If I make a
table of the query instead, and then create a new query from the new table,
I'm able to utilize the totals functions. However, most of these record sets
are anywhere from 5k - 30k records, and I need to run dozens of these
queries. I have a table set up with multivalued fields to save the search
criteria, and use the .value property to filter the results.

Here's the code before:

SELECT Master02.[XNSGO No], Master02.Style, Master02.Flute, Master02.ECT,
Master02.Burst, Master02.Length, Master02.Width, Master02.Depth,
Master02.[Square Footage], Master02.USB, Master02.[Total QTY],
Master02.[Price (IND)], Master02.[Cost (Total)]
FROM Master02, [Search Query]
WHERE (((Master02.Style)=[Search Query]![Style].[Value]) AND
((Master02.Flute)=[Search Query]![Search.Flute.Value]) AND
((Master02.ECT)=[Search Query]![Search.ECT.Value]) AND
((Master02.Burst)=[Search Query]![Search.Burst.Value]) AND
((Master02.USB)=[Search Query]![Search.USB.Value]));

And this is the code after I hit the "totals" option:

SELECT Master02.[XNSGO No], Master02.Style, Master02.Flute, Master02.ECT,
Master02.Burst, Master02.Length, Master02.Width, Master02.Depth,
Master02.[Square Footage], Master02.USB, Master02.[Total QTY],
Master02.[Price (IND)], Master02.[Cost (Total)]
FROM Master02, [Search Query]
GROUP BY Master02.[XNSGO No], Master02.Style, Master02.Flute, Master02.ECT,
Master02.Burst, Master02.Length, Master02.Width, Master02.Depth,
Master02.[Square Footage], Master02.USB, Master02.[Total QTY],
Master02.[Price (IND)], Master02.[Cost (Total)]
HAVING (((Master02.Style)=[Search Query]![Style].[Value]) AND
((Master02.Flute)=[Search Query]![Search.Flute.Value]) AND
((Master02.ECT)=[Search Query]![Search.ECT.Value]) AND
((Master02.Burst)=[Search Query]![Search.Burst.Value]) AND
((Master02.USB)=[Search Query]![Search.USB.Value]));

This gives me the error code, regardless of how I configure the grouping
(I'm aware that this unconfigured grouping would return no unique clusters).
 
P

Phil Smith

I would also look at removing the parenthesis and any spaces from your
field names as well. Replace them all with underscores. I think your
problem might go away if you do. I have run into similar issues with
similar names, changed the enames, eliminated the issues...


M.D. Exausted said:
I have a query which grabs a set of records based on a set of
criteria. I then need to perform some analysis on that record set,
however when I enable the "totals" line, I get error 3125 " " is not a
valid name...." If I make a table of the query instead, and then
create a new query from the new table, I'm able to utilize the totals
functions. However, most of these record sets are anywhere from 5k -
30k records, and I need to run dozens of these queries. I have a table
set up with multivalued fields to save the search criteria, and use
the .value property to filter the results.

Here's the code before:

SELECT Master02.[XNSGO No], Master02.Style, Master02.Flute,
Master02.ECT, Master02.Burst, Master02.Length, Master02.Width,
Master02.Depth, Master02.[Square Footage], Master02.USB,
Master02.[Total QTY], Master02.[Price (IND)], Master02.[Cost (Total)]
FROM Master02, [Search Query]
WHERE (((Master02.Style)=[Search Query]![Style].[Value]) AND
((Master02.Flute)=[Search Query]![Search.Flute.Value]) AND
((Master02.ECT)=[Search Query]![Search.ECT.Value]) AND
((Master02.Burst)=[Search Query]![Search.Burst.Value]) AND
((Master02.USB)=[Search Query]![Search.USB.Value]));

And this is the code after I hit the "totals" option:

SELECT Master02.[XNSGO No], Master02.Style, Master02.Flute,
Master02.ECT, Master02.Burst, Master02.Length, Master02.Width,
Master02.Depth, Master02.[Square Footage], Master02.USB,
Master02.[Total QTY], Master02.[Price (IND)], Master02.[Cost (Total)]
FROM Master02, [Search Query]
GROUP BY Master02.[XNSGO No], Master02.Style, Master02.Flute,
Master02.ECT, Master02.Burst, Master02.Length, Master02.Width,
Master02.Depth, Master02.[Square Footage], Master02.USB,
Master02.[Total QTY], Master02.[Price (IND)], Master02.[Cost (Total)]
HAVING (((Master02.Style)=[Search Query]![Style].[Value]) AND
((Master02.Flute)=[Search Query]![Search.Flute.Value]) AND
((Master02.ECT)=[Search Query]![Search.ECT.Value]) AND
((Master02.Burst)=[Search Query]![Search.Burst.Value]) AND
((Master02.USB)=[Search Query]![Search.USB.Value]));

This gives me the error code, regardless of how I configure the
grouping (I'm aware that this unconfigured grouping would return no
unique clusters).
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you tried this - SUM the Qty and Cost, group all other columns.
You might also want to SUM or AVG the Price, but I wasn't sure about
that. The reason for a "Total" query is that you are going to use an
Aggregate function on one or more of the columns listed in the SELECT
clause.

(I used table aliases for ease of reading.)

SELECT M.[XNSGO No], M.Style, M.Flute, M.ECT, M.Burst, M.Length,
M.Width, M.Depth, M.[Square Footage], M.USB, M.[Price (IND)],

SUM(M.[Total QTY]) As TotalQty,

SUM(M.[Cost (Total)]) As TotalCost

FROM Master02 As M INNER JOIN [Search Query] As Q
ON M.Style=Q.[Style] AND M.Flute=Q.[Search.Flute]
AND M.ECT=Q.[Search.ECT] AND M.Burst=Q.[Search.Burst]
AND M.USB=Q.[Search.USB]
GROUP BY M.[XNSGO No], M.Style, M.Flute, M.ECT,
M.Burst, M.Length, M.Width, M.Depth,
M.[Square Footage], M.USB, M.[Price (IND)]

Warning: Using a column (Field) name like "Search.Flute" is troublesome
'cuz SQL uses TableName.ColumnName syntax and your naming convention
could be confusing without the square brackets! The usual separator
between words in a column name is the under-line character, e.g.:
"search_flute".

HTH,
Click to expand...
 
M

M.D. Exausted

The Search.Flute is table "search" field "flute." However it is in the sql
code, is how access 2007 translated it; I did not write the sql code.

I went through and removed all parenthesis and spaces from all field names
in all tables and queries, and I still get the error as soon as I add the
totals row. I'm very familiar with how to configure the aggregate functions
(from design-view, that is), but the configuration itself doesn't seem to be
the problem; if I remove all fields from the query except one--one that isn't
a multi-valued field, instead of the name error, I get an infinite operation
that I have to ctrl-break. This is the sql that produces this:

SELECT Master02.XNSGO_No
FROM Master02, Search_Query
GROUP BY Master02.XNSGO_No;

Master02 is a table and Search_Query is a query that queries a separate
table called Search. I don't know if that's relevant. And as I previously
noted, the query produces results so long as I don't invoke the aggregate
functions.

Any clue?

Phil Smith said:
I would also look at removing the parenthesis and any spaces from your
field names as well. Replace them all with underscores. I think your
problem might go away if you do. I have run into similar issues with
similar names, changed the enames, eliminated the issues...


M.D. Exausted said:
I have a query which grabs a set of records based on a set of
criteria. I then need to perform some analysis on that record set,
however when I enable the "totals" line, I get error 3125 " " is not a
valid name...." If I make a table of the query instead, and then
create a new query from the new table, I'm able to utilize the totals
functions. However, most of these record sets are anywhere from 5k -
30k records, and I need to run dozens of these queries. I have a table
set up with multivalued fields to save the search criteria, and use
the .value property to filter the results.

Here's the code before:

SELECT Master02.[XNSGO No], Master02.Style, Master02.Flute,
Master02.ECT, Master02.Burst, Master02.Length, Master02.Width,
Master02.Depth, Master02.[Square Footage], Master02.USB,
Master02.[Total QTY], Master02.[Price (IND)], Master02.[Cost (Total)]
FROM Master02, [Search Query]
WHERE (((Master02.Style)=[Search Query]![Style].[Value]) AND
((Master02.Flute)=[Search Query]![Search.Flute.Value]) AND
((Master02.ECT)=[Search Query]![Search.ECT.Value]) AND
((Master02.Burst)=[Search Query]![Search.Burst.Value]) AND
((Master02.USB)=[Search Query]![Search.USB.Value]));

And this is the code after I hit the "totals" option:

SELECT Master02.[XNSGO No], Master02.Style, Master02.Flute,
Master02.ECT, Master02.Burst, Master02.Length, Master02.Width,
Master02.Depth, Master02.[Square Footage], Master02.USB,
Master02.[Total QTY], Master02.[Price (IND)], Master02.[Cost (Total)]
FROM Master02, [Search Query]
GROUP BY Master02.[XNSGO No], Master02.Style, Master02.Flute,
Master02.ECT, Master02.Burst, Master02.Length, Master02.Width,
Master02.Depth, Master02.[Square Footage], Master02.USB,
Master02.[Total QTY], Master02.[Price (IND)], Master02.[Cost (Total)]
HAVING (((Master02.Style)=[Search Query]![Style].[Value]) AND
((Master02.Flute)=[Search Query]![Search.Flute.Value]) AND
((Master02.ECT)=[Search Query]![Search.ECT.Value]) AND
((Master02.Burst)=[Search Query]![Search.Burst.Value]) AND
((Master02.USB)=[Search Query]![Search.USB.Value]));

This gives me the error code, regardless of how I configure the
grouping (I'm aware that this unconfigured grouping would return no
unique clusters).
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you tried this - SUM the Qty and Cost, group all other columns.
You might also want to SUM or AVG the Price, but I wasn't sure about
that. The reason for a "Total" query is that you are going to use an
Aggregate function on one or more of the columns listed in the SELECT
clause.

(I used table aliases for ease of reading.)

SELECT M.[XNSGO No], M.Style, M.Flute, M.ECT, M.Burst, M.Length,
M.Width, M.Depth, M.[Square Footage], M.USB, M.[Price (IND)],

SUM(M.[Total QTY]) As TotalQty,

SUM(M.[Cost (Total)]) As TotalCost

FROM Master02 As M INNER JOIN [Search Query] As Q
ON M.Style=Q.[Style] AND M.Flute=Q.[Search.Flute]
AND M.ECT=Q.[Search.ECT] AND M.Burst=Q.[Search.Burst]
AND M.USB=Q.[Search.USB]
GROUP BY M.[XNSGO No], M.Style, M.Flute, M.ECT,
M.Burst, M.Length, M.Width, M.Depth,
M.[Square Footage], M.USB, M.[Price (IND)]

Warning: Using a column (Field) name like "Search.Flute" is troublesome
'cuz SQL uses TableName.ColumnName syntax and your naming convention
could be confusing without the square brackets! The usual separator
between words in a column name is the under-line character, e.g.:
"search_flute".

HTH,
Click to expand...
Click to expand...
 
Top