A Basic Index Question

[ApexData]

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

['69 Camaro]

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******** *************@n 51g2000cwc.goog legroups.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

[Bob Quintal]

"Ap******@gmail .com" <Ap******@gmail .comwrote in
news:11******** *************@n 51g2000cwc.goog legroups.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

[ApexData]

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

[Rick Brandt]

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

[ApexData]

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

['69 Camaro]

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******** *************@a 3g2000cwd.googl egroups.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

[Rick Brandt]

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

[Lyle Fairfield]

"Ap******@gmail .com" <Ap******@gmail .comwrote in
news:11******** **************@ 48g2000cwx.goog legroups.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

[Lyle Fairfield]

Lyle Fairfield <ly***********@ aim.comwrote in
news:Xn******** *************** **********@216. 221.81.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