Query returns incomplete results

[Tegger]

<This message also multiposted to microsoft.public.access.queries. I would
properly crosspost, but my provider or somebody tends to strip crossposts,
so crossposting is unreliable for me. Sorry.>

A few workstations for which I'm responsible run Access 2000 as part of
Office 2000. These workstations are using a very simple database consisting
of a table with numerous text fields, a Select Query, and a form for
entering the terms to be used by the query. The database is centrally
located, with workstations using it over the network.

The point of the database is simply to be able to filter the table's
contents through the query so as to be able to display in the query only
records that match the terms entered into the form fields. The query
contains Criteria like this:
Like "*" & [forms]![Docket Search]![JobDesc] & "*"

This setup has worked just fine for six years, but all of a sudden
(starting last two weeks?) refuses to reliably return complete results.
There appears to be no pattern to the problem other than it seems to
involve entries made during February of this year. Some appear, some do
not. There are no blank fields in the table.

What am I missing? And thanks for any help that's available.

 

[Tegger]

<This message also multiposted to microsoft.public.access.queries.

Actually to comp.databases.ms-access. In spite of trying to type carefully,
I still managed to mess up.

 

[Allen Browne]

Suggestions:

1. Compact/repair the back end.
A bad index can cause this:
http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries

2. Split
Each user needs a *separate* copy of the front end, with linked tables to a
shared back end. If you don't already have it set up that way, see:
http://allenbrowne.com/ser-01.html

3. Apply updates
If the 2 above don't solve it, and it works on some workstations and not on
others, you are looking for differences between the workstations. Make sure
they all have SP3 for Access 2000:
http://support.microsoft.com/kb/276367
and at least SP8 for JET 4:
http://support.microsoft.com/kb/239114

4. Debug the query
If still stuck, and it happens for just this one query, there may be an
ambiguity in the query statement. Typically it's data type matching, order
of execution of outer joins, etc.

 

[Tegger]

Suggestions:

1. Compact/repair the back end.
A bad index can cause this:
http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries

2. Split
Each user needs a *separate* copy of the front end, with linked tables
to a shared back end. If you don't already have it set up that way,
see:
http://allenbrowne.com/ser-01.html

3. Apply updates
If the 2 above don't solve it, and it works on some workstations and
not on others, you are looking for differences between the
workstations. Make sure they all have SP3 for Access 2000:
http://support.microsoft.com/kb/276367
and at least SP8 for JET 4:
http://support.microsoft.com/kb/239114

4. Debug the query
If still stuck, and it happens for just this one query, there may be
an ambiguity in the query statement. Typically it's data type
matching, order of execution of outer joins, etc.

Thanks very much for the reply.

I've done an update to SP3 (which required SR1 before it would "take").
This appears to also have updated JET 4 to SP8.

Have not split the database yet.

It does appear to be an Indexing problem, at the moment. I tried your steps
in the link referenced in Suggestion 1: The resulting table was missing the
very records that the Search could not find. There are not very many
records missing, so I can add those back by hand.
(Of course, I've kept a backup copy of the original database).

My problem now is managing to get the Search query working again. The old
Search query doesn't want to find any records at all in the newly-created
table, even though the table name and the field names are all identical to
the original table. I recreated the Index as it was in the original table.
There were no relationships in the Search query other than the table
itself.

This sort of thing appears to be over my head, and I'm getting lost trying
to do it. I may have to find a local programmer who can fix this for me.

Thanks again.

 

[Tegger]

..

My problem now is managing to get the Search query working again. The
old Search query doesn't want to find any records at all in the
newly-created table, even though the table name and the field names
are all identical to the original table. I recreated the Index as it
was in the original table. There were no relationships in the Search
query other than the table itself.

A partial update, in case anyone's interested...

The "incomplete results" thing turned out to be user error. Another user of
the database had re-sorted the query results, then saved the change to the
layout, unbeknownst to me, and not realizing what he had done. This meant
that the results were no longer arranged by the column we were originally
expecting it to be arranged by. The "missing" results were actually there,
but not presented in the order that was expected.

After discovering this, I changed the layout back and all is well again.

This sort of thing appears to be over my head, and I'm getting lost
trying to do it. I may have to find a local programmer who can fix
this for me.

Notwithstanding the above discovery, I still want to get this database re-
done properly.

I did find somebody in Texas who seemed to know his stuff, but my boss has
told me I have to use a local UNIX/Linux guy instead (a friend of his).
This guy says he can do something that will integrate with Windows, while
still somehow having his app run in *nix. I don't get how he's going to do
that.

Anyway, thanks for the help here, even though not much came of it.