Query is Crashing Access 2003 sp3

  • Thread starter Thread starter Pat Coleman
  • Start date Start date
P

Pat Coleman

Here is my query:

SELECT [Weight Bronx Wednesday].NUM, Sum([Weight Bronx Wednesday].Weight) AS
SumOfWeight
FROM [Weight Bronx Wednesday]
GROUP BY [Weight Bronx Wednesday].NUM;

My other query where the above query pulls the data from called 'Weight
Bronx Wednesday' lists all the line items associated with invoice numbers
[NUM] and the line item weight [Weight]

I want to get the total weight of each [NUM] so I have this above query.

If I try any other of the parameters available in the 'Group by' dialog box
in design view, such as [COUNT] or [AVG], the query will work no problem. It
is crashing when I try to use [SUM]

I have all the latest updates from officeupdate.com site.

When I run it I get a pop up box saying that Access needs to close down and
send error report toMicrosoft.

This is a summary of error:

AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msaccess.exe ModVer: 11.0.8166.0 ModStamp:46437912
fDebug: 0 Offset: 000bba09


Could this be because I am querying a query ? How many queries can I query,
if you know what I mean. How many queries can I drill down through ? ie can
query1 get data from query2, who in turn gets data from query3, who in turn
gets data from query4 etc. etc....
 
