Index Issue I Believe

[JCM]

I have a query that finds records in a table based upon a field on a form.

The criteria is LIKE "*" & [FormField] so the user can enter the last 4
digits of the Field to give the user a selection. This query has been run
fine for years. Now it is only finding some of the records that meet the
criteria even though they are all present in the table.

I suspected the index for the field that I search on, so I ran a compact and
repair. The search again return the same records as before. ( It is
returning 7 records of 66 that it should return). I then decided to export
that table to a blank DB. Deleted the records in the suspect table. Then
copied the table from the New Blank DB to the production DB and imported the
records to the problem table. The search from the query now returns the
correct 66 records.

I think that confirms the index is the issue however my question is:

Is their an easier way to rebuild the index? I sure thought there should be.
By the way, the table has over 66,000 records.

Thanks
Johnny

 

[Jellifish]

It's more likely to be your data. Check for spaces at the end of the data
etc.

Most likely, the index is not even being used as the first part of your
criteria is the wildcard.

 

[aaron.kempf]

well that's funny you should ask.. because with SQL Server, you can
index _PART_ of a field..
dead serious-- try it
-------------------------------------------------------
alter table employees
add firstnamefirstletter = left(firstname, 1)

create index IX_employees_firstnamefirstletter
on dbo.employees (firstnamefirstletter )

 

[Bob Quintal]

m:

well that's funny you should ask.. because with SQL Server, you
can index _PART_ of a field..
dead serious-- try it
-------------------------------------------------------
alter table employees
add firstnamefirstletter = left(firstname, 1)

create index IX_employees_firstnamefirstletter
on dbo.employees (firstnamefirstletter )

In dBase III you could Index on left(firstname,1). dBase III doesn't
even have a present, much less a future.

 

[aaron.kempf]

meanwhile, SQL Server is the worlds most popular database.. and you
can build indexes-- that would help the OP tremendously

 

[Tony Toews [MVP]]

meanwhile, SQL Server is the worlds most popular database.. and you
can build indexes-- that would help the OP tremendously

You assume that the user is always going to want to search on the first letter or the
first two letters or some other fixed number.

This is not the case as users will want to search on a variable number of letters in
a name.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

Tony;

Jet doesn't support this capability-- and SQL will allow you to find
records faster.

For example.. we could also build this index on the RIGHT 4
characters--
Isn't that what the OP asked for?