Indexing fields in a make table query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rawood - 30 Jan 2006 14:29 GMT

I have an Access program that makes several tables and then runs queries from
them to create the final report. The database is fairly big (a couple of
tables with about 1 million rows each, and several others with a few hundred
thousand rows) and the program runs about 50 queries, so performance time can
be an issue and I think I should be using some indexes. How can I have the
"make table" queries include the indexes I want in the tables they create?

Reply to this Message

Marshall Barton - 30 Jan 2006 19:14 GMT

>I have an Access program that makes several tables and then runs queries from
>them to create the final report. The database is fairly big (a couple of
>tables with about 1 million rows each, and several others with a few hundred
>thousand rows) and the program runs about 50 queries, so performance time can
>be an issue and I think I should be using some indexes. How can I have the
>"make table" queries include the indexes I want in the tables they create?

An SELECT ... INTO query does not have that capability.
Even if it did, you would not like the slowness of adding
all those records and managing the indexes at the same time.

Immediately after you Execute the SELECT ... INTO query,
Execute an CREATE INDEX query to create each index.
See Access Help - Contents - Microsoft Jet SQL Reference for
details.

Signature

Marsh
MVP [MS Access]

Reply to this Message

rawood - 30 Jan 2006 20:47 GMT

It looks like this requires me to use SQL, which I don't know how to do. The
instructions also say that CREATE INDEX can't be used for non-Microsoft Jet
databases, and I don't know if I have a Jet database or not. It seems like
this solution is more sophisticated than I can handle - is there a beginner's
version of this, or am I out of luck?

> >I have an Access program that makes several tables and then runs queries from
> >them to create the final report. The database is fairly big (a couple of
[quoted text clipped - 11 lines]
> See Access Help - Contents - Microsoft Jet SQL Reference for
> details.

Reply to this Message

Marshall Barton - 30 Jan 2006 22:47 GMT

There are three ways to create an index.
    1. the table design window (beginner)
    2. SQL DDL statement (intermediate)
    2. VBA/DAO|ADO procedure (advanced)

If you don't feel up to using a CREATE INDEX query, then you
will have to run one of your make table queries, switch to
table design for the new table and create the index
manually.

Most likely, if you don't know what database engine you are
using, it will be Jet (the default out-of-the-box db engine
for Access). You really have to aware of what you are using
if it is anything other than Jet.

Out-of-luck is not a valid characterization or your
situation. You could always spend an hour or two to learn
how to construct a simple CREATE INDEX query, especially
with all the help you can get in these forums.

Signature

Marsh
MVP [MS Access]

>It looks like this requires me to use SQL, which I don't know how to do. The
>instructions also say that CREATE INDEX can't be used for non-Microsoft Jet
[quoted text clipped - 17 lines]
>> See Access Help - Contents - Microsoft Jet SQL Reference for
>> details.

Reply to this Message