Access supports nesting of queries to 50 levels, so something else must be
causing this crash. (That's from the Access 2007 specs.)

You certainly want to ensure the Name AutoCorrect boxes are unchecked:
http://allenbrowne.com/bug-03.html

Also worth doing a compact/repair, in case an index has gone bad.

There are several other things that can crash Access. Outer joins on tables
that contain yes/no fields is a common one:
http://allenbrowne.com/bug-14.html

It can also occur if Access gets confused about the data type. If might be
informative to ask Access what it considers to be the data type of this
field. Open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("Query1").Fields("SumOfWeight").Type
Replace Query1 with your query name.
Results will be one of the DataTypeEnum constants. If it helps, turn it into
text with the FieldTypeName() function from here:
http://allenbrowne.com/func-06.html

If you are still stuck, you may need to post the SQL for [Weight Bronx
Wednesday]. Reserved words, outer joins, subqueries, VBA function calls,
undeclared parameters etc can mess up the types.
 
[Weight Bronx Wednesday] pulls its data from [Drivers Report]

Weight Bronx Wednesday is this:

SELECT [Drivers Report].NUM, [Drivers Report].INFO, [Drivers Report].Weight
FROM [Drivers Report]
WHERE ((([Drivers Report].INFO)="Bronx Wednesday"));


Drivers report is this:

SELECT SO.NUM, SO.SHIPTONAME, CUSTOMFIELDVIEW.INFO, SOITEM.DESCRIPTION,
SOITEM.TOTALPRICE, (SELECT (product.weight * soitem.qtytofulfill) FROM
product WHERE soitem.productid = product.id) AS Weight
FROM (SOITEM INNER JOIN SO ON SOITEM.SOID = SO.ID) INNER JOIN
CUSTOMFIELDVIEW ON SO.ID = CUSTOMFIELDVIEW.RECORDID
WHERE (((CUSTOMFIELDVIEW.CFID)=25) AND ((SO.STATUSID)=20 Or
(SO.STATUSID)=25));

Drivers report data is pulled from tables connected to Access by ODBC (
Firebird variety)






Allen Browne said:
Access supports nesting of queries to 50 levels, so something else must be
causing this crash. (That's from the Access 2007 specs.)

You certainly want to ensure the Name AutoCorrect boxes are unchecked:
http://allenbrowne.com/bug-03.html

Also worth doing a compact/repair, in case an index has gone bad.

There are several other things that can crash Access. Outer joins on
tables that contain yes/no fields is a common one:
http://allenbrowne.com/bug-14.html

It can also occur if Access gets confused about the data type. If might be
informative to ask Access what it considers to be the data type of this
field. Open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("Query1").Fields("SumOfWeight").Type
Replace Query1 with your query name.
Results will be one of the DataTypeEnum constants. If it helps, turn it
into text with the FieldTypeName() function from here:
http://allenbrowne.com/func-06.html

If you are still stuck, you may need to post the SQL for [Weight Bronx
Wednesday]. Reserved words, outer joins, subqueries, VBA function calls,
undeclared parameters etc can mess up the types.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pat Coleman said:
Here is my query:

SELECT [Weight Bronx Wednesday].NUM, Sum([Weight Bronx Wednesday].Weight)
AS SumOfWeight
FROM [Weight Bronx Wednesday]
GROUP BY [Weight Bronx Wednesday].NUM;

My other query where the above query pulls the data from called 'Weight
Bronx Wednesday' lists all the line items associated with invoice numbers
[NUM] and the line item weight [Weight]

I want to get the total weight of each [NUM] so I have this above query.

If I try any other of the parameters available in the 'Group by' dialog
box in design view, such as [COUNT] or [AVG], the query will work no
problem. It is crashing when I try to use [SUM]

I have all the latest updates from officeupdate.com site.

When I run it I get a pop up box saying that Access needs to close down
and send error report toMicrosoft.

This is a summary of error:

AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msaccess.exe ModVer: 11.0.8166.0 ModStamp:46437912
fDebug: 0 Offset: 000bba09


Could this be because I am querying a query ? How many queries can I
query, if you know what I mean. How many queries can I drill down through
? ie can query1 get data from query2, who in turn gets data from query3,
who in turn gets data from query4 etc. etc....
Click to expand...
Click to expand...
 
Okay, Pat, that's all fair enough.

I can't comment on whether the ODBC drivers for Firebird are a factor as
I've not used them.

Instead of using the Product table in a subquery, you might try including it
in the main query. Use an outer join if needed. (I noticed the calculated
field has no alias in the subquery: Access seems to prefer it if you do
provide one.)

Presumably you've tried the things suggested in the previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Pat Coleman said:
[Weight Bronx Wednesday] pulls its data from [Drivers Report]

Weight Bronx Wednesday is this:

SELECT [Drivers Report].NUM, [Drivers Report].INFO, [Drivers
Report].Weight
FROM [Drivers Report]
WHERE ((([Drivers Report].INFO)="Bronx Wednesday"));


Drivers report is this:

SELECT SO.NUM, SO.SHIPTONAME, CUSTOMFIELDVIEW.INFO, SOITEM.DESCRIPTION,
SOITEM.TOTALPRICE, (SELECT (product.weight * soitem.qtytofulfill) FROM
product WHERE soitem.productid = product.id) AS Weight
FROM (SOITEM INNER JOIN SO ON SOITEM.SOID = SO.ID) INNER JOIN
CUSTOMFIELDVIEW ON SO.ID = CUSTOMFIELDVIEW.RECORDID
WHERE (((CUSTOMFIELDVIEW.CFID)=25) AND ((SO.STATUSID)=20 Or
(SO.STATUSID)=25));

Drivers report data is pulled from tables connected to Access by ODBC (
Firebird variety)

Allen Browne said:
Access supports nesting of queries to 50 levels, so something else must
be causing this crash. (That's from the Access 2007 specs.)

You certainly want to ensure the Name AutoCorrect boxes are unchecked:
http://allenbrowne.com/bug-03.html

Also worth doing a compact/repair, in case an index has gone bad.

There are several other things that can crash Access. Outer joins on
tables that contain yes/no fields is a common one:
http://allenbrowne.com/bug-14.html

It can also occur if Access gets confused about the data type. If might
be informative to ask Access what it considers to be the data type of
this field. Open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("Query1").Fields("SumOfWeight").Type
Replace Query1 with your query name.
Results will be one of the DataTypeEnum constants. If it helps, turn it
into text with the FieldTypeName() function from here:
http://allenbrowne.com/func-06.html

If you are still stuck, you may need to post the SQL for [Weight Bronx
Wednesday]. Reserved words, outer joins, subqueries, VBA function calls,
undeclared parameters etc can mess up the types.

Pat Coleman said:
Here is my query:

SELECT [Weight Bronx Wednesday].NUM, Sum([Weight Bronx
Wednesday].Weight) AS SumOfWeight
FROM [Weight Bronx Wednesday]
GROUP BY [Weight Bronx Wednesday].NUM;

My other query where the above query pulls the data from called 'Weight
Bronx Wednesday' lists all the line items associated with invoice
numbers [NUM] and the line item weight [Weight]

I want to get the total weight of each [NUM] so I have this above query.

If I try any other of the parameters available in the 'Group by' dialog
box in design view, such as [COUNT] or [AVG], the query will work no
problem. It is crashing when I try to use [SUM]

I have all the latest updates from officeupdate.com site.

When I run it I get a pop up box saying that Access needs to close down
and send error report toMicrosoft.

This is a summary of error:

AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msaccess.exe ModVer: 11.0.8166.0 ModStamp:46437912
fDebug: 0 Offset: 000bba09


Could this be because I am querying a query ? How many queries can I
query, if you know what I mean. How many queries can I drill down
through ? ie can query1 get data from query2, who in turn gets data from
query3, who in turn gets data from query4 etc. etc....
Click to expand...
Click to expand...
Click to expand...
 
Back
Top