Index on calculated field

  • Thread starter Thread starter Greg Surratt
  • Start date Start date
G

Greg Surratt

I have an A2K database that includes two fields, Attempted and
Correct. I'd like to be able to index the database on the calculation

"Correct / Attempted"

I know that good design practice says not to store calculated fields,
so is there another way to create this index besides creating a query,
including the calculation and then sorting on the calculated field?
This is a flat file database, so I don't want to use a query if I
don't need it.

I tried making a third field in the table with a default value of
"Correct / Attempted", but it wouldn't accept the calculation.

Thanks,
Greg
 
I have an A2K database that includes two fields, Attempted and
Correct. I'd like to be able to index the database on the calculation

"Correct / Attempted"
Click to expand...

You can't: unlike some other DBMS's Access does not allow the creation
of indexes on calculated fields.
I know that good design practice says not to store calculated fields,
so is there another way to create this index besides creating a query,
including the calculation and then sorting on the calculated field?
This is a flat file database, so I don't want to use a query if I
don't need it.
Click to expand...

I'm not sure we're using the term index in the same way. An Index is a
pretty much concealed object in the .mdb file containing pointers to
records in a Table; it makes searching and sorting faster on that
field. Creating a query will not in its own right create an index, and
(as noted) Access doesn't let you create an Index object on a query at
all, only on a Table.

Saying that you don't want to use queries in Access is a little like
saying you don't want to use a transmission in your automobile!
Queries are ESSENTIAL tools to any use of Access; even if you're just
using a table datasheet and using the binoculars to find a record, or
the A-Z icon to sort them, Access will create an invisible query to do
so. You'll be dealing with a query in any case; why not just create
one yourself? It can be used in any way that you would use a Table.
 
You can't: unlike some other DBMS's Access does not allow the creation
of indexes on calculated fields.


I'm not sure we're using the term index in the same way. An Index is a
pretty much concealed object in the .mdb file containing pointers to
records in a Table; it makes searching and sorting faster on that
field. Creating a query will not in its own right create an index, and
(as noted) Access doesn't let you create an Index object on a query at
all, only on a Table.
Click to expand...

I think we are speaking the same language. I was using "index" as in
sort on this field.
Saying that you don't want to use queries in Access is a little like
saying you don't want to use a transmission in your automobile!
Queries are ESSENTIAL tools to any use of Access; even if you're just
using a table datasheet and using the binoculars to find a record, or
the A-Z icon to sort them, Access will create an invisible query to do
so. You'll be dealing with a query in any case; why not just create
one yourself? It can be used in any way that you would use a Table.
Click to expand...

Good answer, John. I guess a query is the way to do this after all.
My end goal is to process the records in the order of "Correct/Tried"
(my calculated field). I was looking for a plain vanilla answer, but
the query is only a painless single extra step.

Thanks for the insight.

Greg
 
I think we are speaking the same language. I was using "index" as in
sort on this field.
Click to expand...

Well, I interpreted it correctly: but a Sort and an Index are *two
different things*. You can create an Index on a table field (or
fields); this will take up x many bytes of storage space on your hard
disk within the .mdb file. You can also sort a table or a query on one
or more fields, whether or not an index exists on that field. If an
index exists, the JET database engine will (usually!) use the stored
index to make the sort operation run faster.
 
Back
Top