Link to home
Start Free TrialLog in
Avatar of DrD
DrD

asked on

Sorting a database table

How do you sort an mdb table using VB? I have three  columns: UnitID (integer), SampleDate (mm/dd/yyyy), TestResult (integer). The table contains data like

UnitID       SampleDate      TestResult
 1           2/4/2000          3
 1           2/5/2000          101
 1           1/2/1988          4
 3           2/2/2001          56
 2           1/1/1999          12

I want the table to look like this...

UnitID       SampleDate       TestResult
 1            1/2/1988          4
 1            2/4/2000          3
 1            2/5/2000         101
 2            1/1/1999          12
 3            2/2/2001          56

In other words: Sort by UnitID (Ascending), then by date
(Ascending)

Note: this is a Table not a Query that I want to sort..

Thanks

David

 
Avatar of Z_Beeblebrox
Z_Beeblebrox

Hi,

Tables do not have sort orders, when you view them from inside access or from vb, the order returned will be unguaranteeable. If you want to display the data in a table, you must use an order by in a query.

Zaphod.
Or specify the recordset to use a certain predefined index.
make a 2-field index on that table and set that index
and now when you access that table it will be in the order you want
ie: make an index lets say "IDDate"
make it a 2 field index ie: UnitID and Date
when ever you want the table sorted by UnitID and Date just say
MyTable.Index = "IDDate"
ofcource the table is not sorted in the true sence but you will access it in the index order, this for all purposes has the same effect as a really(physical)sorted table
If you really want a table to be sorted a certain way, you have to create a copy of the table, sort the original table data, and transfer it to the new table, then delete the original and rename the new one back to the original name.

Unless you have a really good reason, DO NOT do this.  For one thing, if you have any foreign keys to the table's primary key, they will be pointing to the wrong records.

As mentioned, the proper "make" a table sorted is to use "order by", or to create a query which does the same thing and allows you to access the query without using the order by on the table (although I don't think you'll get any speed benefit.)
>Unless you have a really good reason, DO NOT do this.
>For one thing, if you have any foreign keys to the
>table's primary key, they will be pointing to the wrong
>records.

*Why* would this cause them to point to the wrong record? The only way this could be true, is if you changed the values for the fields making up the primary key.

Since in this situation you'd be creating a second table, which eventually replaces the first, you'd have to drop all relationships involving that table, and then reestablish those relationships after the second table is in place of the first.

But again, this is *NOT* the recommended course of action.


As was said, simply use a predefined Index, or do as Rspahitz and others have said, and use a Query or Select statement to control the order in which the records appear.


-Dennis Borg
Avatar of DrD

ASKER

Thanks for all the responses. I am interested in PierreCampe's suggestion. But how do I create this index in Code? Also, even in the query route I need to sort the ROWS of data by UnitID and then by Date. How do I keep the rows of data intact?
>I am interested in PierreCampe's suggestion. But how do I
>create this index in Code?

FYI -- You only *create* this index once .... after that, you simply use it. The DBMS would maintain the index as you add and delete records.



>Also, even in the query route I need to sort the ROWS of
>data by UnitID and then by Date.

The SQL statement of the query would look something like:


   SELECT * FROM MyTable ORDER BY UnitID ASC, [Date] DESC;

I would recommend that you do not name a field "DATE", as DATE is a keyword. Instead I would suggest a field name such as "OrderDate", or "BirthDate", etc.

Same would be true for field names such as "Name", which is also a property name for field objects.



>How do I keep the rows of data intact?

I'm not sure I understand what you mean.



