By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

A Basic Index Question

P: n/a
I have a table called EMPLOYEES with the following fields:
EmployeeID
LastName
FirstName
OtherData, etc...

In Table design:
I set EmployeeID as the primary index.
I created a MultiField index using LastName, FirstName and called it
idxLastFirst

I have a single bound form that that holds ALL the fields from my
table. It has the Record Source set to EMPLOYEES.
How do I bind the form to the idxLastFirst index I created, so that I
get the sort I want???
Do I have to create a Query? And, if so, what was the point of
creating the idxLastFirst index?

Thankyou

Dec 29 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Hi.
How do I bind the form to the idxLastFirst index I created, so that I
get the sort I want???
Do I have to create a Query?
If you want no flexibility on how the data is displayed, bind the form to the
table. In all other cases, create the query to display columns as desired and
the records in the order desired, then bind the form to that query.
Do I have to create a Query? And, if so, what was the point of
creating the idxLastFirst index?
To help Jet optimize the retrieval of records. And if the index is unique and
doesn't allow NULL's, it will prevent duplicate records in the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
<Ap******@gmail.comwrote in message
news:11*********************@n51g2000cwc.googlegro ups.com...
>I have a table called EMPLOYEES with the following fields:
EmployeeID
LastName
FirstName
OtherData, etc...

In Table design:
I set EmployeeID as the primary index.
I created a MultiField index using LastName, FirstName and called it
idxLastFirst

I have a single bound form that that holds ALL the fields from my
table. It has the Record Source set to EMPLOYEES.
How do I bind the form to the idxLastFirst index I created, so that I
get the sort I want???
Do I have to create a Query? And, if so, what was the point of
creating the idxLastFirst index?

Thankyou

Dec 29 '06 #2

P: n/a
"Ap******@gmail.com" <Ap******@gmail.comwrote in
news:11*********************@n51g2000cwc.googlegro ups.com:
I have a table called EMPLOYEES with the following fields:
EmployeeID
LastName
FirstName
OtherData, etc...

In Table design:
I set EmployeeID as the primary index.
I created a MultiField index using LastName, FirstName and
called it idxLastFirst

I have a single bound form that that holds ALL the fields from
my table. It has the Record Source set to EMPLOYEES.
How do I bind the form to the idxLastFirst index I created, so
that I get the sort I want???
Do I have to create a Query? And, if so, what was the point
of creating the idxLastFirst index?

Thankyou
Use the query. You created idxLastFirst so the query would run
much faster :-)

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Dec 29 '06 #3

P: n/a

Hi

I took your advice and built a query using the query builder:
SELECT [T-PERS].* FROM [T-PERS] ORDER BY [T-PERS].LAST, [T-PERS].FIRST;

It still makes no sense to me! Why build a multifield index
idxLastFirst at the table level and then do it again at the query
level. My record sort, for rec-to-rec viewing is based on the query
now, and has nothing to do with idxLastFirst which was originally
intended for this purpose??? Why even have it??? My key index is
EmpID for table cross linking.

Also, the SQL stmt created does not appear as though it defines a TRUE
multifield index???

Thanks for your comments
Greg

Dec 29 '06 #4

P: n/a
Ap******@gmail.com wrote:
Hi

I took your advice and built a query using the query builder:
SELECT [T-PERS].* FROM [T-PERS] ORDER BY [T-PERS].LAST,
[T-PERS].FIRST;

It still makes no sense to me! Why build a multifield index
idxLastFirst at the table level and then do it again at the query
level. My record sort, for rec-to-rec viewing is based on the query
now, and has nothing to do with idxLastFirst which was originally
intended for this purpose??? Why even have it??? My key index is
EmpID for table cross linking.

Also, the SQL stmt created does not appear as though it defines a TRUE
multifield index???

Thanks for your comments
Greg
You are not "building an index" when you apply a sort order in a query and you
are not establishing a sort order in a table when you create an index. These
are completely separate operations.

When you run your query and the query optimizer sees that you want a particular
sort order or are joining on a field or are applying criteria on a field it will
see if there are any indexes on those fields that will makes its job more
efficient. If it finds any such indexes it will use them thus making your query
faster than it would have been if the indexes did not exist.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 29 '06 #5

P: n/a
Thankyou for clarifying that Rick.
That having been said, I am on target with my approach for performance?

