Query takes hours

[Macroman]

MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard
drive

Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing

Table 2 has 423,000 records and contains the following fields
tbltwo_custID
tbltwo_easting
tbltwo_northing

Table 1 and Table 2 does have other fields but the ones I have listed are
the key ones to explain my problem. And that problem is that table 2
contains dirty data and table 1 the good data. For both tables the CustID is
unique and there are no CustID's in table 2 that are in table 1.

I am trying to extract from table 1 good data where the easting and northing
are close so I end up with a new table returning values from table 1 and
values from table 2. That way I can then re-populate later on the results
into table 1.

The query parameters as per design view

FIELD...........Expr1: Abs(tblone_easting - tbltwo_easting)
FIELD...........Expr2: Abs(tblone_northing - tbltwo_northing)

For each of these 2 fields I have a CRITERIA of "<10".

Understandably the query will take a while, upto now its been running for 9
hrs (on my spare PC at work). And what I have realised while testing on my
PC at home is that when I eventually get results I cannot export the results
without the query re-running.

So therefore;
Q1: Is there a quicker way of querying "tbltwo" against "tblone" as per my
query parameters above.
Q2: If I am lucky enough to return to work and find I have results how can I
export those results without the query re-running.

many many thanks

Macroman

[DFS]

Post the SQL statement and we'll take a look.

Macroman wrote:

MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb
Hard drive

Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing

Table 2 has 423,000 records and contains the following fields
tbltwo_custID
tbltwo_easting
tbltwo_northing

Table 1 and Table 2 does have other fields but the ones I have listed
are the key ones to explain my problem. And that problem is that
table 2 contains dirty data and table 1 the good data. For both
tables the CustID is unique and there are no CustID's in table 2 that
are in table 1.

I am trying to extract from table 1 good data where the easting and
northing are close so I end up with a new table returning values from
table 1 and values from table 2. That way I can then re-populate
later on the results into table 1.

The query parameters as per design view

FIELD...........Expr1: Abs(tblone_easting - tbltwo_easting)
FIELD...........Expr2: Abs(tblone_northing - tbltwo_northing)

For each of these 2 fields I have a CRITERIA of "<10".

Understandably the query will take a while, upto now its been running
for 9 hrs (on my spare PC at work). And what I have realised while
testing on my PC at home is that when I eventually get results I
cannot export the results without the query re-running.

So therefore;
Q1: Is there a quicker way of querying "tbltwo" against "tblone" as
per my query parameters above.
Q2: If I am lucky enough to return to work and find I have results
how can I export those results without the query re-running.

many many thanks

Macroman

[salad]

Macroman wrote:

MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard
drive

Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing

Table 2 has 423,000 records and contains the following fields
tbltwo_custID
tbltwo_easting
tbltwo_northing

Table 1 and Table 2 does have other fields but the ones I have listed are
the key ones to explain my problem. And that problem is that table 2
contains dirty data and table 1 the good data. For both tables the CustID is
unique and there are no CustID's in table 2 that are in table 1.

I am trying to extract from table 1 good data where the easting and northing
are close so I end up with a new table returning values from table 1 and
values from table 2. That way I can then re-populate later on the results
into table 1.

The query parameters as per design view

FIELD...........Expr1: Abs(tblone_easting - tbltwo_easting)
FIELD...........Expr2: Abs(tblone_northing - tbltwo_northing)

For each of these 2 fields I have a CRITERIA of "<10".

Understandably the query will take a while, upto now its been running for 9
hrs (on my spare PC at work). And what I have realised while testing on my
PC at home is that when I eventually get results I cannot export the results
without the query re-running.

So therefore;
Q1: Is there a quicker way of querying "tbltwo" against "tblone" as per my
query parameters above.
Q2: If I am lucky enough to return to work and find I have results how can I
export those results without the query re-running.

many many thanks

Macroman

You didn't show a query. I am wondering if somehow you got a Cartesian
Join going or something else wrong.

You should be able to create a query something like
Select CustID, "T1" As Table, easting, northing _
From Table1
Where Abs(tblone_easting - tbltwo_easting) < 10
Save it as query1
Now run it.
How long does it take to compute?