-Dennis Borg
We can show you how to create indexes in code (I just don't have the time to get into that now) ... but you do realize that you can create the index from Access (I'm assuming you're using an Access database)
DennisBorg said, "*Why* would this cause them to point to the wrong record? The only way this could be true, is if you changed the values for the fields making up the primary key...you'd have to drop all relationships involving that table"

Yes, Dennis, I assumed that this process would somehow bypass the relationships.  As long as the referential integrity rules are enforced by the DB, the situation will not occur, but will force you to (as you said) rebuild those relationships, which could get pretty nasty if the DB doesn't let you save and restore them.

--
As for the creation of indices, etc., this is far easier in the comfortable and protective Access environment, although can be done in VB if you know the right commands.

Personally, I do all my .mdb design in Access, then use VB for data manipulation (select/insert/update/delete.)


Avatar of DrD

ASKER

It looks as if I can create this index in the database table itself using Access. But how much of the db design is Access and how much of this design will remain if the VB is used on a machine which doesn't have Access installed? I would think the safest way would be to create the index using VB.
Anyone know how to do that...
More points!
Thanks
David

 
Hi,

Even on a machine that does not have access installed, I assume that you would be accessing the database you created in Access using ODBC and the access driver. The indexes you created in index are stored in the database.

Just to clarify one point, all indexing does is make querying faster, the only way, as far as I am aware, to get a recordset in a specific order is to use an order by query. Even if you do index the table, the order is, AFAIK, not guaranteed, although it will probably usually be in order. Nonetheless, having an index is a good idea on all field that you want to order by because this will speed up the query immensely.

Zaphod.
the complete database shema is saved in the database
so it makes no difference if you use access or vb
and for that access does not have to be installed on the users machine
Ok I am assuming you created the index with MSAccess
here an example of doing it
i use DAO (imho the only way to access an .mdb(but that is only  my opinion))

dim db as dao.database
dim rs as dao.recordset
set db=opendatabase("yourdb.mdb")
set rs=db.openrecordset("yourtable",dbopentable)
rs.index="yourindex"

and now the recordpointer will point to the first record according to "yourindex"
and everytime you rs.movenext you will get the next record in sorted order
if you rs.moveprevious you will get the previous record in sorted order
and if you want to quickly go to a specific record then seek to that record
ie:rs.seek "=",the field of the active index
and if you want your table immediately sorted by an other index just say:
rs.index="TheOtherIndex"
and that will make you look like a sorcerer to your users as you immediately and i mean *immediately* sort a 500000 record table on any field you want (but that field has to be indexed)
also note that setting an index and seeking to a record is magnitudes faster then running a select query and the overall effect is the same and if your db is on a network machine it will be magnitudes of magnitudes faster then a query
ASKER CERTIFIED SOLUTION
Avatar of pierrecampe
pierrecampe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DrD

ASKER

Bravo! PierreCampe and my grateful thanks to all of you for your help on this!
DrD:

>It looks as if I can create this index in the database table itself using Access.
>But how much of the db design is Access and how much of this design will
>remain if the VB is used on a machine which doesn't have Access installed?

Tables, Queries, Relationships, Stored Procedures, Indexes, etc are all Database Objects which are stored with the database. It does not matter what language you use to access the database, the database objects which have been created are there in the database.

If you are using Java, Visual Basic, Pascal / Delphi, or whatever language, and are accessing an Access Database, all of those database objects are there in the database. Same is true if you were using an Oracle database, or an MS SQL Server database instead of MS Access.

When you create a setup package for a VB application which uses an Access Database, all of the necessary components for Data Access are packaged along with your application. MS Access uses the Jet Database Engine to manage the Access databases.

The application "Microsoft Access" uses that same Jet Database Engine. It is that Database Engine which is needed for data access, not the Microsoft Access application itself.

So you are perfectly safe in creating your indexes, queries, tables, relationships, etc in MS Access, and then installing your application onto a different machine which does not have the MS Access application; for your Setup Package has installed the Jet Database Engine onto that machine.



Zaphod:

> I assume that you would be accessing the database
>you created in Access using ODBC and the access driver.

Just to clarify, ODBC is not required simply because the computer does not have MS Access installed on it.


>Just to clarify one point, all indexing does is make querying faster,
>the only way, as far as I am aware, to get a recordset in a specific
>order is to use an order by query. Even if you do index the table,
>the order is, AFAIK, not guaranteed, although it will probably
>usually be in order.

This is incorrect. True, Indexing *does* make queries faster, but (oversimplified explaination) Indexes do this by recording the order in which the indexed fields appear. If it had a random list of values for the field(s) in the index, then it could not possible look up things any faster. So by the very definition of Indexes, it records the order in which the records would appear if the records were sorted by those fields.

This is why you see the records in the expected order when you pick a certain index.


-Dennis Borg
Dennis said, "So by the very definition of Indexes, it records the order in which the records would appear if the records were sorted by those fields."

I'm not sure, but I think the quote was more in reference to the sequence of an index (or that's how I interpreted it, although it was not stated as such.)

