Access freezing as I scroll down queried results

[JJEWELL]

I have a table named D that contains 707005 records. It was built from an
imported txt file that contained coordinates and concentrations in two
columns with headers lines on each page giving day and group identifications.
The ID field shown in my excerpt below was an autonumber created during the
import.

ID - East1 - North1 - Conc1 - East2 - North2 - Conc2 - Day - Group
===========================================
13631 - null - null - null - null - null - null - 1 - null
13632 - null - null - null - null - null - null - null - ALLST
13643 - 671815.6 - 4062860.7 - 1.11 - 671695.2 - 4062526.5 - 1.53 -null -
null
13644 - 671817.5 - 4062456.2 - 1.32 - 671818.3 - 4062298.7 - 1.40 - null -
null
13645 - 671818.2 - 4062763 - 1.14 - 674132.4 - 4065962.5 - 0.02 - null - null
.....37 more lines with actual data that look the same as the last three
above, then the header rows repeat. After the Day = 1 and Group = ALLST
header rows repeat four times (with 40 records of actual data under the
second and third ones and 21 records under the last one for a total of 141
records of actual data and a grand total of 149 records - actual data plus
header rows), the Group changes to something else, e.g., COP, then after the
13 different Group possibilies are presented (we are at 1937 records at this
point), the Day changes to 2 and the 13 Groups repeat again giving
information for the same 141 coordinates (actual 282, but they are in two
columns). This goes on for 365 days, which give the GRAND TOTAL of 707005
records (149 records per group/day combination and 13 groups and 365 days).

Ultimately, I want to move the second column of coordinates/concentration
data to the bottom of the first column (via union query, append [to table]
queries - whatever - I can figure this out), but the issue at hand is that I
want to fill in the null Day and Group values in each of the records that
contain actual data. I wrote the following query to do this. You'll notice
that this query only returns records with actual data, i.e., the header rows
are removed by on including records where East1 is not null. This should
reduce the number of records to 669045 (141 * 13 * 365). Not sure this
matters, but I thought I would point it out.

SELECT D.ID, D.East1, D.North1, D.Conc1, D.East2, D.North2, D.Conc2, (SELECT
TOP 1 Day FROM D AS Dupe1 WHERE Dupe1.Day Is Not Null AND Dupe1.ID < D.ID
ORDER BY Dupe1.ID DESC) AS DayR, (SELECT TOP 1 Group FROM D AS Dupe2 WHERE
Dupe2.Group Is Not Null AND Dupe2.ID < D.ID ORDER BY Dupe2.ID DESC) AS GroupR
FROM D
WHERE (((D.East1) Is Not Null))
ORDER BY D.ID;

The query works, or appears to, but when I scroll down the query results - I
can get to about record 125 - Access freezes.

Any explanations, solutions, work arounds, etc. are appreciated greatly.

 

[S.Clark]

Append the data to a local table, then scroll through the table instead of
the query.

Otherwise, return less number of records, so that Access doesn't have to do
so much processing as you're scrolling.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

I have a table named D that contains 707005 records. It was built from an
imported txt file that contained coordinates and concentrations in two
columns with headers lines on each page giving day and group identifications.
The ID field shown in my excerpt below was an autonumber created during the
import.

ID - East1 - North1 - Conc1 - East2 - North2 - Conc2 - Day - Group
===========================================
13631 - null - null - null - null - null - null - 1 - null
13632 - null - null - null - null - null - null - null - ALLST
13643 - 671815.6 - 4062860.7 - 1.11 - 671695.2 - 4062526.5 - 1.53 -null -
null
13644 - 671817.5 - 4062456.2 - 1.32 - 671818.3 - 4062298.7 - 1.40 - null -
null
13645 - 671818.2 - 4062763 - 1.14 - 674132.4 - 4065962.5 - 0.02 - null - null
....37 more lines with actual data that look the same as the last three
above, then the header rows repeat. After the Day = 1 and Group = ALLST
header rows repeat four times (with 40 records of actual data under the
second and third ones and 21 records under the last one for a total of 141
records of actual data and a grand total of 149 records - actual data plus
header rows), the Group changes to something else, e.g., COP, then after the
13 different Group possibilies are presented (we are at 1937 records at this
point), the Day changes to 2 and the 13 Groups repeat again giving
information for the same 141 coordinates (actual 282, but they are in two
columns). This goes on for 365 days, which give the GRAND TOTAL of 707005
records (149 records per group/day combination and 13 groups and 365 days).

Ultimately, I want to move the second column of coordinates/concentration
data to the bottom of the first column (via union query, append [to table]
queries - whatever - I can figure this out), but the issue at hand is that I
want to fill in the null Day and Group values in each of the records that
contain actual data. I wrote the following query to do this. You'll notice
that this query only returns records with actual data, i.e., the header rows
are removed by on including records where East1 is not null. This should
reduce the number of records to 669045 (141 * 13 * 365). Not sure this
matters, but I thought I would point it out.