You should be able to write another query
Select CustID, "T2" As Table, easting, northing _
From Table2
Where Abs(tblone_easting - tbltwo_easting) < 10
Save it as query2
Now run it.
How long does it take to compute?

Now you can create another query that Unions them
Select Query1.* From Query1
UNION ALL
Select Query2.* From Query2
and save it as Query3
Now run it.

It shouldn't take longer than running the two queries by themselves.

[Bob Quintal]

"Macroman" <pd*********@btinternet.com> wrote in
news:dv**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:

MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM,
40Gb Hard drive

Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing

Table 2 has 423,000 records and contains the following fields
tbltwo_custID
tbltwo_easting
tbltwo_northing

Table 1 and Table 2 does have other fields but the ones I have
listed are the key ones to explain my problem. And that
problem is that table 2 contains dirty data and table 1 the
good data. For both tables the CustID is unique and there are
no CustID's in table 2 that are in table 1.

I am trying to extract from table 1 good data where the
easting and northing are close so I end up with a new table
returning values from table 1 and values from table 2. That
way I can then re-populate later on the results into table 1.

The query parameters as per design view

FIELD...........Expr1: Abs(tblone_easting - tbltwo_easting)
FIELD...........Expr2: Abs(tblone_northing - tbltwo_northing)

For each of these 2 fields I have a CRITERIA of "<10".

Understandably the query will take a while, upto now its been
running for 9 hrs (on my spare PC at work). And what I have
realised while testing on my PC at home is that when I
eventually get results I cannot export the results without the
query re-running.

So therefore;
Q1: Is there a quicker way of querying "tbltwo" against
"tblone" as per my query parameters above.
Q2: If I am lucky enough to return to work and find I have
results how can I export those results without the query
re-running.

many many thanks

Macroman

You are generating a product recordset of 167,000 x 423,000
records to be tested. That's going to take a long time.

The next time, generate a query on table one that calculates min
and max limits for your two fields. Use that query to test table
two values as between the limits in the query. This is still
slow, but faster than your way.

If you get a result set, try just copying the results to the
clipboard, if there aren't too many rows. {Ctrl-A}{Ctrl-C}, then
paste into your desired table.

saves re-running.
--
Bob Quintal

PA is y I've altered my email address.

[corey lawson]

"Macroman" <pd*********@btinternet.com> wrote in
news:dv**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:

MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb
Hard drive

Table 1 has 167,000 records and contains the following fields
tblone_custID
tblone_easting
tblone_northing

Table 2 has 423,000 records and contains the following fields
tbltwo_custID
tbltwo_easting
tbltwo_northing

Table 1 and Table 2 does have other fields but the ones I have listed
are the key ones to explain my problem. And that problem is that table
2 contains dirty data and table 1 the good data. For both tables the
CustID is unique and there are no CustID's in table 2 that are in
table 1.

I am trying to extract from table 1 good data where the easting and
northing are close so I end up with a new table returning values from
table 1 and values from table 2. That way I can then re-populate later
on the results into table 1.

The query parameters as per design view

FIELD...........Expr1: Abs(tblone_easting - tbltwo_easting)
FIELD...........Expr2: Abs(tblone_northing - tbltwo_northing)

For each of these 2 fields I have a CRITERIA of "<10".

So therefore;
Q1: Is there a quicker way of querying "tbltwo" against "tblone" as
per my query parameters above.
Q2: If I am lucky enough to return to work and find I have results how
can I export those results without the query re-running.

many many thanks

Macroman

Create indexes on the custID fields in both tables...

Then...

select t1.custid, t1.easting, t1.northing, t2.easting, t2.northing
from table1 as t1 inner join table2 as t2 on t1.custid = t2.custid
where ((t1.easting - t2.easting) between -10 and 10)
and ((t2.northing - t2.northing) between -10 and 10)

part of this is to avoid using the call to the VBA function ABS. Creating
the indexes should increase the speed of the query a bit, too. There is
not much point in indexing the easting and northing fields.

If the query is running right for you, then make a copy of the query and
make it into a "make table" query. Then export this new table instead.
and you only have to run the query once...