Database Index

[John]

Hi all -

I'm trying to build a small Access Database (that came with VB5) - I'd like
the database records sorted and displayed by date (which it does - date is
an indexed field) but if I have one record with a certain date and try to
input another one with the same date I get an error message...I'm assuming
the program doesn't know how to sort two fields containing the exact same
information?

Any help will be appreciated...

Thanks - John

[cor_blimey]

It sounds like you have put a unique index on the date field rather than an
index.

Check the index type.

"John" <jo********@yahoo.com> wrote in message
news:dz*************@newsread4.news.pas.earthlink. net...

Hi all -

I'm trying to build a small Access Database (that came with VB5) - I'd like the database records sorted and displayed by date (which it does - date is
an indexed field) but if I have one record with a certain date and try to
input another one with the same date I get an error message...I'm assuming
the program doesn't know how to sort two fields containing the exact same
information?

Any help will be appreciated...

Thanks - John

[John]

Thanks for the response...

Yes, the 'unique' field was checked - but I haven't checked it. In fact,
while setting up the index field for date, I unchecked the unique box but
when I hit add index the unique field is being automatically checked again
and it won't let me uncheck it... ?

I need the db sorted by date and there may be several records for a single
date -

Ideas?

John

"cor_blimey" <sh******@ntlworld.com> wrote in message
news:IG**************@newsfep1-gui.server.ntli.net...

It sounds like you have put a unique index on the date field rather than an index.

Check the index type.

"John" <jo********@yahoo.com> wrote in message
news:dz*************@newsread4.news.pas.earthlink. net...

Hi all -

I'm trying to build a small Access Database (that came with VB5) - I'd

like

the database records sorted and displayed by date (which it does - date is an indexed field) but if I have one record with a certain date and try to input another one with the same date I get an error message...I'm assuming the program doesn't know how to sort two fields containing the exact same information?

Any help will be appreciated...

Thanks - John

[Suzette]

If the field is a key field, it will need the unique setting. The only way
to be able to take of the unique setting is to turn the field off as a key
field.

Sue
"John" <jo********@yahoo.com> wrote in message
news:t%*************@newsread4.news.pas.earthlink. net...

Thanks for the response...

Yes, the 'unique' field was checked - but I haven't checked it. In fact,
while setting up the index field for date, I unchecked the unique box but
when I hit add index the unique field is being automatically checked again
and it won't let me uncheck it... ?

I need the db sorted by date and there may be several records for a single
date -

Ideas?

John

"cor_blimey" <sh******@ntlworld.com> wrote in message
news:IG**************@newsfep1-gui.server.ntli.net...

It sounds like you have put a unique index on the date field rather than an

index.

Check the index type.

"John" <jo********@yahoo.com> wrote in message
news:dz*************@newsread4.news.pas.earthlink. net...

Hi all -

I'm trying to build a small Access Database (that came with VB5) - I'd

like

the database records sorted and displayed by date (which it does -

date is an indexed field) but if I have one record with a certain date and try to input another one with the same date I get an error message...I'm assuming the program doesn't know how to sort two fields containing the exact same information?

Any help will be appreciated...

Thanks - John

[Pieter Linden]

"John" <jo********@yahoo.com> wrote in message news:<t%*************@newsread4.news.pas.earthlink .net>...

Thanks for the response...

Yes, the 'unique' field was checked - but I haven't checked it. In fact,
while setting up the index field for date, I unchecked the unique box but
when I hit add index the unique field is being automatically checked again
and it won't let me uncheck it... ?

I need the db sorted by date and there may be several records for a single
date -

Ideas?

John

John,

I hate to rock the boat, but a table is by definition unsorted. If
you want to order your records, use a query.

SELECT MT1.Field1, MT2.Field2, MT2.Field3...
FROM MyTable1 AS MT1 INNER JOIN MyTable2 AS MT2 ON
MyTable1.Field1=MyTable2.Field2
ORDER BY MT1.Field1, MT2.Field2...

otherwise, you'll need to index on the fields you want sorted by...
kind of a waste of time, unless you're going to sort/query on those
fields a lot.