You are totally correct that the index will always maintain things in the sort order specified (unless corruption occurs or unless the index is otherwise compromised) but the elements will not always be in sequence.  i.e. When you create an autonumber primary key, this is automatically indexed.  As you add records, the new number will always be the next sequential one after the last record *unless* you deleted that record, in which case there is no guarantee that the next number will be sequential.  So add a record and it becomes ID=1; add another: ID=2; add another: ID=3; delete ID=3; add another: ID=4, not 3 as you might expect.  This will cause no problems in the indexing or in the speed achieved by using the index.
>I'm not sure, but I think the quote was more in reference
>to the sequence of an index (or that's how I interpreted
>it, although it was not stated as such.)

I do not understand what is trying to be said here.


>but the elements will not always be in sequence.

In respect to the index, yes it will.



>When you create an autonumber primary key, this is
>automatically indexed.

Just to clarify ... when you create an AutoNumber field in a table in Access, Access does *not* automatically create an index for that field.

There are certain word triggers that causes Access to assume that the field in question is a key field or is a primary key. (See Tools / Options of the menu, and select the Tables/Queries tab. Look at the "AutoIndex on Import/Create" option to see a configurable list of these words) When your field is named after these words, Access will automatically index them and also make certain assumptions about what the nature of those indexes should be (primary, unique, etc)

Of course, you're free to override this and completely change the definition of those indexes which Access generated automatically, or you can remove that index altogether.

But Access does not require a field to be indexed simply because it is an AutoNumber field; in fact, Access does not require any field to be indexed. That is all up to you as the database designer.

Try creating a new table, and name one of the fields "AAA", and make it an AutoNumber field. You'll see that Access does not automatically index that field for you.



>As you add records, the new number will always be the
>next sequential one after the last record *unless* you
>deleted that record, in which case there is no guarantee
>that the next number will be sequential.  So add a record
>and it becomes ID=1; add another: ID=2; add another:
>ID=3; delete ID=3; add another: ID=4, not 3 as you might
>expect.

But when you assign a recordset to use that particular index, they will be in sequence as defined by that index. They will appear in that order.

You seem to be confusing "consecutive" with "sequential". Data can be in a particular order and still not be consequitive.

For example:

   1, 7, 9, 10, 13, 42, 67, 98

Even though the numbers above are not consecutive numbers, they are in sequence; they are in order (ascending order to be exact).

How MS Access assigns values to AutoNumber fields is totally irrelevant to indexes. They (AutoNumbers and Indexes) are two totally different things.


Does that help you understand it better?


-Dennis Borg
>You seem to be confusing "consecutive" with "sequential".

Sorry...you're right.

However, the primary key MUST be indexed (as far as I know) and relational tables require a unique key (one of which is the primary key), so I don't think you can create an Access table without an index.
>so I don't think you can create an Access table without an index.

I have on a number of occassions. Try it out. A table is not required to have an index.

Any given table may have no indexes, or it may have one index, or it may have several indexes.

Furthermore, a table can have one or more indexes without having a Primary Key.

Any given index may be composed of one field, or it may be composed of several fields.




>the primary key MUST be indexed ...

It would probably be more accurate to say that the Primary Key *IS* an index. In fact, it is a *special* index in which all of the fields must have a value (i.e. Not Null), and that the combination of field values for the fields defining the Primary Key must be unique.

