Access 97 Different Row Counts for Snapshot or Dynaset query

[Bill Hutchison]

I have a query that returns different results (3508 rows for snapshot,
6288 for dynaset) and that is the only thing I change to get the
different results. When I try to make a table from the query, it
makes 3508 rows. I also know that there are 6288 unique rows in the
results. Is there some configuration choice that is causing the
snapshot version to return the wrong row count?

Thanks for your help on this one. If it wasn't that the Dynaset takes
so long to scroll through the records when generating text output
(half an hour vs a couple of seconds for the snapshot) I wouldn't mind
using the dynaset.

So I guess I'm hoping for either a solution to the wrong snapshot
count or a way to improve the speed of the dynaset.

bi***@gci.net

[Allen Browne]

Strange one.

First thing to do will be a compact and repair (Tools menu.) It is just
possible that the difference is due to an bad index, and a repair could fix
that.

If that doesn't solve the problem, can you confirm that you have SR2 for
Access 97? If not, grab the SP from:
http://support.microsoft.com/kb/151261

Next, search your entire hard disk for msjet35.dll. There should be only one
copy, probably in windows\system32. Sometimes you could end up with
multiples, which meant the patch you thought you were running was acually
different.

Once you pin it down to one, see if you need an update:
http://support.microsoft.com/kb/172733

If those things don't apply, is this running on a dual-processor machine?
http://support.microsoft.com/kb/178650

If none of that helps, it is time to start examining the SQL statement, to
see if this is a known JET bug. There's lots of them, so it can take some
time to track down, particularly if the SQL statement is complex, or based
on other lower-level queries, subqueries, disparate joins, literals in
joins, etc. Post the SQL statement with info about the fields used in joins,
criteria, and grouping.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bill Hutchison" <bi***@gci.netwrote in message
news:45**************@news.gci.net...

>I have a query that returns different results (3508 rows for snapshot,
6288 for dynaset) and that is the only thing I change to get the
different results. When I try to make a table from the query, it
makes 3508 rows. I also know that there are 6288 unique rows in the
results. Is there some configuration choice that is causing the
snapshot version to return the wrong row count?

Thanks for your help on this one. If it wasn't that the Dynaset takes
so long to scroll through the records when generating text output
(half an hour vs a couple of seconds for the snapshot) I wouldn't mind
using the dynaset.

So I guess I'm hoping for either a solution to the wrong snapshot
count or a way to improve the speed of the dynaset.

bi***@gci.net

[Bill Hutchison]

Thanks to Allen Browne for his advice so far. It turns out I already
had the Jet SP3. Here is the query which results in different row
counts. It is based on a query of the JCN table plus two tables
generated from crosstab queries of scheduled start and scheduled
finish dates, the tables being a limited set of the project activities
from the crosstab queries of an activities table. The joins are on
the JCN columns, sorted on the first three columns of the [JCN SCHED
QUERY].

SELECT [JCN SCHED QUERY].LOC, [JCN SCHED QUERY].FAC, [JCN SCHED
QUERY].JCN, [JCN SCHED QUERY].ST, [JCN SCHED QUERY].WORK_LOC_DESC,
LS.S00, LS.S08, LS.S10, LS.S21, LS.S44, LS.S80, LS.SComp, LS.SClose,
LF.F00, LF.F08, LF.F10, LF.F21, LF.F44, LF.F80, LF.FComp, LF.FClose
FROM ([JCN SCHED QUERY] INNER JOIN LF ON [JCN SCHED QUERY].JCN =
LF.JCN) INNER JOIN LS ON LF.JCN = LS.JCN
ORDER BY [JCN SCHED QUERY].LOC, [JCN SCHED QUERY].FAC, [JCN SCHED
QUERY].JCN
WITH OWNERACCESS OPTION;
On Wed, 14 Mar 2007 14:47:36 GMT, bi***@gci.net (Bill Hutchison)
wrote:

>I have a query that returns different results (3508 rows for snapshot,
6288 for dynaset) and that is the only thing I change to get the
different results. When I try to make a table from the query, it
makes 3508 rows. I also know that there are 6288 unique rows in the
results. Is there some configuration choice that is causing the
snapshot version to return the wrong row count?

Thanks for your help on this one. If it wasn't that the Dynaset takes
so long to scroll through the records when generating text output
(half an hour vs a couple of seconds for the snapshot) I wouldn't mind
using the dynaset.

So I guess I'm hoping for either a solution to the wrong snapshot
count or a way to improve the speed of the dynaset.

bi***@gci.net

[Allen Browne]

Okay, Bill, I take it you are certain that you have SP2 for Office 97, and
that you have only one copy of msjet35.dll on your hard disk, and it is not
a dual core processor, and that the compact/repair did not solve the
problem.

The query statement looks innocuous enough. But it might depend on the
queries underneath it, so you will need to drill down further and look at
those as well. Particularly, any criteria on calculated fields? If so, they
may need to be explicitly typecast:
http://allenbrowne.com/ser-45.html

Likewise, if there are any parameters, explicitly declare them so they are
typed (Parameters on Query menu, in query design view), but do not declare
the parameters applied on fields of type Text, since that could trigger this
bug:
http://allenbrowne.com/bug-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bill Hutchison" <bi***@gci.netwrote in message
news:46***************@news.gci.net...

Thanks to Allen Browne for his advice so far. It turns out I already
had the Jet SP3. Here is the query which results in different row
counts. It is based on a query of the JCN table plus two tables
generated from crosstab queries of scheduled start and scheduled
finish dates, the tables being a limited set of the project activities
from the crosstab queries of an activities table. The joins are on
the JCN columns, sorted on the first three columns of the
[JCN SCHED QUERY].

SELECT [JCN SCHED QUERY].LOC,
[JCN SCHED QUERY].FAC,
[JCN SCHED QUERY].JCN,
JCN SCHED QUERY].ST,
[JCN SCHED QUERY].WORK_LOC_DESC,
LS.S00, LS.S08, LS.S10, LS.S21, LS.S44,
LS.S80, LS.SComp, LS.SClose, LF.F00,
LF.F08, LF.F10, LF.F21, LF.F44,
LF.F80, LF.FComp, LF.FClose
FROM ([JCN SCHED QUERY] INNER JOIN LF
ON [JCN SCHED QUERY].JCN = LF.JCN)
INNER JOIN LS ON LF.JCN = LS.JCN
ORDER BY [JCN SCHED QUERY].LOC,
[JCN SCHED QUERY].FAC,
[JCN SCHED QUERY].JCN
WITH OWNERACCESS OPTION;

>

On Wed, 14 Mar 2007 14:47:36 GMT, bi***@gci.net (Bill Hutchison)
wrote:

>>I have a query that returns different results (3508 rows for snapshot,
6288 for dynaset) and that is the only thing I change to get the
different results. When I try to make a table from the query, it
makes 3508 rows. I also know that there are 6288 unique rows in the
results. Is there some configuration choice that is causing the
snapshot version to return the wrong row count?

Thanks for your help on this one. If it wasn't that the Dynaset takes
so long to scroll through the records when generating text output
(half an hour vs a couple of seconds for the snapshot) I wouldn't mind
using the dynaset.

So I guess I'm hoping for either a solution to the wrong snapshot
count or a way to improve the speed of the dynaset.

bi***@gci.net