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]

Re: Database Index

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

Check the index type.

"John" <john_stlmo@yah oo.com> wrote in message
news:dz16b.177$ Yt.86@newsread4 .news.pas.earth link.net...[color=blue]
> Hi all -
>
> I'm trying to build a small Access Database (that came with VB5) - I'd[/color]
like[color=blue]
> 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
>
>[/color]

[John]

Re: Database Index

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 " <shit.box@ntlwo rld.com> wrote in message
news:IG16b.1094 $b.429@newsfep1-gui.server.ntli .net...[color=blue]
> It sounds like you have put a unique index on the date field rather than[/color]
an[color=blue]
> index.
>
> Check the index type.
>
> "John" <john_stlmo@yah oo.com> wrote in message
> news:dz16b.177$ Yt.86@newsread4 .news.pas.earth link.net...[color=green]
> > Hi all -
> >
> > I'm trying to build a small Access Database (that came with VB5) - I'd[/color]
> like[color=green]
> > the database records sorted and displayed by date (which it does - date[/color][/color]
is[color=blue][color=green]
> > an indexed field) but if I have one record with a certain date and try[/color][/color]
to[color=blue][color=green]
> > input another one with the same date I get an error message...I'm[/color][/color]
assuming[color=blue][color=green]
> > the program doesn't know how to sort two fields containing the exact[/color][/color]
same[color=blue][color=green]
> > information?
> >
> > Any help will be appreciated...
> >
> > Thanks - John
> >
> >[/color]
>
>[/color]

[Suzette]

Re: Database Index

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" <john_stlmo@yah oo.com> wrote in message
news:t%16b.192$ Yt.96@newsread4 .news.pas.earth link.net...[color=blue]
> 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 " <shit.box@ntlwo rld.com> wrote in message
> news:IG16b.1094 $b.429@newsfep1-gui.server.ntli .net...[color=green]
> > It sounds like you have put a unique index on the date field rather than[/color]
> an[color=green]
> > index.
> >
> > Check the index type.
> >
> > "John" <john_stlmo@yah oo.com> wrote in message
> > news:dz16b.177$ Yt.86@newsread4 .news.pas.earth link.net...[color=darkred]
> > > Hi all -
> > >
> > > I'm trying to build a small Access Database (that came with VB5) - I'd[/color]
> > like[color=darkred]
> > > the database records sorted and displayed by date (which it does -[/color][/color][/color]
date[color=blue]
> is[color=green][color=darkred]
> > > an indexed field) but if I have one record with a certain date and try[/color][/color]
> to[color=green][color=darkred]
> > > input another one with the same date I get an error message...I'm[/color][/color]
> assuming[color=green][color=darkred]
> > > the program doesn't know how to sort two fields containing the exact[/color][/color]
> same[color=green][color=darkred]
> > > information?
> > >
> > > Any help will be appreciated...
> > >
> > > Thanks - John
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Pieter Linden]

Re: Database Index

"John" <john_stlmo@yah oo.com> wrote in message news:<t%16b.192 $Yt.96@newsread 4.news.pas.eart hlink.net>...[color=blue]
> 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[/color]

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.Field 2
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.