Need Faster Query - 3073 evaded

[jacobusroos via AccessMonster.com]

I have 2 tables, the one is the Parent table which holds a column
TotalImprovedSQM this column is calculated by using the child table column
sum(improved) the criteria by which this happens is three column values in
both tables ERFNO PORTIONNO TOWNSHIP.

"Update MainTable set ImprovedSQMTotal = (SELECT Sum(structures.Improved) AS
ImprovementsSQMTotal FROM MainTable, structures where (MainTable.
BulkData1_ERFNO = structures.ERFNO) AND (MainTable.BulkData1_TOWNSHIPNAME =
structures.Township) AND (MainTable.BulkData1_PORTIONNO = structures.[Portion
No])) "

The above query in Access keeps givin me 3073 error and Both column do have
primary keys and IDs

Thus I wrote : Update Maintable
set ImprovedSQMTotal = DLookUp("sum(nz(Improved,0))","structures","(" &
MainTable.BulkData1_PORTIONNO & " = structures.[Portion No]) AND ('" &
MainTable.BulkData1_TOWNSHIPNAME & "' = structures.Township) AND (" &
MainTable.BulkData1_ERFNO & " = structures.ERFNO)")

This works but the problem is it takes exceptional long to do on 37000+
records

Is there a beter way, am I missing small details in my first attempt (the
first SQL statement) or is the second sql statement the only way ?

 

[John Spencer]

You could do this in two steps.

Step one create a temporary table that stores the needed fields

Use this query to populate the data into the temporary table
INSERT INTO TempTable(BulkData1_ERFNO
, BulkData1_TownshipName
, BulkData1_PortionNo
, ImprovementsSQMTotal

SELECT Maintable.BulkData1_ERFNO
, MainTable.BulkData1_TownshipName
, MainTable.BulkData1_PortionNo
, Sum(structures.Improved) AS ImprovementsSQMTotal
FROM MainTable INNER JOIN Structures
ON MainTable.BulkData1_ERFNO = structures.ERFNO AND
MainTable.BulkData1_TOWNSHIPNAME = structures.Township AND
MainTable.BulkData1_PORTIONNO = structures.[Portion No]
GROUP BY Maintable.BulkData1_ERFNO
, MainTable.BulkData1_TownshipName
, MainTable.BulkData1_PortionNo

Now use that table as the source for the update query.

UPDATE Maintable INNER JOIN TempTable as T
ON MainTable.BulkData1_ERFNO = T.ERFNO AND
MainTable.BulkData1_TOWNSHIPNAME = T.Township AND
MainTable.BulkData1_PORTIONNO = T.[Portion No]
SET ImprovedSQMTotal = [T].[ImprovementsSQMTotal]

Now you can delete the temporary table. Tony Toews has an example on
his site on how to set up and use a temporary table in a temporary
database and set a link to the temporary table. Once that is done you

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jerry Whittle]

There is a better way and it's to not have that column that you are
attempting to update in the table at all. It's usually a very bad idea to
have a column of data derived from other columns. In fact it breaks rules of
normalization - usually.

Instead you should calculate the data in forms, reports, and queries as
needed. That way you are sure that the result is the latest and not dependent
on the last time the table was updated.

There are exceptions such as when you need to store what happen at a point
of time such as an invoice.

 

[jacobusroos via AccessMonster.com]

Hi John,

I tried your example... well I am smiling partly because I didn't think about
that approach and secondly but more importantly , the whole process takes
about 90+% less time to execute.

It took about 3h00 to run my code, yours takes about 5min - 12min. Can't
thank you enough.

I am quite happy with your solution Your syntax helped me find and fix my
own syntax faults... Syntax is the mother of all frustration.

I do have another question : Access Tsql syntax differ so much from MSSQL. Is
there a handbook/reference that shows how to do the different TSQL statements
in Access, which grew we accustomed to in MSSQL ?

Best regards,
J.Jacobus Roos

 

[Bob Barrows]

I do have another question : Access Tsql syntax differ so much from
MSSQL.

For one thing, it's JetSQL in Jet (Access is not the database - Access is
the admin and development tool), and Transact-SQL in SQL Server.

Is there a handbook/reference that shows how to do the
different TSQL statements in Access, which grew we accustomed to in
MSSQL ?

This article goes from the POV of switching from Jet to MSSQL, but it should
give you the gist:
http://sqlserver2000.databases.aspf...ifferences-between-access-and-sql-server.html

What this article does not cover is the need in Jet to nest your table joins
in parentheses, which is not necessary in MSSQL and can trip up the
unsuspecting developer. The best technique when joining more than two tables
is to do it in Design View, then switch to SQL View to see what Jet demands
in the FROM clause.