create index

  • Thread starter Thread starter W
  • Start date Start date
W

W

Hi,

I have to look up data in a table, where there is no particular index now.
I now do it with a findfirst strCriteria, and then, in a loop I use findnext
strCriteria.

This all goes terribly slow.

How can I create an index on the fly, for my table ItemsPurchased, where I
have the combined index on the following fields, and in this order :

CustomerId (string)
DateOfPurchase (date)
ItemPurchased (string)

I then want to be able to verify my data, but I think I will not have to use
findfirst/findnext ?

And then, in fine, I want to drop the index.

Thanks for your help,

W
 
What's the goal here?

I think you are trying to find out whether there are any duplicates of the 3
fields? If so, this kind of thing might work:

SELECT CustomerId, DateOfPurchase, ItemPurchased,
Count (CustomerId) AS HowMany
FROM ItemsPurchased
GROUP BY CustomerId, DateOfPurchase, ItemPurchased
HAVING Count (CustomerId) > 1;
 
Hi,

I have to look up data in a table, where there is no particular index now.
I now do it with a findfirst strCriteria, and then, in a loop I use findnext
strCriteria.

This all goes terribly slow.

How can I create an index on the fly, for my table ItemsPurchased, where I
have the combined index on the following fields, and in this order :

CustomerId (string)
DateOfPurchase (date)
ItemPurchased (string)

I then want to be able to verify my data, but I think I will not have to use
findfirst/findnext ?

And then, in fine, I want to drop the index.

Thanks for your help,

W
Click to expand...

If you think a full table scan is slow, then you'll be very disappointed at
how much slower it will be to create a full table index, write it to disk, run
the (much faster now!) search, and drop the index. This combination of
operations will be VERY timeconsuming and will also bloat your database.

Note that Access can be pretty clever at using existing indexes. You do NOT
need to create a one-time use three field index to search three fields, if
CustomerID, DateOfPurchase and ItemPurchased all have permanent stored
indexes; and I'd be inclined to think that they should, just on general
principles, since they all look like fields which will be routinely searched
or sorted. I'd just be sure these three fields are indexed and that the
database has been compacted and the query edited and saved (to be sure that
the query statistics are up to date).
 
Back
Top