Indexing fields in a make table query

[Guest]

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?

 

[Marshall Barton]

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.

 

[Guest]

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?

 

[Marshall Barton]

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.