Abend Query - Getting Not enough disk space error

[Douglas @ Helpdesk]

I have a user that created an access database. User created two tables and
then created a abend query. When she tries to run the query it just clocks
(moves about 3 bar spaces as far as completion) and eventually user gets an
error message "Not enough space on temporary disk"

User stated that it was on the a network drive and then tried to move it
over to the hard drive and user was still getting the same error message.

User has already tried to do a compact and repair database. and converted it
over to the 2002 version.

one of the tables has 22837 records and the other table has 21771 records.

would this cause the application to hang up when you go an abend query?

I have changed the File Page Size thinking that it might resolve the issue
and it didnt.

here is a copy of the SQL statement from the users Abend Query and it
appears to be ok.


INSERT INTO [Year Over Year Table] ( [PAYER CUST], [PAYER CUST NAME], [SHIP
TO], [PH1], [PH2 DESC], [PH2], [PH6 DESC], [PH6], [MATL DESC], [MATERIAL],
[PERIOD], [QUANTITY], [PH1 DESC], [Internal - External Customer], [Legal
Sales], [Legal Costs RA], [Mfg Plant], [Duties], [ASM], [Mfg (G)/L Value],
[YEAR] )

SELECT [2008 Plan Table].[Payer Code], [2008 Plan Table].[Payer Name], [2008
Plan Table].[Ship to Code], [2008 Plan Table].[Ph 1 Code], [2008 Plan
Table].[Ph 2 Description], [2008 Plan Table].[Ph 2 Code], [2008 Plan
Table].[Ph 6 Description], [2008 Plan Table].[Ph 6 Code], [2008 Plan
Table].[Material Description], [2008 Plan Table].[Material Code], [2008 Plan
Table].[Cal year / month], [2008 Plan Table].[Qty (MT)], [2008 Plan
Table].[Ph 1 Description], [2008 Plan Table].[Internal/External], [2008 Plan
Table].[Total Sales], [2008 Plan Table].[Total Std Mfg Cost], [2008 Plan
Table].[Mfg Plant Name], [2008 Plan Table].[Total Plan Duties], [2008 Plan
Table].[Correct ASM], [2008 Plan Table].[(G)/L Value], [2008 Plan
Table].[Year]

FROM [2008 Plan Table], [Year Over Year Table];


and ideas?

 

[John Spencer]

That query is attempting to add 497,184,327 (22,837 * 21771) records to the
table. Somehow I think that is not what the user really wants to do.

I think that the user is trying to add the 2008 plan table records to the
Year over Year table. If so change the FROM at the end

FROM [2008 Plan Table], [Year Over Year Table];

Change that to
FROM [2008 Plan Table]


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

That query is attempting to add 497,184,327 (22,837 * 21771) records to the
table. Somehow I think that is not what the user really wants to do.

Really? Ya think???

<bg>

Easy error to fall into with append queries... you think you need to include
the target table in the list of tables.

 

[Dale Fye]

The way this query is written, it is trying to do a cartesian join between
the [2008 Plan Table] and the [Year Over Year Table]. If these tables
contain ~ 23,000 and 22,000 records, then this query would generate
approximately 500 Million records, probably not what the user really wants to
do. Since the only reference to the [Year Over Year Table] is in the INSERT
portion of the statement, recommend removing that table name from the FROM
clause. Should read:

INSERT INTO [Year Over Year Table] (field list,.....)
SELECT fieldList, .....
FROM [2008 Plan Table]

HTH
Dale