SELECT D.ID, D.East1, D.North1, D.Conc1, D.East2, D.North2, D.Conc2, (SELECT
TOP 1 Day FROM D AS Dupe1 WHERE Dupe1.Day Is Not Null AND Dupe1.ID < D.ID
ORDER BY Dupe1.ID DESC) AS DayR, (SELECT TOP 1 Group FROM D AS Dupe2 WHERE
Dupe2.Group Is Not Null AND Dupe2.ID < D.ID ORDER BY Dupe2.ID DESC) AS GroupR
FROM D
WHERE (((D.East1) Is Not Null))
ORDER BY D.ID;

The query works, or appears to, but when I scroll down the query results - I
can get to about record 125 - Access freezes.

Any explanations, solutions, work arounds, etc. are appreciated greatly.

 

[JJEWELL]

I changed the query to an append query. It is running now. Who knows how
long it will take. Hopefully this works, but I'm still confused about the
freezing up issue. I have tried, as you suggested, reducing the number of
records by limiting the query results to to TOP 150, but Access still freezes
just before scrolling to the bottom of the list, e.g., it will show record
138, but not any more.

Also, I tried to just update the source table rather than creating a new
recordset. When trying to run the update query, I get the error message
"Operation must use an updateable query." What is this talking about?

Lastly, I tried splitting the query so each one only contained one of the
subqueries - one to fill in the Day values and one to fill in the Group
values. I thought that maybe my query was just too complicated. The same
freezing up issue happens to the split queries. You can scroll so far down
the results until Access freezes and you have to Ctrl+Alt+Delete.

Append the data to a local table, then scroll through the table instead of
the query.

Otherwise, return less number of records, so that Access doesn't have to do
so much processing as you're scrolling.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

I have a table named D that contains 707005 records. It was built from an
imported txt file that contained coordinates and concentrations in two
columns with headers lines on each page giving day and group identifications.
The ID field shown in my excerpt below was an autonumber created during the
import.

ID - East1 - North1 - Conc1 - East2 - North2 - Conc2 - Day - Group
===========================================
13631 - null - null - null - null - null - null - 1 - null
13632 - null - null - null - null - null - null - null - ALLST
13643 - 671815.6 - 4062860.7 - 1.11 - 671695.2 - 4062526.5 - 1.53 -null -
null
13644 - 671817.5 - 4062456.2 - 1.32 - 671818.3 - 4062298.7 - 1.40 - null -
null
13645 - 671818.2 - 4062763 - 1.14 - 674132.4 - 4065962.5 - 0.02 - null - null
....37 more lines with actual data that look the same as the last three
above, then the header rows repeat. After the Day = 1 and Group = ALLST
header rows repeat four times (with 40 records of actual data under the
second and third ones and 21 records under the last one for a total of 141
records of actual data and a grand total of 149 records - actual data plus
header rows), the Group changes to something else, e.g., COP, then after the
13 different Group possibilies are presented (we are at 1937 records at this
point), the Day changes to 2 and the 13 Groups repeat again giving
information for the same 141 coordinates (actual 282, but they are in two
columns). This goes on for 365 days, which give the GRAND TOTAL of 707005
records (149 records per group/day combination and 13 groups and 365 days).

Ultimately, I want to move the second column of coordinates/concentration
data to the bottom of the first column (via union query, append [to table]
queries - whatever - I can figure this out), but the issue at hand is that I
want to fill in the null Day and Group values in each of the records that
contain actual data. I wrote the following query to do this. You'll notice
that this query only returns records with actual data, i.e., the header rows
are removed by on including records where East1 is not null. This should
reduce the number of records to 669045 (141 * 13 * 365). Not sure this
matters, but I thought I would point it out.

SELECT D.ID, D.East1, D.North1, D.Conc1, D.East2, D.North2, D.Conc2, (SELECT
TOP 1 Day FROM D AS Dupe1 WHERE Dupe1.Day Is Not Null AND Dupe1.ID < D.ID
ORDER BY Dupe1.ID DESC) AS DayR, (SELECT TOP 1 Group FROM D AS Dupe2 WHERE
Dupe2.Group Is Not Null AND Dupe2.ID < D.ID ORDER BY Dupe2.ID DESC) AS GroupR
FROM D
WHERE (((D.East1) Is Not Null))
ORDER BY D.ID;

The query works, or appears to, but when I scroll down the query results - I
can get to about record 125 - Access freezes.

Any explanations, solutions, work arounds, etc. are appreciated greatly.