Build Table Index Based on a field value?

[saraqpost]

I am not sure if this can be done using the Index on Table Design, or
if I have to do something else.

I have a table: tblInvoices, which has InvoiceDate, InvoiceNumber,
InvoiceStatus.

If the user requests "delete", I simply change the status from "A"
(default) to "D".

I created a unique index to prevent the user from adding the same
invoice twice:
InvoiceDate
InvoiceNumber
InvoiceStatus

One user (not sure why) added a record, then deleted it, then added the
same information again, then tried to delete it again, and (of course)
the index stopped her from doing that.

Is there anyway to just have an index on the "A" value of Status? So
InvoiceDate, InvoiceNumber, and InvoiceStatus = "A".

I don't think I care how many "D" records there are that are
"identical".

Thanks -
sara

 

[strive4peace]

Hi Sara,

"Is there anyway to just have an index on the "A" value of Status?"

No

"If the user requests "delete", I simply change the status from "A"
(default) to 'D'"

If you want to keep multiple deleted records, add a sequential number
after D ... but if there is already a D, it would be simplest to delete
or archive ... don't keep them in the same table -- or, depending on why
your needs, you will be constantly filtering them out for your reports



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*