SELECT [T-PERS].* FROM [T-PERS] ORDER BY [T-PERS].LAST, [T-PERS].FIRST;

Will the above query perform better knowing that the index idxLastFirst
exists?

Do I need to make the query's ORDER BY somehow more specific?

ThankYou
Greg

Dec 29 '06 #6

P: n/a
Hi.
the SQL stmt created does not appear as though it defines a TRUE
multifield index???
To add to Rick's explanation, it appears that you were confusing the two
different operations. One may define a multicolumn index on a table using SQL,
but the syntax is very different than your SELECT query. It's called DDL (Data
Definition Language) SQL. For example:

CREATE INDEX IndexName
ON TableName (ColName1, ColName2);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
<Ap******@gmail.comwrote in message
news:11*********************@a3g2000cwd.googlegrou ps.com...
>
Hi

I took your advice and built a query using the query builder:
SELECT [T-PERS].* FROM [T-PERS] ORDER BY [T-PERS].LAST, [T-PERS].FIRST;

It still makes no sense to me! Why build a multifield index
idxLastFirst at the table level and then do it again at the query
level. My record sort, for rec-to-rec viewing is based on the query
now, and has nothing to do with idxLastFirst which was originally
intended for this purpose??? Why even have it??? My key index is
EmpID for table cross linking.

Also, the SQL stmt created does not appear as though it defines a TRUE
multifield index???

Thanks for your comments
Greg

Dec 29 '06 #7

P: n/a
Ap******@gmail.com wrote:
Thankyou for clarifying that Rick.
That having been said, I am on target with my approach for
performance?

SELECT [T-PERS].* FROM [T-PERS] ORDER BY [T-PERS].LAST,
[T-PERS].FIRST;

Will the above query perform better knowing that the index
idxLastFirst exists?

Do I need to make the query's ORDER BY somehow more specific?
No. The optimizer should see the index and use it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 29 '06 #8

P: n/a
"Ap******@gmail.com" <Ap******@gmail.comwrote in
news:11**********************@48g2000cwx.googlegro ups.com:
Thankyou for clarifying that Rick.
That having been said, I am on target with my approach for performance?

SELECT [T-PERS].* FROM [T-PERS] ORDER BY [T-PERS].LAST, [T-PERS].FIRST;

Will the above query perform better knowing that the index idxLastFirst
exists?

Do I need to make the query's ORDER BY somehow more specific?

ThankYou
Greg
No. Years ago MS bought FoxPro in order to get their Rushmore technology
which will optimize queries very well. So JET/Rushmore uses whatever
indexing it decides is most useful.

This is the plan JET uses to run a similar query:

--- Query3 ---

- Inputs to Query -
Table 'Employees'
Using index 'idxName'
Having Indexes:
idxName 9 entries, 1 page, 9 values
which has 2 columns, fixed
- End inputs to Query -

01) Scan table 'Employees'
Using index 'idxName'

--
lyle fairfield
Dec 30 '06 #9

P: n/a
Lyle Fairfield <ly***********@aim.comwrote in
news:Xn*********************************@216.221.8 1.119:
No. Years ago MS bought FoxPro in order to get their Rushmore technology
which will optimize queries very well. So JET/Rushmore uses whatever
indexing it decides is most useful.
As an old fox (PRO user) I exaggerated Rushmore's role here. TTBOMK
Rushmore is called into play when one sets conditions that require the
instersection or union of sets of records, and optimizes performance by
performing the operations on pertinent indexes.

--
lyle fairfield
Dec 30 '06 #10

P: n/a
Lyle Fairfield wrote:
Lyle Fairfield <ly***********@aim.comwrote in
news:Xn*********************************@216.221.8 1.119:
No. Years ago MS bought FoxPro in order to get their Rushmore
technology which will optimize queries very well. So JET/Rushmore
uses whatever indexing it decides is most useful.

As an old fox (PRO user) I exaggerated Rushmore's role here. TTBOMK
Rushmore is called into play when one sets conditions that require the
instersection or union of sets of records, and optimizes performance
by performing the operations on pertinent indexes.
Access (and SQL Server) really spoil you because it seems that both optimizers
are really really good. On our IBM server we can run query analyzer tools that
will recommend that specific indexes should be added only to find out that the
system does not use them after they are created. UDB400 just *loves* to scan
tables.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Dec 30 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.