The term "Primary Key" is used to refer to the Primary Key Index; sometimes it is used to refer to the field or fields which define that Primary Key Index, since the field(s) defining the index are often loosely considered to be the same thing as the index itself; and for all intents and purposes, they are the same thing.


-Dennis Borg
Good clarifications Dennis.  Thanks.  But I'm still surprised you can create a table without a P.K.

Also, one piece missing from your descriptions is that multiple fields can be indexed, and a field can have more than one index.
rspahitz:

>Good clarifications Dennis.  Thanks.

Thank-you; and you're welcome.  :-)



>But I'm still surprised you can create a table without a P.K.

Even though it's true, it is almost always a good idea to define a PK.



>Also, one piece missing from your descriptions is that
>multiple fields can be indexed,

Good point to bring up.

Actually, I did cover that. If you read back, you'll see that I mentioned that a table can have ONE OR MORE INDEXES. Since a major part of the definition of any given index is the field or fields specified in the index, this would show that more than one field in a table can be involved in an index. For example, if a table has two indexes, each index must contain at least one field each. This means that, in this hypothetical example, two fields are indexed.

I also mentioned that any given index may include more than one field. This would also illustrate that more than one field can be involved with the indexes.

Here is a quote of me where I gave these indications:

----------------------------------------------------------------------------
Furthermore, a table can have one or more indexes without having a Primary Key.

Any given index may be composed of one field, or it may be composed of several fields.
----------------------------------------------------------------------------


Something else to think about:

      Any given field may be participate in more than one index



Another way of saying what we were just talking about:

      Any given Index must have one or more fields

      Any given Field may be included in one or more indexes (except perhaps for certain data types, such as BLOBs or Memos, depending up the DBMS in question)

      Any given Table may have one or more indexes (but does not have to have any)



-Dennis Borg
>a field can have more than one index.

Not to nitpick, but just to help clarify this in your mind ... Fields do not have indexes, Tables do. Fields are included in Indexes as part of the definition of the index.

Make sense?


-Dennis Borg
OK, so:

* A table can have any number of indexes (indices).

* Each index is stored outside the table, hence the reason a primary key field has a (separate) corresponding primary key index.

* Each index contains information from one or more fields (although *typically* just one with a good design, in my experience).

* Different indexes can contain information from the same field.  For example a forward-sorted index on field-A, a reverse-sorted index on field-A, and an index that is a combination of field-A and field-B.

>* A table can have any number of indexes (indices).

Yep



>* Each index is stored outside the table, hence the
>reason a primary key field has a (separate) corresponding
>primary key index.

I'm not sure what you mean here.

Indexes are part of the Table's definition. How a particular DBMS stores this data depends upon the DBMS. Some, like Access, stores all the database objects (tables, indexes, forms, reports, etc) in one file. Others, such as Paradox, store each object in their own file.

But perhaps that's not what you were thinking of.




>* Different indexes can contain information from the same
>field.  For example a forward-sorted index on field-A, a
>reverse-sorted index on field-A, and an index that is a
>combination of field-A and field-B.

Yep





>* Each index contains information from one or more fields
>(although *typically* just one with a good design, in my
>experience).

Yep; although there are many "typical" situations in which good design dictates there should be more than one field in an index, or even in the Primary Key Index.

For example, consider the following two tables:

   Orders
   -----------
   1) OrderNum
   2) CustomerID
   3) OrderDate
   4) DueDate
   5) DateShipped



   OrderDetails
   ----------------------
   1) OrderNum
   2) ItemNum
   3) Qty
   4) PriceEach


For table Orders, you would want two indexes. The primary index would consist of the OrderNum field. You would also want to set up another index (perhaps named CustomerIndex) consisting of the CustomerID field. Both of these indexes would include only one field each.

Now, table OrderDetails should have one index, which would be the PK. But this time, it should be composed of two fields: OrderNum and ItemNum

Do you see how that would make sense?

The realtionship between these two tables would be one-to-many (one on the Orders side, many on the OrderDetails side)


-Dennis Borg