SQL syntax for Null?

[TC]

Re: SQL syntax for Null?

But, the duplicates don't take any space!

See google groups message ID:

<3964f176.02112 41832.f9b52ee@p osting.google.c om>

:-)

TC


"Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
news:YbGkb.1579 52$bo1.113900@n ews-server.bigpond. net.au...[color=blue]
> Yes. DO delete the indexes where they are duplicated.
>
> To prevent duplicate indexes in future, go to:
> Tools | Options | Tables/Queries
> and delete everything in the box:
> AutoIndex on Import/Create
>
> If you leave those entries there, Access automatically adds an index to[/color]
any[color=blue]
> field that has a name ending in ID, Num, Code etc. Since you also[/color]
specified[color=blue]
> TxType_ID as your primary key, you have a spurious index.
>
> The same thing happens with foreign keys, though it is less obvious. If[/color]
you[color=blue]
> have a foreign key with a name ending in "ID", Access automatically[/color]
indexes[color=blue]
> it. Then if you create an index on the field, you have a duplicate. Then[/color]
if[color=blue]
> you create a relationship with referential integrity (RI) between this[/color]
table[color=blue]
> and the primary one, Access creates *another* hidden index to maintain the
> RI, so you now have the field triple-indexed!
>
> In general, don't let Access auto-index, and don't create indexes on your
> foreign keys: use a relationship with RI instead.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "deko" <dje422@hotmail .com> wrote in message
> news:UKFkb.88$f B6.48@newssvr14 .news.prodigy.c om...[color=green]
> > I checked my tables according to the procedure you described below. I
> > discovered that (in most all of my tables) some fields apparently had
> > duplicate indexes. For example:
> >
> > Index Name -- FieldName
> > TxType_ID -- TxType_ID
> > PrimaryKey -- TxType_ID
> >
> > Can I delete the TxType_ID index?
> > Is it okay to rename these indexes?
> >
> >
> > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > news:JEskb.1572 01$bo1.83594@ne ws-server.bigpond. net.au...[color=darkred]
> > > TC, open a table in design view.
> > > Open the Indexes dialog (View menu).
> > >
> > > One of the properties in the lower pane of the dialog is, "Ignore[/color][/color]
> Nulls".[color=green][color=darkred]
> > > Provided that setting is No, Access will be able to match the Nulls[/color]
> > without[color=darkred]
> > > a table scan.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "TC" <a@b.c.d> wrote in message news:1066550022 .148889@teuthos ...
> > > >
> > > > "Allen Browne" <allenbrowne@Se eSig.invalid> wrote in message
> > > > news:kPmkb.1567 95$bo1.17100@ne ws-server.bigpond. net.au...
> > > > > (B) should be more efficient.
> > > > >
> > > > > The expression:
> > > > > WHERE qry100.Cat_ID Is Null
> > > > > is evaluated by JET, the query engine in Access, so it can take
> > > advantage
> > > > of
> > > > > any index on the field.
> > > >
> > > > Not if nulls ain't indexed! I suspect (but am not certain) that this[/color]
> > would[color=darkred]
> > > > always do a full table scan.[/color][/color]
>
>[/color]